ddsingh@infomatics.info +91 9760014754

JDBC(Java Database Connectivity)

  • JDBC is a java api[Application Programing Interface] which facilitate connectivity of java applications to databases.
  • This api contains a set of interfaces and helper classes which is provided by sun microsystem(Oracle).Before using this api u must understand its evolution.
  • When concept of database evolved RDBMS Package such as Oracle,SQL Sever,MySql etc. are created.
  • Users of a RDBMS Package can be divided in two category

                 1)Human Users

                 2)Applications

  • A RDBMS Package vendor provides interfaces for both these type of users.

  • Application interface is called driver usually a driver is a set of functions or classes which perform database operations .initially native drivers were provided by database venders to application programmer to perform database operations.

 

 

  • Limitations of Native Driver

             1)Appliation programmer need  to learn different drivers  for each RDBMS Package.

             2)If RDBMS Package of an Application is changed than Connectivity code of an application is to be rewritten

  • Both the problem of native driver were solved by odbc.
  • Odbc is a standard api for application to interact with rdbms package.
  • ODBC is a set of c functions prototype(fn declarations) to perform database operations.
  • implementations of these functions is provided by database vendor.
  • each implementation of ODBC API is called odbc driver.
  • Note:-

                   ODBC API is Created by One Committee.where as Implementation of ODBC API (ODBC Driver) is created by Database Vendor

  • Limitation of Odbc:-

 

  •  Major limitation of ODBC is that it is written in c.In order to use odbc,
  • application programmer of different language need to invoke/call c function.
  • To Remove this limitation  for  java programmer, sun microsystem(Oracle) provided one api in java  by the name of  jdbc.
  • JDBC API consists of set of interfaces provided by sun microsystem.
  • Implementation of these interfaces  is provided by database vendor. this implementation is called jdbc drivers.
  • Note:-  JDBC API and JDBC Driver are two different things.
  • JDBC API is Created by Sun(Oracle)

  • Different Database vendors provide implementation of jdbc api in diffrent ways.
  •  On the basis of Database vendors  implemetation, jdbc drivers are divided in four category.
  • There are 4 types of JDBC drivers:

 

             1. Type1 or JDBC –ODBC Bridge driver

             2. Type2 or JDBC Native Driver

             3. Type3 or JDBC Network Driver

             4. Type4 or Pure Java Native Driver

JDBC Driver

  • JDBC Driver is a software component that enables java application to interact with the database.It Perform two task.

 

  • Convert SQL statements into a form that a particular database can interpret(Understand).

 

  • Retrieve the result of SQL statements and convert the result into equivalent JDBC API class objects. 

 

  • JDBC Driver is a software component that enables java application to interact with the database.It Perform two task.

 

  • Convert SQL statements into a form that a particular database can interpret(Understand).

 

  • Retrieve the result of SQL statements and convert the result into equivalent JDBC API class objects. 

 


1) Type 1 or JDBC – ODBC Bridge Driver

  • This driver is a java wrapper of odbc driver.That is in this case implementation classes of jdbc interfaces are provided by database  vendor.

 

  • It invokes/calles odbc function using JNI(Java Native Interface)

Advantage of type1 Driver:-

1) This is the simplest driver from the point of view of implementation

 2) Type 1 Driver can be used to connect

  java application to any database without changing the driver class.

Disadvantage of Type 1 Driver:-
 

1) odbc driver need to be installed on each machine where java Application is to be executed.

 

2) Degraded Performance is obtained because each database operation requires multiple method calls and multiple  conversion.

 

Java to Jdbc-> jdbc to odbc -> odbc driver to Native Format of Database.


2) Type 2 or JDBC Native Driver

  • This driver is  a java Wrapper of Native Driver.in this driver classes invoke the native functions using JNI.
  • In this driver implementation classes of jdbc interfaces are provided by database vendor.
  • it invokes the functions of Native driver using JNI.

 

Advantage of Type 2 Driver:-

1) odbc driver is not required

