JDBC 3.0 - Part I

This article is the first of a series. In this series, we will discuss all the good things JDBC® 3.0 package has to offer. JDBC API provides a programmatic way of accessing data from Java code. Even though non-relational data can be accessed, the goal of JDBC and the technology revolves around accessing relational data. The JDBC is packaged in the java.sql and javax.sql packages.Introduction

JDBC 3.0 ships with both J2SE and J2EE. It comes with J2SE 1.4. For a basic understanding of database drivers and DriverManager, you can take a look at the last month's article "Understanding Java Database Connectivity" at http://www.orafaq.com/articles/.

Out DriverManager, In DataSource

Up until JDBC 1.0, the java.sql.DriverManager was used to register and load drivers and then establish connection. JDBC 2.0 Optional Package API introduced the javax.sql.DataSource interface for establishing connections. Just the way a DriverManager returns a Connection object, a DataSource returns a Connection object when a method getConnection() is called. While the DriverManager loads driver classes, a DataSource uses a JNDI lookup to retrieve a DataSource object. If you are wondering about JNDI, let us take a quick look at JNDI.

Quick Look at JNDI

JNDI stands for Java Naming and Directory Interface. It would just benefit us to understand JNDI as a rendezvous where we can 'bind' objects to a String name. Then, later, when we need the object, we just provide the 'rendezvous' the name. The 'rendezvous' is a service. JNDI is a naming and directory service.

DataSource - Getting to know it better

The application server implements the javax.sql.DataSource interface. It is also possible for a database vendor to implement the datasource. Creating a datasource in your code involves two steps, (a) instantiating an object that implements DataSource, (b) binding the object to JNDI with a name of your choice.
For example:

// MyDataSource is your application server's datasource. You have to
// check your vendor documentation.
MyDataSource myDS = new MyDataSource(...);
myDS.setServerName("my_server");
myDS.setNetworkProtocol(...);
...
// setting more properties
		
try {
   // InitialContext is a JNDI class implementing Context interface
   // InitialContext returns a starting point to bind your objects.
   Context context = new InitialContext();
   context.bind("jdbc/db", myDS);
}
catch(NamingException ne){
   // unable to create context, binding error, network failure etc.
}

Once you have 'bound' your datasource to your name, you can call methods to get the object.

Note: The InitialContext, in the example is instantiated using a no-argument constructor. You will use this when your JNDI service and the application server are on the same JVM. If they are not, you need to pass a Hashtable with properties so that the Context knows where to find the JNDI service.

Now that you have bound a datasource, let us see how we will retrieve it. Very simple.

try {
   Context context = new InitialContext();
   DataSource ds = (DataSource)context.lookup("jdbc/db");
}
catch(NamingException ne) {
   //failure to retrieve, network failure, etc.
}

Very similar to DriverManager, you call getConnection(..) on the datasource to get a Connection object. The methods are:

public Connection getConnection() throws SQLException;
public Connection getConnection(String username, String password)
	throws SQLException;

There are a few subtle differences between the Connection object returned by the DriverManager, and that returned by the DataSource, even though, they refer to the same java.sql.Connection. DriverManager returns a Connection object which encapsulates a physical connection to the database. DataSource, on the other hand, might return a connection from a pool of connections. We will discuss connection pooling in the coming articles.

Statements

Once you have a connection, you can ask it to give a Statement to you. Examples of simple Statement and PreparedStatement can be seen at my last month?s article. Since I have already discussed them, let us see an example of a CallableStatement. CallableStatement is used for executing stored procedures. For example:

CallableStatement cs = connection.prepareCall(
	"{ call myProcedure(?, ?) }");
cs.setString(1, "a");
cs.setString(2, "b");

ResultSets

ResultSets have gone through a lot of changes since JDBC 1.0. ResultSet is what is returned from the successful execution of a query.

ResultSet rs = cs.executeQuery();

To see results of query,

while( rs.next()) {

   String s = rs.getString(1);

}

ResultSets are placeholders for the data returned. ResultSets can be 3 types, based on sensitivity level: TYPE_FORWARD_ONLY (resultset is not scrollable), TYPE_SCROLL_INSENSITIVE (cursor can move both forward and backward), TYPE_SCROLL_SENSITIVE (cursor can move both forward and backward, but will reflect changes to database while it is open).

ResultSets can also be classified according to concurrency: CONCUR_READ_ONLY (result set cannot be updated), CONCUR_UPDATEABLE (result set can be updated). The third way of classifying ResultSets can be through holdability: HOLD_CURSORS_OVER_COMMIT (stay open when a commit is being done), CLOSE_CURSORS_AT_COMMIT (closed when a commit operation is performed).

JDBC 3.0 Features Supported By Oracle drivers

Oracle JDBC drivers support the following JDBC 3.0 features: (a) Global transactions and distributed transactions on the same connection, (b) Transaction Savepoints, (c) PreparedStatement Caching, (d) Full compliance with JDK 1.4

Global and Local Transactions

A local transaction is a transaction that is committed on a single database. Global transactions, (or distributed transactions) are transactions that might cover multiple databases. It usually involves multiple transactions. Each transaction in a distributed transaction is called a transaction branch.

Savepoints

Savepoints are used in local transactions only. Savepoints are used within a single transaction. Anything after a savepoint can be rolled back. A savepoint is encapsulated in a java.sql.Savepoint interface, and can be gotten from a Connection.

Statement Caching

Caching of prepared statements, as anyone might have guessed, improves speed and performance because it is cached. No more need to create a cursor for the database, no more repeated statement parsing. A Statement has to be associated with a connection. It can be associated with a OracleConnection, or a PooledConnection. When caching is enabled, the statement is cached even after a ?close? is called on the statement.

Summary

JDBC 3.0 is a SQL99-compliant technology, based on CLI (Call Level Interface). This has matured over the years, since it was first released in 1997. Well worth considering if you are developing in Java; or planing to develop in Java.

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