JDBC (Java Database Connectivity) and Drivers

 

JDBC

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. There are four types of JDBC drivers:

• JDBC-ODBC Bridge
• NATIVE DRIVER
• NETWORK PROTOCOL DRIVER
• THIN DRIVER

We have discussed the above four drivers in the next chapter.

We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database. It is like Open Database Connectivity (ODBC) provided by Microsoft. 

The current version of JDBC is 4.3. It is the stable release since 21st September, 2017. It is based on the X/Open SQL Call Level Interface. The java.sql package contains classes and interfaces for JDBC API. A list of popular interfaces of JDBC API are given below: 

o Driver interface 

o Connection interface 

o Statement interface 

o PreparedStatement interface 

o CallableStatement interface 

o ResultSet interface 

o ResultSetMetaData interface 

o DatabaseMetaData interface 

o RowSet interface

A list of popular classes of JDBC API are given below

•  DriverManager class 
•  Blob class 
• Clob class 
• Types class 

Why Should We Use JDBC 

Before JDBC, ODBC API was the database API to connect and execute the query with the database. But, ODBC API uses ODBC driver which is written in C language (i.e. platform-dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language). 

We can use JDBC API to handle database using Java program and can perform the following activities: 

1. Connect to the database 

2. Execute queries and update statements to the database 

3. Retrieve the result received from the database. 

JDBC Driver 

1. JDBC Drivers

1.    JDBC-ODBC bridge driver

2.    Native-API driver

3.    Network Protocol driver

4.    Thin driver

JDBC Driver is a software component that enables java application to interact with the database. There drivers:

1.    JDBC-ODBC bridge driver

2.    Native-API driver (partially java driver)

3.    Network Protocol driver (fully java driver)

4.    Thin driver (fully java driver)

1) JDBC-ODBC bridge driver 

JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driv method calls into the ODBC function calls. This is now discouraged because of thin driver.

In Java 8, the JDBC-ODBC Bridge has been removed.

Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle recommends that you use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC Bridge.

Advantages:

o   easy to use.

o   can be easily connected to any database.

Disadvantages:

o   Performance degraded because JDBC method call is converted into the ODBC function calls.

o   The ODBC driver needs to be installed on the client machine. 

2) Native-API driver

The Native API driver uses the client-side libraries of the database. The driver converts JDBC method ca the database API. It is not written entirely 

Advantage: 

o performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

o The Native driver needs to be installed on the each client machine. 

o The Vendor client library needs to be installed on client machine. 

3) Network Protocol driver

The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java

Advantage:

o   No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc.

Disadvantages:

o   Network support is required on client machine. 

o Requires database-specific coding to be done in the middle tier.

o   Maintenance of Network Protocol driver becomes costly because it requires databasespecific coding to be done in the middle tier. 

4) Thin driver

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

Advantage:

o   Better performance than all other drivers.

  o No software is required at client side or server side.

Disadvantage:

o   Drivers depend on the Database.

Java Database Connectivity 

There are 5 steps to connect any java application with the database using JDBC. 

These steps are as follows 

o Register the Driver class 

o Create a connection 

o Create statement 

o Execute queries 

o Close connection 

 

1) Register the driver class

The forName() method of Class class is used to register the driver class. This method is used to dynam class.

Syntax of forName() method

1. public static void forName(String className)throws ClassNotFoundException  

Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put the vender's Jar in the classpath, and then JDBC The driver manager can detect and load the driver automatically.

Example to register the OracleDriver class

Here, Java program is loading oracle driver to esteblish database connection.

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

2) Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database

Syntax of getConnection() method

1.    1) public static Connection getConnection(String url)throws SQLException  

2.    2) public static Connection getConnection(String url,String name,String password)  

3.    throws SQLException  

Example to establish connection with the Oracle database

1.    Connection con=DriverManager.getConnection(  

2.    "jdbc:oracle:thin:@localhost:1521:xe","system","password");  

 

3) Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statem execute queries with the database.

Syntax of createStatement() method