2) better performance is obtained as compared to type1 driver.

 

Java to jdbc->jdbc to native->native to database.

 

Disadvantage:-

 

  1) Native Driver need to be installed on each machine where java appliation is to be executed.

   


3) Type 3 or Network Driver/ JDBC-Net Pure-Java driver
 

 

  • It is the modified version of type-2 driver.
  • In this driver implementation classes is provided by database vendor .
  • In this driver JDBC driver classes invokes function of native driver over the network

Advantage:-

  1. Native driver need to be installed only on a single machine on the network.

Disadvantage:-

 1) Additional networking support is required.

2) Native driver is still required.

3) Performance is degraded because of additional networking overhead

 


4) Type 4 Driver or pure Java Driver/Thin driver

  • It is a Java driver  it means Type-4 driver is a pure implementation of jdbc in java.in this driver jdbc classes directly interact with database.

 

  • The type-4 driver converts  JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

Advantage of Type-4 Driver

1) odbc or Native drivers are not required.

 

2) Better performance is obtained as compare to the other driver

Disadvantage:-

  1. For Each database different set of driver classes are required.

 

Using JDBC API

The JDBC API classes and interfaces are available in the java.sql and the javax.sql packages.

 

Java.sql: -(Package)

Commonly used classes and Interfaces are as follows

 

1) DriverManager                           

2) SQLExeception

3)Types

 

4) Connection

5) Statement

6) PreparedStatement                              

7) CallableStatement

8)ResultSet

9) ResultSetMetaData

10) DatabaseMetaData

 

  • 1) DriverManager: -

It is a utility class/helper class provided by Sun Microsystem(Oracle). that is responsible for managing jdbc drivers and act as a factory of connection.

 

  • 2)Types:-It is a helper class which defines static final integer datamembers to represents JDBC types.

 

  • 3) SQLException:-

                   it is a super class of all Database related exceptions

  • 4) Connection:-

                   this interface provides methods for managing database connectivity act as a factory of Statement

 

  • 5) Statement:-

                 this interface provides methods for execution SQL query and acts as a factory of ResultSet.

  • 6) PreparedStatement

                 This interface is  a sub interface of a Statement and provide the facility of executing parameterized queries.

 

 

  • 7)CallableStatement:-This interface extends PreparedStatement and provides methods for executing  stored procedures and functions

 

  • 8) ResultSet:-

                   This interface provides methods of managing the results of a select query it means it

                   Represents the information Which is retrieved from a database.

Steps to connect to the database in java

  • There are 5 steps to connect any java application with the database. They are as follows:

 

             1)Register the driver class

             2)Creating connection

             3)Creating statement

             4)Executing queries

             5)Closing connection

 

 

 

1) Register the driver class/Load the driver  class

 

      1.1) The forName() method is available in the java.lang.Class class.

 

This method is used to register the driver class. This method is used to dynamically load the driver class.

 

Syntax of forName() method

public static  Class forName(String className)throws 

          ClassNotFoundException  

 

In the case of type 1 driver

 

Ex:-

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

In the case of type 4 driver

Ex:-

For Oracle

Class.forName("oracle.jdbc.driver.OracleDriver");

For MySql

Class.forName("com.mysql.jdbc.Driver");

 

 

1.2)Using the registerDriver()method

 

          This method belongs to the DriverManager class.You need to call the registerDriver() method to register the Driver class object with the DriverManager.

EX:-

DriverManager.registerDriver(driverObject);

Ex:-

Connection con=DriverManager.getConnection(“UrlOfDatabase”);

 

Here url provide the database information to the DriverManager.This information is basically used to establish a connection with the database.

 

4) Execute the query

The Statement interface contains the following methods to execute static SQL statements to a database:

ResultSet executeQuery(String selectQuery)

int executeUpdate(String DMLStatement)

boolean execute(String DDLStatement)

 

