Sunday, April 13, 2008

JDBC 4.0

First of all Thanks to the Java SE Service Provider Java developers no longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver. The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.

JDBC 4.0 also provides an improved developer experience by minimizing the boiler-plate code we need to write in Java applications that access relational databases. It also provides utility classes to improve the JDBC driver registration and unload mechanisms as well as managing data sources and connection objects.

With JDBC 4.0, Java developers can now specify SQL queries using Annotations, taking the advantage of metadata support available with the release of Java SE 5.0 (Tiger). Annotation-based SQL queries allow us to specify the SQL query string right within the Java code using an Annotation keyword. This way we don't have to look in two different files for JDBC code and the database query it's calling. For example, if you have a method called getActiveLoans() to get a list of the active loans in a loan processing database, you can decorate it with a @Query(sql="SELECT * FROM LoanApplicationDetails WHERE LoanStatus = 'A'") annotation.

Also, the final version of the Java SE 6 development kit (JDK 6)--as opposed to the runtime environment (JRE 6)--will have a database based on Apache Derby bundled with it. This will help developers explore the new JDBC features without having to download, install, and configure a database product separately.

The major features added in JDBC 4.0 include:

1. Auto-loading of JDBC driver class

2. Connection management enhancements

3. Support for RowId SQL type

4. DataSet implementation of SQL using Annotations

5. SQL exception handling enhancements

6. SQL XML support

There are also other features such as improved support for large objects (BLOB/CLOB) and National Character Set Support. These features are examined in detail in the following section.

Auto-Loading of JDBC Driver

In JDBC 4.0, we no longer need to explicitly load JDBC drivers using Class.forName(). When the method getConnection is called, the DriverManager will attempt to locate a suitable driver from among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.

The DriverManager methods getConnection and getDrivers have been enhanced to support the Java SE Service Provider mechanism (SPM). According to SPM, a service is defined as a well-known set of interfaces and abstract classes, and a service provider is a specific implementation of a service. It also specifies that the service provider configuration files are stored in the META-INF/services directory. JDBC 4.0 drivers must include the file META-INF/services/java.sql.Driver. This file contains the name of the JDBC driver's implementation of java.sql.Driver. For example, to load the JDBC driver to connect to a Apache Derby database, the META-INF/services/java.sql.Driver file would contain the following entry:


Let's take a quick look at how we can use this new feature to load a JDBC driver manager. The following listing shows the sample code that we typically use to load the JDBC driver. Let's assume that we need to connect to an Apache Derby database, since we will be using this in the sample application explained later in the article:

    Connection conn =
        DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);

But in JDBC 4.0, we don't need the Class.forName() line. We can simply call getConnection() to get the database connection.

Note that this is for getting a database connection in stand-alone mode. If you are using some type of database connection pool to manage connections, then the code would be different.

Connection Management

Prior to JDBC 4.0, we relied on the JDBC URL to define a data source connection. Now with JDBC 4.0, we can get a connection to any data source by simply supplying a set of parameters (such as host name and port number) to a standard connection factory mechanism. New methods were added to Connection and Statement interfaces to permit improved connection state tracking and greater flexibility when managing Statement objects in pool environments. The metadata facility (JSR-175) is used to manage the active connections. We can also get metadata information, such as the state of active connections, and can specify a connection as standard (Connection, in the case of stand-alone applications), pooled (PooledConnection), or even as a distributed connection (XAConnection) for XA transactions. Note that we don't use the XAConnection interface directly. It's used by the transaction manager inside a Java EE application server such as WebLogic, WebSphere, or JBoss.

RowId Support

The RowID interface was added to JDBC 4.0 to support the ROWID data type which is supported by databases such as Oracle and DB2. RowId is useful in cases where there are multiple records that don't have a unique identifier column and you need to store the query output in a Collection (such Hashtable) that doesn't allow duplicates. We can use ResultSet's getRowId() method to get a RowId and PreparedStatement's setRowId() method to use the RowId in a query.