1. public Statement createStatement()throws SQLException  

Example to create the statement object

1. Statement stmt=con.createStatement();  

 

4) Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This met object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

1. public ResultSet executeQuery(String sql)throws SQLException  

Example to execute query

1. ResultSet rs=stmt.executeQuery("select * from emp");  

    

3.    while(rs.next()){  

4.    System.out.println(rs.getInt(1)+" "+rs.getString(2));  

5.    }  

 

5) Close the connection object

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

Syntax of close() method

1. public void close()throws SQLException  

Example to close connection

1. con.close();  

Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.

It avoids explicit connection closing step.

Java Database Connectivity with Oracle

To connect java application with the oracle database, we need to follow 5 following steps. In this examp Oracle 10g as the database. So we need to know following information for the oracle database:

1.    Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.

2.    Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localho jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which or may also use IP address, 1521 is the port number and XE is the Oracle service name. You may ge information from the tnsnames.ora file.

3.    Username: The default username for the oracle database is system.

4.    Password: It is the password given by the user at the time of installing the oracle database.

import java.sql.*;   

class OracleCon

{   

public static void main(String args[])

{   try

{ 

//step1 load the driver class  

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

  

//step2 create  the connection object  Connection con=DriverManager.getConnection(   "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  

  

//step3 create the statement object  

Statement stmt=con.createStatement();  

  

//step4 execute query  

ResultSet rs=stmt.executeQuery("select * from emp");   while(rs.next())  

System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  

  

//step5 close the connection object   con.close();  

  

}catch(Exception e){ System.out.println(e);}  

  

}  

}  

DriverManager class

Method	Description
1) public static void registerDriver(Driver driver):	is used to register the given driver with
2) public static void deregisterDriver(Driver driver):	is used to deregister the given driver ( from the list) with DriverManager.
3) public static Connection getConnection(String url):	is used to establish the connection with
4) public static Connection getConnection(String url,String userName,String password):	is used to establish the connection with username and password.

The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().

Useful methods of DriverManager class

Connection interface

A Connection is a session between java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement and DatabaseMetaData. The Connection interface provide many methods for transaction management like commit(), rollback() etc.

By default, connection commits the changes after executing queries.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be used to execute SQL queries.

2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a Statement object that will generate ResultSet objects with the given type and concurrency.

3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true.

4) public void commit(): saves the changes made since the previous commit/rollback permanent.

5) public void rollback(): Drops all changes made since the previous commit/rollback.

6) public void close(): closes the connection and Releases a JDBC resources immediately.

Statement interface

The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:

1)  public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.

2)  public int executeUpdate(String sql): is used to execute specified query, it may be create, drop, insert, update, delete etc.

3)  public boolean execute(String sql): is used to execute queries that may return multiple results.

4)  public int[] executeBatch(): is used to execute batch of commands.

  

import java.sql.*;   

class FetchRecord

{   

public static void main(String args[])throws Exception{   Class.forName("oracle.jdbc.driver.OracleDriver");   

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","syst em","oracle");   Statement stmt=con.createStatement();  

  

//stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");  

//int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 where id

=33");  

int result=stmt.executeUpdate("delete from emp765 where id=33");   System.out.println(result+" records affected");   con.close();  

}}  

ResultSet interface

The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.

By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well as we can make this object as updatable by:

1.                                   Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  

2.                                   ResultSet.CONCUR_UPDATABLE);  

Commonly used methods of ResultSet interface

1) public boolean next():	is used to move the cursor to the one row next from the current position.
2) public boolean previous():	is used to move the cursor to the one row previous from the current position.
3) public boolean first():	is used to move the cursor to the first row in result set object.
4) public boolean last():	is used to move the cursor to the last row in result set object.
5) public boolean absolute(int row):	is used to move the cursor to the specified row number in the ResultSet object.
6) public boolean relative(int row):	is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
7) public int getInt(int columnIndex):	is used to return the data of specified column index of the current row as int.
8) public int getInt(String columnName):	is used to return the data of specified column name of the current row as int.
9) public String getString(int columnIndex):	is used to return the data of specified column index of the current row as String.
10) public String getString(String columnName):	is used to return the data of specified column name of the current row as String.