DQL(Data Query Langauage(Select Command)

DML(Data manipulation  Language(insert,update,delete)

DDL(Data Definition Language(create,alter,drop)

 

 

5) Close the Connection object

By closing connection object statment and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

 

Syntax :-

 

public void close()throws SQLException  

Eg:-

con.close();  

  • When you execute a Select query to retrieve data from a table using a Java application, the output of the query is stored in a ResultSet object in a tabular format. A ResultSet   object maintains a cursor that enables you to move through the rows stored in a ResultSet object.
  •  By default, the ResultSet object maintains a cursor that moves in the forward direction only. As a result, it moves from the first row to the last row in the ResultSet.
  •  You cannot update the default ResultSet object(bcz it is in Read Only Mode). The cursor in the ResultSet object initially points before the first row.

  • In case of select Query result of the query  to be read which required two steps.

 

                 i) Record pointer is to be placed on the desired  record in the ResultSet

 

                ii) Value of indivisual field(Column) of the record is to be retrieved.

Note: -

 

   next() method of the ResultSet interface performs two task.

       1)it is used to advance(move ahead) the record pointer by one record.

       2)it checks weather record is there or not.

 

public boolean next()

To read the value of individual fields(Column) of a record

ResultSet interface provides one more method.

Syntax:-

public type getXXX (int fieldIndex) throws SQLException

 

XXX Denote the Datatype.

 

Eg:-

 

public  int getInt(4);  //4 is the coulmn number

(Demos)

SelectTest.java

import java.sql.*;
class SelectTest
{

     public static void main(String args[])
      {

          try
          {

				Class.forName("com.mysql.jdbc.Driver");   
				Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College","root","mysql");

				//Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");

			   Statement stmt=con.createStatement();  //3
		       ResultSet rs=stmt.executeQuery("select * from Emp");  //4
                System.out.println("Details of the Employee are as follows");

                  while(rs.next())
                  {
						    System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getInt(4));
                   }
                  con.close();   //5
           }
           catch(Exception e)
           {
                  System.out.println(e);
           }
}
}

 

InsertTest.java

import java.sql.*;
public class TestInsert
{

	public static void main(String args[]) throws ClassNotFoundException,SQLException
	{

		   Class.forName("com.mysql.jdbc.Driver");   
           Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
			Statement stmt=con.createStatement();
            stmt.executeUpdate("insert into Emp values('Z001', 'Farhan','Trainee',7000)");	
            System.out.println("Record has been successfully inserted");
            con.close();

     }
}


TestUpdate.java

//this demo is for updation
import java.sql.*;

public class TestUpdate
{

	public static void main(String args[]) throws ClassNotFoundException,SQLException
	{

		Class.forName("com.mysql.jdbc.Driver");   
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
		Statement stmt=con.createStatement();
		stmt.executeUpdate("Update Emp set Empname='Dhoni' where empid='Z001' ");
		System.out.println("Record is updated successfully");
		con.close();
	}
}

 

 

TestDelete.java

//This demo is for deletion
import java.sql.*;

public class TestDelete
{

public static void main(String a[]) throws ClassNotFoundException,SQLException
{

	Class.forName("com.mysql.jdbc.Driver");   
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
    System.out.println("Connection Established");
    Statement stmt=con.createStatement();
    stmt.executeUpdate("Delete from Emp where empid='Z001' ");
    System.out.println("Record is deleted successfully ");
    con.close();
  }
}


 

Create Table.java

import java.sql.*;
public class CreateTable
{
	public static void main(String args[])
	{

		try
		{

		Class.forName("com.mysql.jdbc.Driver");   
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
		Statement stmt=con.createStatement();
		String str="create table Student(stuid int,stuname varchar(20) )";
		stmt.execute(str);
		System.out.println("Table is Created");
		con.close();
	   }
		catch(Exception e)
		{
			System.err.println("Connection to database failed");
		}
	}
}

AlterTable.java