An important thing to remember about the RowId object is that its value is not portable between data sources and should be considered as specific to the data source when using the set or update methods in PreparedStatement and ResultSet respectively. So, it shouldn't be shared between different Connection and ResultSet objects.

The method getRowIdLifetime() in DatabaseMetaData can be used to determine the lifetime validity of the RowId object. The return value or row id can have one of the values listed in Table 1.

RowId Value



Doesn't support ROWID data type.


Lifetime of the RowID is dependent on database vendor implementation.


Lifetime of the RowID is within the current transaction as long as the row in the database table is not deleted.


Lifetime of the RowID is the duration of the current session as long as the row in the database table is not deleted.


Lifetime of the RowID is unlimited as long as the row in the database table is not deleted.

Annotation-Based SQL Queries

The JDBC 4.0 specification leverages annotations (added in Java SE 5) to allow developers to associate a SQL query with a Java class without writing a lot of code to achieve this association. Also, by using the Generics (JSR 014) and metadata (JSR 175) APIs, we can associate the SQL queries with Java objects specifying query input and output parameters. We can also bind the query results to Java classes to speed the processing of query output. We don't need to write all the code we usually write to populate the query result into a Java object. There are two main annotations when specifying SQL queries in Java code: Select and Update.

Select Annotation

The Select annotation is used to specify a select query in a Java class for the get method to retrieve data from a database table. Table 2 shows various attributes of the Select annotation and their uses.






SQL Select query string.



Same as sql attribute.



Name of the database table against which the sql will be invoked.

readOnly, connected, scrollable


Flags used to indicate if the returned DataSet is read-only or updateable, is connected to the back-end database, and is scrollable when used in connected mode respectively.



Flag to indicate if the column names in the sql annotation element are mapped 1-to-1 with the fields in the DataSet.

Here's an example of Select annotation to get all the active loans from the loan database:

