Java Database Connectivity(JDBC) Tutorial

Java Database Connectivity:

JDBC is a Java database connectivity technology (Java Standard Edition platform) from Oracle Corporation. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.

JDBC drivers:

JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the DBMS can understand.

Types:

There are commercial and free drivers available for most relational database servers. These drivers fall into one of the following types:
JDBC technology drivers fit into one of four categories.
1.     JDBC-ODBC bridge
2.     Native-API Driver
3.     Network-Protocol Driver(MiddleWare Driver)
4.     Database-Protocol Driver(Pure Java Driver


   1. JDBC-ODBC bridge:
The JDBC type 1 driver, also known as the JDBC-ODBC bridge, is a database driver implementation that employs the ODBC driver to connect to the database. The driver converts JDBC method calls into ODBC function calls.

Sun provides a JDBC-ODBC Bridge driver: sun.jdbc.odbc.JdbcOdbcDriver. This driver is native code and not Java, and is closed source.

Advantages:

  • Almost any database for which an ODBC driver is installed can be accessed, and data can be retrieved.

Disadvantages:

Performance overhead since the calls have to go through the jdbc Overhead bridge to the ODBC driver, then to the native db connectivity interface (thus may be slower than other types of drivers)

2.     Native-API Driver

The JDBC type 2 driver, also known as the Native-API driver, is a database driver implementation that uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API.For example: Oracle OCI driver is a Type 2 Driver.

Advantages:
As there is no implementation of jdbc-odbc bridge, its considerably faster than a type 1 driver.

Disadvantages:
The vendor client library needs to be installed on the client machine.

3 .     Network-Protocol Driver(MiddleWare Driver)

The JDBC type 3 driver, also known as the Pure Java Driver for Database Middleware, is a database driver implementation which makes use of a middle tier between the calling program and the database. The middle-tier (application server) converts JDBC calls directly or indirectly into the vendor-specific database protocol.

Advantages:

Since the communication between client and the middleware server is database independent, there is no need for the database vendor library on the client. The client need not be changed for a new database.

Disadvantages:

· The middleware layer added may result in additional latency, but is typically overcome by using better middleware services.

4.     Database-Protocol Driver(Pure Java Driver)

Schematic of the Native-Protocol driver.The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor-specific database protocol.

Advantages:

· Completely implemented in Java to achieve platform independence.
· These drivers don't translate the requests into an intermediary format (such as ODBC).

Disadvantages:

  • Drivers are database dependent, as different database vendors use widely different (and usually proprietary) network protocols.

JDBC Basics – Java Database Connectivity Steps:

Step 1: Loading a database driver: The first thing you need to do before you can open a database connection is to load the JDBC driver for the database.


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

Step 2: Opening the Connection
To open a database connection you use the java.sql.DriverManager class.
 String url      = "jdbc:h2:~/test";  
String user     = "sa";
String password = "";

Connection connection =
    DriverManager.getConnection(url, user, password);


Step 3: Creating a jdbc Statement object: Once a connection is obtained we can interact with the database.
Statement statement = connection.createStatement();
A statement object is used to send and execute SQL statements to a database.

Statement: Execute simple sql queries without parameters.
Statement createStatement()
Creates an SQL Statement object.

Prepared Statement: Execute precompiled sql queries with or without parameters.
PreparedStatement prepareStatement(String sql)
returns a new PreparedStatement object. PreparedStatement objects are precompiled
SQL statements.

Callable Statement: Execute a call to a database stored procedure.
CallableStatement prepareCall(String sql)
returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

Step 4: Closing the Connection
Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:
connection.close();

Example-

import java.sql.*;

class TestRetrieve
{
 public static void main(String[] args)
 {
  try
  {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection c=DriverManager.getConnection("Jdbc:Odbc:mydsn");
   Statement s=c.createStatement();
   ResultSet result1=s.executeQuery("select * from emp");
   while(result1.next())
   {
    System.out.println(result1.getString(1));
    System.out.println(result1.getString(2));
   }
  }catch(SQLException e)
     {
      System.out.println(e);
     }
   catch(Exception i)
   {
    System.out.println(i);
   }
  
 }
}