//This demo is for alteration
import java.sql.*;
public class AlterTable
{
public static void main(String args[]) throws Exception
{
	Class.forName("com.mysql.jdbc.Driver");   
	Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
	String str="alter table Student add address varchar(20)";
     Statement stmt=con.createStatement();
    stmt.execute(str);
	 System.out.println("Table is altered");
      con.close();
    }
   }

 

DropTable.java

import java.sql.*;
public class DropTable
{

	public static void main(String args[]) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");   
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/College?useSSL=false","root","mysql");
		String str="DROP TABLE student";
		Statement stmt=con.createStatement();
		stmt.execute(str);
		con.close();
		System.out.println("table is Dropped");

	}
	}

 

 

Table in Oracle

 

Emp  (Table Name)

 

    EmpId(varchar2)

    EmpName(varchar2)

    EmpJob(varchar2)

    Salary(Number)

Read me of type 4 driver(For Oracle):-

1) in case of type-4 driver oracle driver is

      oracle.jdbc.driver.OracleDriver

          this class need to be loaded

 

Class.forName("oracle.jdbc.driver.OracleDriver");

2)For Connection:-
 

  "jdbc:oracle:thin:@hostaddress:portNo:serviceName“

Ex:-

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

 

where jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which oracle is running, we may also use IP address, 1521 is the port number and XE is the Oracle service name.

 

these values are stored by oracle in a text file name is tnsnames.ora( the path of this file is as follows

  C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN

 

  C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

3) implementation classes of jdbc type4 driver are provided by oracle in a jar file     that is (ojdbc14.jar/ojdbc6.jar)

 

this jar file must be available in the classpath because it is not the part of jdk.

 

There are two ways to set the classpath:

• temporary

• permament

  • ->temporary

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

// set classpath=%classpath%;C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;

 

(or we can copy that jar file in E: drive) then

 

//set classpath=%classpath%;E:\ojdbc14.jar;.;

 

  • Permanent:-

How to set the permanent classpath:-

For that right click on the MyComputer->Properties->Advanced System Settings->Environment variable then click on new tab.

In variable name write classpath

and in variable value paste the path of ojdbc14.jar

 

%classpath%;e:\ojdbc14.jar.;

 



Querying and Modifying Data Using the PreparedStatement
 

  • The PreparedStatement interface is derived from the Statement interface and is available in the java.sql package

 

  • The PreparedStatement interface Allows you to pass runtime parameters to the SQL statements to query and modify the data in a table.
  • Difference b/w Statement and PrerparedStatement:->

 

                   1)PreparedStatement support parameters where as Statement does not.

                   2) PreparedStatement provides better performance as compared to the Statement, If same query is to be executed multiple time with different values.

In a database  execution of a query requires four step

 

1) Query is parsed

2) Parsed query is compiled

3) Query is optimized

4) Query is Executed

  • In case of Statement all the four steps are performed each time when query is submitted for execution.

 

  • Where as  In case of PreparedStatement first three steps are performed only once when the query is submitted for the first time and only the last step is performed in subsequence submitted.

 

  • i.e. if same query is to be executed multiple times then PreparedStatement provide better performance then the Statement.

                                

 Using  PreparedStatement interface: -

 

PreparedStatement  provides the facility of executing parameterized queries

 

 following steps are required to use a PreparedStatement

 

Step I: -

1.1) PreparedStatement Object is created.

 

      prepareStatement() method of Connection interface is used for this purpose

Syntax:-

 

public PreparedStatement prepareStatement(String Querywithparameter)throws SQLException

 

Note: - ? is used to represent parameter in the query.

 

Step II: -

The value of each ‘?’ parameter is set by calling an appropriate setXXX() method, where XXX is the data type of the parameter

 

public void setXXX(int paramIndex,type value);

 

Ex:-

                    stat.setInt(1,100);

stat.setString(2,“Raj");

Step III: -

Query is executed

paste the ojdbc14.jar(This is for oracle 10 g/ojdbc6.jar(This is for oracle 11g/mysql-connector-java-5.1.45-bin.jar file in JRE/lib/ext folder:

 

 

This is an alternative of classpath


Next