New features in JDBC 3.0

This article provides an introduction to the array of core new features available in JDBC 3.0. More specifically, the features ‘supporting save points’, ‘using parameter metadata’, ‘updating large objects’ and ‘auto generated keys’ are discussed. Wherever possible, to get a hang of it, relevant code samples have been provided in the respective sections.

Defining savepoints
It is possible to programmatically control the creation and releasing of save points through JDBC 3.0. But before doing it, one must check whether the underlying database supports the concepts of savepoints. Save points provide multiple-level-control of database commit or rollback operations within a single transaction. For example, if two unrelated database operations have to happen with a single transaction, these two save points can be created for the very purpose. Then based on various business conditions, commit/rollback can be done to any of these save points within the same transaction. To illustrate the usage, consider the following example: package net.javabeat.jdbc3.newfeatures.savepoint; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.Savepoint; import net.javabeat.jdbc3.newfeatures.CommonUtils; public class SavepointTest { public static void main(String[] args) throws Exception{ Connection connection = CommonUtils.getConnection(); DatabaseMetaData databaseMetadata = connection.getMetaData(); if (databaseMetadata.supportsSavepoints()){ System.out.println("Savepoints supported by the target database"); processTable(connection); } } private static void processTable(Connection connection) throws Exception{ PreparedStatement pStatement = connection.prepareStatement(" INSERT INTO CUSTOMER VALUES (?, ?)"); pStatement.setInt(1, 1); pStatement.setString(2, "New Customer"); pStatement.execute(); Savepoint countrySavepoint = connection.setSavepoint("country"); pStatement = connection.prepareStatement(" INSERT INTO COUNTRY VALUES (?, ?)"); pStatement.setInt(1, 1); pStatement.setString(2, "India"); // Make this transaction to fail deliberately connection.rollback(countrySavepoint); connection.commit(); } } As seen from the above example, the program checks whether save points are supported by the target database by querying methods available in DatabaseMetadata. Note for save points are created by calling the method setSavePoint() defined on the Connection object. A save point can be given a name so that at a later point of time, a transaction can be made to commit or rollback based on the name. In the example code, to illustrate the usage of save point, we deliberately rollback the operation related to customer table, whereas, the changes made to country table are committed.

Parameter Metadata
Construction of queries for statements such as prepared statements might involve specifying dynamic values through ‘?’. Previously, there was no support for the tools or for the applications to identify the parameter information embedded in the queries. Now, support has been added to retrieve the parameter information that is passed to prepared statements. Please refer the below code that illustrates the usage: package net.javabeat.jdbc3.newfeatures.pmd; import java.sql.Connection; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import net.javabeat.jdbc3.newfeatures.CommonUtils; public class PMDTest { public static void main(String[] args) throws Exception{ Connection connection = CommonUtils.getConnection(); PreparedStatement pStatement = connection.prepareStatement( "INSERT INTO CUSTOMER VALUES (?, ?)"); pStatement.setInt(1, 1); pStatement.setString(2, "New Customer"); ParameterMetaData parameterMetadata = pStatement.getParameterMetaData(); int parameterCount = parameterMetadata.getParameterCount(); for (int index = 1; index <= parameterCount; index ++){ String parameterClassName = parameterMetadata.getParameterClassName(index); int parameterMode = parameterMetadata.getParameterMode(index); String paramterTypeName = parameterMetadata.getParameterTypeName(index); System.out.println(parameterClassName + "/" + parameterMode + "/" + "/" + paramterTypeName); } } } Auto Generated Keys
Execution of queries sometimes results in the automatic generation of fields or values for tables. The behavior is entirely dependent on the underlying database implementation. Previously, there was no standard mechanism for retrieving such information once the query is executed. Application developers are forced to use non-standard mechanisms for fetching information that happens outside the scope of query execution. For example, consider a table column containing a particular column, say ‘name’, and an equivalent upper column, let’s say ‘upper_name’. The value for the ‘upper_name’ will be automatically populated as soon as the value is populated for the ‘name’ column. This is one of the examples of auto generation feature and the newer specification supports this feature. Refer the code sample below: package net.javabeat.jdbc3.newfeatures.autogenkey; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.Statement; import net.javabeat.jdbc3.newfeatures.CommonUtils; public class AutoGeneratedKeyTest { public static void main(String[] args) throws Exception{ Connection connection = CommonUtils.getConnection(); DatabaseMetaData databaseMetadata = connection.getMetaData(); if (databaseMetadata.supportsGetGeneratedKeys()){ processAutoGeneratedKeys(connection); } } private static void processAutoGeneratedKeys(Connection connection) throws Exception{ System.out.println("Auto generated keys can be retrived"); Statement statement = connection.createStatement(); statement.execute("INSERT INTO ADDRESSINFORMATION (ADDRESSDATA ) " + "VALUES ('Address Text')", Statement.RETURN_GENERATED_KEYS); ResultSet resultSet = statement.getGeneratedKeys(); java.sql.ResultSetMetaData rsMetadata = resultSet.getMetaData(); for (int index = 1; index <= rsMetadata.getColumnCount(); index ++){ while (resultSet.next()){ String columnLabel = rsMetadata.getColumnName(index); System.out.println(resultSet.getObject(columnLabel)); } } } } Note that, while executing the statement, the application has to provide indication to the underlying engine that, if any auto generation columns are applicable once the query is executed, those information have to be made available in the equivalent statement object. This is done through the method Statement.execute() where the second parameter specifies this option. Next, the method getGeneratedKeys() is added to the Statement object which returns a ResultSet containing the desired values. Because, this feature may or may not be supported by the database engine, the API supportsGeteGeneratedKeys() can be used to check the feature availability.

Updating clob/blob objects
The support to update clob objects is directly available on the Clob/Blob objects. The method updateClob() is added to the ResultSet object. Previously, there is no standard way to update large data objects. Note to ensure that this works and because the operation is defined on the ResultSet, the underlying ResultSet object should support updating the record. This is possible if the ResultSet is a flavor of CONCUR_UPDATABLE. Please refer the below code package net.javabeat.jdbc3.newfeatures.clblupdate; import java.io.StringReader; import java.sql.Clob; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import net.javabeat.jdbc3.newfeatures.CommonUtils; public class ClobTest { public static void main(String[] args) throws Exception{ Connection connection = CommonUtils.getConnection(); int key = 1; insertClob(connection, key); readAndUpdateClob(connection, key); } private static void insertClob(Connection connection, int key) throws Exception{ String sql = "INSERT INTO CLOBTEST VALUES (?, ?)"; PreparedStatement pStatement = connection.prepareStatement(sql); pStatement.setInt(1, key); StringReader reader = new StringReader("som big data"); pStatement.setClob(2, reader); pStatement.execute(); } private static void readAndUpdateClob(Connection connection, int key) throws Exception{ String sql = "SELECT DATA FROM CLOBTEST WHERE ID = ?"; PreparedStatement pStatement = connection.prepareStatement( sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pStatement.setInt(1, key); ResultSet resultSet = pStatement.executeQuery(); if (resultSet.next()){ Clob clobObject = resultSet.getClob(1); clobObject.setString(1, "new big data"); resultSet.updateClob(1, clobObject); } } } Conclusion
The new features in JDBC 3.0 have addressed most of the common problems that tools or developer community is encountering, thereby providing a unified way of solution. There are other minor updates done to JDBC 3.0, such as ‘configuring connection pools’, ‘adding of new data types as Boolean, Datalink, URL’ etc.