Introduction to Java Database Connectivity

In this article, we discuss the different ways in which Java can communicate with a database. We will talk about the traditional different kinds of drivers and also discuss some code to connect to a database in order to run queries.

Introduction

Java runs on a Java Virtual Machine (JVM). JVM translates your application code to bytecodes. A database is a separate software system. In order for these two to talk, they need to have a communication channel. Sun offers, through its J2SE, a package entirely dedicated for performing database-related operations. This is the JDBC package.

Part I - Concept of a Driver

Just the same way, your printer needs a driver, or maybe, your new soundcard needs a driver for the underlying hardware to understand, a database needs a driver. A database can be accessed by proprietary API's (Application Programming Interfaces). The Java Programmer Joe does not know the intricate API's of the database. Here is where the concept of driver comes in. The Java programmer makes JDBC calls from his program. The JVM translates the code to the database API. One needs a database driver either from a database vendor, or a J2EE server vendor.

There are four kinds of industry-standard JDBC drivers:

Type 1 - JDBC-ODBC Bridge

The JDBC-ODBC bridge (this type of driver is called the JDBC-ODBC bridge) is responsible for translating JDBC communication to ODBC language calls.

Type 2 - Both Native and Java Driver

Type 2 Drivers are a layer less than Type 1. Consider the diagram below:

The driver translates JDBC calls to database-specific API's. A Type 2 driver needs to be installed on both the JVM and the machine which hosts the database. The Oracle OCI JDBC driver is a Type 2 driver. This OCI driver requires an installation of Oracle's SQL*Net to provide communication to the database server.

Type 3 - Database Server Driver

Type 3 drivers connect multiple clients to multiple databases by being an intermediate database server.

Type 4 - 100% Java Driver

As you see, this driver goes through the least number of stopping stations before talking to the database. Consequently, this is the most efficient in terms of database-access time. Oracle provides a Type 4 driver, which is commonly referred to as the "thin" JDBC driver. One can connect with the Thin Driver without SQL*Net installed on the client side.

Part II - Making the Connection

Now that you have installed a driver, let us see how we can access the database using core JDBC API.

A driver implements the java.sql.Driver interface. Drivers have to be registered with the DriverManager (java.sql.DriverManager class). The DriverManager is a static class, and a driver is registered using the method:

public static void registerDriver(java.sql.Driver dr);

A driver is loaded in your application using reflection:

try{
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   ...
   ...
}
catch (ClassNotFoundException cnfe){ }

Concept of a JDBC URL

URL (Universal Resource Locator) is a unique string to identify a resource. Hence, a JDBC URL is a string that uniquely identifies a database. A JDBC URL looks like:

jdbc: weblogic: oracle

The first part will always be "jdbc". The second token is the name of the driver or database. You will get the exact URL from the application server documentation.

In your code, you will do something like this:

String url = "jdbc:weblogic:oracle";
String user= "java_developer";
String pwd = "oracle";

Connection con =  DriverManager.getConnection(url, user, pwd);

The getConnection() method is overloaded. Other method signatures are:

public static Connection getConnection(String url) throws SQLException;
public static Connection getConnection(String url, java.util.Properties prop)
	throws SQLException;

The Properties object will have properties such as auto-commit, etc.

Executing Queries

Now that you have a hold of a database connection, you will need to run queries against your database. The query has to be a statement. The connection object has methods:

Statement createStatement() throws SQLException;
public PreparedStatement prepareStatement(String sql) throws SQLException;

On the Statement and PreparedStatement, you can call:

execute();
executeQuery();

Although there are other variants of Statements and executes, the scope of this article is to give the basic overall of java connectivity to the database.

Executing the queries bring back a ResultSet object, which is then enumerated to retrieve results.

Summary

Sun maintains a searchable list of drivers at http://servlet.java.sun.com/products/jdbc/drivers. When you download a JDK, it ships with a Type 1 Driver.

More information about the Oracle specific JDBC drivers can be found in the Oracle/Java FAQ at http://www.orafaq.com/faq/jdbc

Prepared by Saikat Goswami, Boston, Massachussets, sai_nyc@hotmail.com