interface LoanAppDetailsQuery extends BaseQuery {
        @Select("SELECT * FROM LoanDetais where LoanStatus = 'A'")
        DataSet getAllActiveLoans();

The sql annotation allows I/O parameters as well (a parameter marker is represented with a question mark followed by an integer). Here's an example of a parameterized sql query.

interface LoanAppDetailsQuery extends BaseQuery {
        @Select(sql="SELECT * from LoanDetails
                where borrowerFirstName= ?1 and borrowerLastName= ?2")
        DataSet getLoanDetailsByBorrowerName(String borrFirstName,
                String borrLastName);

Update Annotation

The Update annotation is used to decorate a Query interface method to update one or more records in a database table. An Update annotation must include a sql annotation type element. Here's an example of Update annotation:

interface LoanAppDetailsQuery extends BaseQuery {
        @Update(sql="update LoanDetails set LoanStatus = ?1
                where loanId = ?2")
        boolean updateLoanStatus(String loanStatus, int loanId);

SQL Exception Handling Enhancements

Exception handling is an important part of Java programming, especially when connecting to or running a query against a back-end relational database. SQLException is the class that we have been using to indicate database related errors. JDBC 4.0 has several enhancements in SQLException handling. The following are some of the enhancements made in JDBC 4.0 release to provide a better developer's experience when dealing with SQLExceptions:

1. New SQLException sub-classes

2. Support for causal relationships

3. Support for enhanced for-each loop

New SQLException classes

The new subclasses of SQLException were created to provide a means for Java programmers to write more portable error-handling code. There are two new categories of SQLException introduced in JDBC 4.0:

  • SQL non-transient exception
  • SQL transient exception

Non-Transient Exception: This exception is thrown when a retry of the same JDBC operation would fail unless the cause of the SQLException is corrected. Table 3 shows the new exception classes that are added in JDBC 4.0 as subclasses of SQLNonTransientException (SQLState class values are defined in SQL 2003 specification.):

Exception class

SQLState value













Transient Exception: This exception is thrown when a previously failed JDBC operation might be able to succeed when the operation is retried without any intervention by application-level functionality. The new exceptions extending SQLTransientException are listed in Table 4.

Exception class

SQLState value







Causal Relationships

The SQLException class now supports the Java SE chained exception mechanism (also known as the Cause facility), which gives us the ability to handle multiple SQLExceptions (if the back-end database supports a multiple exceptions feature) thrown in a JDBC operation. This scenario occurs when executing a statement that may throw more than one SQLException .

We can use getNextException() method in SQLException to iterate through the exception chain. Here's some sample code to process SQLException causal relationships:

catch(SQLException ex) {
     while(ex != null) {
        LOG.error("SQL State:" + ex.getSQLState());
        LOG.error("Error Code:" + ex.getErrorCode());
        LOG.error("Message:" + ex.getMessage());
        Throwable t = ex.getCause();
        while(t != null) {
            LOG.error("Cause:" + t);
            t = t.getCause();
        ex = ex.getNextException();

Enhanced For-Each Loop

The SQLException class implements the Iterable interface, providing support for the for-each loop feature added in Java SE 5. The navigation of the loop will walk through SQLException and its cause. Here's a code snippet showing the enhanced for-each loop feature added in SQLException.

catch(SQLException ex) {
     for(Throwable e : ex ) {
        LOG.error("Error occurred: " + e);

Support for National Character Set Conversion

Following is the list of new enhancements made in JDBC classes when handling the National Character Set:

1. JDBC data types: New JDBC data types, such as NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB were added.

2. PreparedStatement: New methods setNString, setNCharacterStream, and setNClob were added.

3. CallableStatement: New methods getNClob, getNString, and getNCharacterStream were added.

4. ResultSet: New methods updateNClob, updateNString, and updateNCharacterStream were added to ResultSet interface.

Enhanced Support for Large Objects (BLOBs and CLOBs)

The following is the list of enhancements made in JDBC 4.0 for handling the LOBs:

1. Connection: New methods (createBlob(), createClob(), and createNClob()) were added to create new instances of BLOB, CLOB, and NCLOB objects.

2. PreparedStatement: New methods setBlob(), setClob(), and setNClob() were added to insert a BLOB object using an InputStream object, and to insert CLOB and NCLOB objects using a Reader object.

3. LOBs: There is a new method (free()) added in Blob, Clob, and NClob interfaces to release the resources that these objects hold.

Now, let's look at some of the new classes added to the java.sql and javax.jdbc packages and what services they provide.

JDBC 4.0 API: New Classes

RowId (java.sql)

As described earlier, this interface is a representation of an SQL ROWID value in the database. ROWID is a built-in SQL data type that is used to identify a specific data row in a database table. ROWID is often used in queries that return rows from a table where the output rows don't have an unique ID column.

Methods in CallableStatement, PreparedStatement, and ResultSet interfaces such as getRowId and setRowId allow a programmer to access a SQL ROWID value. The RowId interface also provides a method (called getBytes()) to return the value of ROWID as a byte array. DatabaseMetaData interface has a new method called getRowIdLifetime that can be used to determine the lifetime of a RowId object. A RowId's scope can be one of three types:

1. Duration of the database transaction in which the RowId was created

2. Duration of the session in which the RowId was created

3. The identified row in the database table, as long as it is not deleted

DataSet (java.sql)

The DataSet interface provides a type-safe view of the data returned from executing of a SQL Query. DataSet can operate in a connected or disconnected mode. It is similar to ResultSet in its functionality when used in connected mode. A DataSet, in a disconnected mode, functions similar to a CachedRowSet. Since DataSet extends List interface, we can iterate through the rows returned from a query.

There are also several new methods added in the existing classes such as Connection (createSQLXML, isValid) and ResultSet (getRowId).

Sample Application

The sample application included with this article is a loan processing application that includes a loan search page where the user submits the form by entering a loan ID to get loan details. The loan search page calls a controller object that in turn calls a DAO object to access the back-end database to retrieve the loan details. These details include borrower name, loan amount, and loan expiration date, which are displayed on a loan details screen. In the back-end database, we have a table called LoanApplicationDetails to store the details of the loan application.

The use case of the sample application is to get loan details for a specified loan ID. The loan details are available for retrieval once a loan is registered and locked for a mortgage product and interest rate combination. The project details of the loan processing application are shown in Table 5.



Project Name


Project Directory


DB Directory


JDK Directory


IDE Directory



Apache Derby


6.0 (beta 2 release)


Eclipse 3.1

Unit Testing

JUnit 4


Ant 1.6.5

The table below lists the JDBC parameters we need to connect to the loan details Apache Derby database. These parameters are stored in a text file called, which is located in the etc/jdbc directory under the project base directory (see Table 6).



JDBC Driver File






User Id




Note: Apache Derby database provides two types of JDBC drivers: Embedded Driver (org.apache.derby.jdbc.EmbeddedDriver) and Client/Server Driver (org.apache.derby.jdbc.ClientDriver). I used the Client/Server Driver version in the sample application.

The following are the commands to start the Derby database server and to create the new database using the ij tool.

To start Derby Network Server, open a command prompt and run the following commands (change DERBY_INSTALL and JAVA_HOME environment variables to reflect your local environment).

set DERBY_INSTALL=C:\dev\dbservers\db-derby-
set JAVA_HOME=C:\dev\java\jdk1.6.0
set DERBY_INSTALL=C:\dev\dbservers\db-derby-
cd %DERBY_INSTALL%\frameworks\NetworkServer\bin

To connect to the database server and create the test database, open another command prompt and run the following commands. Make sure to change DERBY_INSTALL and JAVA_HOME environment variables to suit to your environment.

set JAVA_HOME=C:\dev\java\jdk1.6.0
set DERBY_INSTALL=C:\dev\dbservers\db-derby-
set CLASSPATH=%DERBY_INSTALL%\lib\derbyclient.jar;
connect 'jdbc:derby://localhost:1527/LoanDB;create=true';


The classpath setting to compile the Java source should include the derby.jar and junit4.jar files located in the lib directory under project main directory. We also need to include etc, etc/jdbc and etc/log4j directories in the classpath so the application can access the JDBC properties and Log4J configuration files. I created an Ant build script (located in JdbcApp/build directory) to automate the tasks of compiling and packaging the Java code.

The test class used to test the loan details data access object is called LoanAppDetailsDAOTest. We pass in parameters such as loan ID and borrower name to get the loan details.

The following section shows code examples on auto-loading the JDBC driver and annotation-based SQL query features of JDBC 4.0 specification.

JDBC Driver Auto-Loading

The BaseDAO abstract class has a method called getConnection to get a database connection. The following code snippet shows this method (notice that we don't have to register the JDBC driver). The JDBC driver is automatically loaded as long as the appropriate driver class name (org.apache.derby.jdbc.ClientDriver) is located in java.sql.Driver file.

protected Connection getConnection() throws DAOException {
        // Load JDBC properties first
        if (jdbcUrl == null || jdbcUser == null ||
                        jdbcPassword == null) {
        // Get Connection
        Connection conn = null;
        try {
                conn = DriverManager.getConnection(jdbcUrl, jdbcUser,
        } catch (SQLException sqle) {
                throw new DAOException("Error in getting a DB connection.",
        return conn;

SQL Annotations

LoanAppDetailsQuery interface has the annotated SQL queries to get a list of active loans (criteria is loanstatus="A") and loan details based on the borrower name (in the case of a single borrower owning multiple loans). We saw these SQL annotations earlier in the article. Here's the sample code showing how we can call the SQL query defined using an Annotation.

public DataSet getAllActiveLoans() throws Exception {
        // Get Connection
        Connection conn = getConnection();
        LoanAppDetailsQuery query = null;
        DataSet loanDetails = null;
        query = QueryObjectFactory.createQueryObject(
                        LoanAppDetailsQuery.class, conn);
        loanDetails = query.getAllActiveLoans();
        return loanDetails;
public DataSet getLoanDetailsByBorrowerName(
                String borrFirstName, String borrLastName) throws Exception {
        // Get Connection
        Connection conn = getConnection();
        LoanAppDetailsQuery query = null;
        DataSet loanDetails = null;
        query = QueryObjectFactory.createQueryObject(
                        LoanAppDetailsQuery.class, conn);
        loanDetails = query.getLoanDetailsByBorrowerName(
        return loanDetails;

JDBC Basics

Driver Manager


1. Types of Drivers

There are many possible implementations of JDBC drivers. These implementations are categorized as follows:

      • Type 1 - drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.
      • Type 2 - drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.
      • Type 3 - drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client?s requests to the data source.
      • Type 4 - drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.


Connection con = DriverManager.getConnection(url, "Fernanda", "J8");


 InitialContext ic = new InitialContext()
 DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
 Connection con = ds.getConnection();
 DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()

Connection con = ds.getConnection();

Create statement


Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Resultset Type




Prepared - Statement

PreparedStatement updateSales = con.prepareStatement(

Return Values for the executeUpdate Method

Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate being assigned to the variable n:

updateSales.setInt(1, 50); 
updateSales.setString(2, "Espresso"); 
int n = updateSales.executeUpdate();
// n = 1 because one row had a change in it

The table COFFEES was updated by having the value 50 replace the value in the column SALES in the row for Espresso. That update affected one row in the table, so n is equal to 1.

When the method executeUpdate is used to execute a DDL statement, such as in creating a table, it returns the int 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES, n is assigned a value of 0:

int n = executeUpdate(createTableCoffees); // n = 0

Note that when the return value for executeUpdate is 0, it can mean one of two things:

  • the statement executed was an update statement that affected zero rows
  • the statement executed was a DDL statement

Committing a Transaction

Once auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit. The following code, in which con is an active connection, illustrates a transaction:

PreparedStatement updateSales = con.prepareStatement(
updateSales.setInt(1, 50);
updateSales.setString(2, "Colombian");
PreparedStatement updateTotal = con.prepareStatement(
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");

Using Transactions to Preserve Data Integrity

In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. For instance, suppose that an employee was supposed to enter new coffee prices in the table COFFEES but delayed doing it for a few days. In the meantime, prices rose, and today the owner is in the process of entering the higher prices. The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. After inserting the outdated prices, the employee realizes that they are no longer valid and calls the Connection method rollback to undo their effects. (The method rollback aborts a transaction and restores values to what they were before the attempted update.) At the same time, the owner is executing a SELECT statement and printing out the new prices. In this situation, it is possible that the owner will print a price that was later rolled back to its previous value, making the printed price incorrect.

This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time.

To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) Once a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)

How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.

Calling a Stored Procedure from JDBC

JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

String createProcedure = "create procedure SHOW_SUPPLIERS " +
                        "as " +
                        "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
                        "from SUPPLIERS, COFFEES " +
                        "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
                        "order by SUP_NAME";
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();


JDBC 4.0 provides ease of development and improves the developer experience when working with SQL. Another goal of JDBC 4.0 is to provide enterprise-level JDBC features to expose the API to a richer set of tools to manage JDBC resources. Also, the JDBC 4.0 API provides a migration path for JDBC drivers to be compliant with the J2EE Connector architecture (JCA). This provides JDBC vendors the ability to move toward implementing the JDBC technology Connector API. This is important in the context of using a JDBC data source in an enterprise Service Oriented Architecture (SOA) application where JDBC data source can be deployed as just another adapter in the Enterprise Service Bus (ESB) architecture without having to write ESB specific implementation code for the JDBC data source.

In this article, we looked at JDBC 4.0 enhancements, such as RowId support, JDBC driver loading, and Annotations-based SQL. There will be other features added in JDBC 4.0 in the future to support SQL:2003 specification. For more information on JDBC 4.0 spec, refer to the specification document.

1 comment:

Tanika Co Valda said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training in India . Nowadays Java has tons of job opportunities on various vertical industry.