PreparedStatement interface

The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.

Let's see the example of parameterized query:

1. String sql="insert into emp values(?,?,?)";  

As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

 

How to get the instance of PreparedStatement?

The prepareStatement() method of Connection interface is used to return the object of PreparedStatement. Syntax:

1. public PreparedStatement prepareStatement(String query)throws SQLException{}  

 

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

Method                                                           Description
public void setInt(int paramIndex, int value)	sets the integer value to the given parameter index.
public void setString(int paramIndex, String value)	sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value)	sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value)	sets the double value to the given parameter index.
public int executeUpdate()	executes the query. It is used for create, drop, insert, update, delete etc.
public ResultSet executeQuery()	executes the select query. It returns an instance of ResultSet.

Example of PreparedStatement interface that inserts the record

First of all create table as given below:

1. create table emp(id number(10),name varchar2(50));   Now insert records in this table by the code given below:

import java.sql.*;

class InsertPrepared{

  

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

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

  

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

  

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");   stmt.setInt(1,101);//1 specifies the first parameter in the query   stmt.setString(2,"Ratan");  

  

int i=stmt.executeUpdate();  

System.out.println(i+" records inserted");  

  

con.close();  

  

}catch(Exception e){ System.out.println(e);}  

  

}  

}  

 

Example of PreparedStatement interface that updates the record

PreparedStatement stmt=con.prepareStatement("update emp set name=? where id=?");   stmt.setString(1,"Sonoo");//1 specifies the first parameter in the query i.e. name   stmt.setInt(2,101);  

  

int i=stmt.executeUpdate();  

System.out.println(i+" records updated");  

 

Example of PreparedStatement interface that deletes the record

PreparedStatement stmt=con.prepareStatement("delete from emp where id=?");   stmt.setInt(1,101);  

  

int i=stmt.executeUpdate();  

System.out.println(i+" records deleted");  

 

Example of PreparedStatement interface that retrieve the records of a table

PreparedStatement stmt=con.prepareStatement("select * from emp");   ResultSet rs=stmt.executeQuery();   while(rs.next()){  

System.out.println(rs.getInt(1)+" "+rs.getString(2));   

}  

 

Example of PreparedStatement to insert records until user press n

import java.sql.*;   import java.io.*;   class RS{   public static void main(String args[])throws Exception

{  

public static void main(String args[])throws Exception{  

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

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

  

PreparedStatement ps=con.prepareStatement("insert into emp130 values(?,?,?)");  

  

BufferedReader br=new BufferedReader(new InputStreamReader(System.in));   

  

do{  

System.out.println("enter id:");   int id=Integer.parseInt(br.readLine());  

System.out.println("enter name:");  

String name=br.readLine();   System.out.println("enter salary:");   float salary=Float.parseFloat(br.readLine());

 ps.setInt(1,id);   ps.setString(2,name);   ps.setFloat(3,salary);   int i=ps.executeUpdate(); 

  

   

System.out.println(i+" records affected");  

  

System.out.println("Do you want to continue: y/n");   String s=br.readLine();   if(s.startsWith("n")){   break;  

}  

}while(true);  

  

con.close();  

}}  

Difference between ODBC and JDBC

ODBC	JDBC
ODBC Stands for Open Database Connectivity.	JDBC Stands for java database connectivity.
Introduced by Microsoft in 1992.	Introduced by SUN Micro Systems in 1997.
We can use ODBC for any language like C,C++,Java etc.	We can use JDBC only for Java languages.
We can choose ODBC only Windows platform.	We can use JDBC in any platform.

          Mostly ODBC Driver developed in native languages like C, C++.          JDBC Stands for java database connectivity.

For Java applications, it is not recommended to use ODBC because performance will be down due to internal conversion and applications will become platform Dependent.

For Java application, it is highly recommended to use JDBC because there we no performance & platform dependent problem.