DataBase Operation In Java Using JDBC

What is JDBC?

     Java database connectivity (JDBC) is the JavaSoftware specification of a standard application programming interface (API) that allows Java programs to access database management systems.

      In the program we are using MS SQL Server 2000 and Type1 JDBC Driver. First create a database and create a table(Employee)in it containing columns EmpID Name and Salary. Then create a DSN in your system which will points to the database we want to connect.

      In the following program we are inserting values to the database, updating the database, deleting values from the table and retrieving the values from the table using ResultSet.

CODE:-

Code For Inserting, Updating and Deletion

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBCDemo {
	public static void main(String[] args) {
		 try{

	          //loading  the driver

	          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

	          System.out.println("Driver Is loaded");

	          //Setting Connection String

	          String dburl="jdbc:odbc:Demo";

	          //Getting connection Object

	          Connection con = DriverManager.getConnection(dburl,"sa","");

	          System.out.println("Connection established");

	          //Performing database Operation

	          Statement st=con.createStatement();

	          String sql="INSERT INTO EMPLOYEE VALUES('103','Liku',10000)";

	          //String sql="UPDATE EMPLOYEE SET SALARY=20000 WHERE EMPNAME='Abinash'";

	          //String sql="delete from employee where empid='103'";

	         
	          int result=st.executeUpdate(sql);

	         
	          System.out.println("No of rows affected=" + result);

	         
	          //Releasing Resources

	          st.close();
	          con.close();

	          System.out.println("Connection closed");

	      }

	      catch(Exception ex)

	      {

	          ex.printStackTrace();

	      }
	}

}

OUTPUT

Driver Is loaded
Connection established
No of rows affected=1
Connection closed

In this case you can run only one query at a time. So the other two are commented.

Code for Retrieval Of Records

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet; 

public class JDBCDemo {
	public static void main(String[] args) {

		 try{

	          //loading driver

	          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

	          System.out.println("Driver loaded");

	          //Setting Connection String

	          String dburl="jdbc:odbc:Demo";

	          //Get connection Object

	          Connection con = DriverManager.getConnection(dburl,"sa","");

	          System.out.println("Connection established");

	          //database Operation

	          Statement st=con.createStatement();

	          String sql="select*from employee";

	          ResultSet rs=st.executeQuery(sql);

	          while(rs.next())

	          {
	       
	        	 System.out.print("Employee ID= "+ rs.getString(1)+" : ");

	        	 System.out.print("Employee Name= "+ rs.getString(2)+" : ");

	        	 System.out.println("Employee Salary= "+ rs.getString(3));
	        	
	         }

	          //Releasing Resources

	          rs.close();

	          st.close();

	          con.close();

	          System.out.println("Connection closed");

	      }

	      catch(Exception ex)

	      {

	          ex.printStackTrace();

	      }
	}

}

OUTPUT

Driver loaded
Connection established
Employee ID= 108 : Employee Name= Abhisek : Employee Salary= 23000.0
Employee ID= 103 : Employee Name= Abinash : Employee Salary= 25000.0
Employee ID= 107 : Employee Name= Mukesh : Employee Salary= 20000.0
Connection closed

Description

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet; 

Now we have to load the jdbc odbc driver using the following method

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

     Then we have to create a connection string which will point to the DSN we created. Then create the con object and call the getConnection()

 String dburl="jdbc:odbc:Demo";
 Connection con = DriverManager.getConnection(dburl,"sa","");

dburl is only a string variable and sa is the user ID for the database. The password is blank here.

 Statement st=con.createStatement();
 String sql="select*from employee";

       For insert, delete and update you have to use executeUpdate() method and for retieval use executeQuery() and pass the sql statement to it.

 int result=st.executeUpdate(sql);
 ResultSet rs=st.executeQuery(sql);

We have to create ResultSet object as the return type of executeQuery() is ResultSet.

 rs.close();
 st.close();
 con.close();

Exception handling is necessary here.

RSS Feed