Design and Performance improvements in JDBC🤓

Jdbc New Features

The latest version of JDBC is 4.0 currently. Java has updated jdbc api to ease and simplify the coding to database interactivity.

Here, we are going to see the features included in Jdbc 3.0 and Jdbc 4.0.

---

Jdbc 3.0 Features

The important features of JDBC API 3.0 are as follows:

• Jdbc RowSet We have done the great discussion on JdbcRowSet in the previous page.
• Savepoint in transaction management Now you are able to create, rollback and release the savepoint by Connection.setSavepoint(), Connection.rollback(Savepoint svpt) and Connection.releaseSavepoint(Savepoint svpt) methods.
• Statement and ResultSet Caching for Connection Pooling Now you are able to reuse the statement and result set because jdbc 3 provides you the facility of statement caching and result set caching.
• Switching between Global and Local Transactions
• Retrieval of auto generated keysNow you are able to get the auto generated keys by the method getGeneratedKeys().

Jdbc 4.0 Features

The important features of JDBC API 4.0 are given below:

• Automatic Loading of Driver classYou don't need to write Class.forName() now because it is loaded bydefault since jdbc4.
• Subclasses of SQLException Jdbc 4 provides new subclasses of SQLException class for better readability and handling.
• New methods There are many new methods introduced in Connection, PreparedStatement, CallableStatement, ResultSet etc.
• Improved DataSource Now data source implementation is improved.
• Event Handling support in Statement for Connection PoolingNow Connection Pooling can listen statement error and statement closing events.

Among many new features and enhancements in Java SE 6 is the Java Database Connectivity (JDBC) API library, which is updated to JDBC 4.0 in this version of Java SE. JDBC 4.0, specified as JSR-221 under the Java Community Process, provides better code manageability and flexibility, and more complex data type support. In this article I will explore some new features and enhancements available in JDBC 4.0 from the perspective of programming ease and flexibility.

New Features in JDBC 4.0

The JDBC 4.0 specification lists some twenty new features or enhancements, some of which are major, others minor. Because it is not possible to discuss all of them in this article, I've attempted to group them in following categories based on functionality they provide or the area(s) the changes fall into.

1. Driver and connection management
2. Exception handling
3. Data type support
4. API changes

For the remainder of this article, I will be explaining these categories in more details.

Driver and Connection Management

The use of drivers--connection and result set management--sees a number of new features and enhancements in different JDBC aspects. The following three aspects are the most significant.

Getting Connected Becomes Easier

If you have been programming JDBC for some time, then I'm sure you still have the typical laundry list of tasks to follow for establishing a connection to the target database. The first in the list is to load a suitable driver. Did you think this process should have been improved, or ever would be? They did exactly that in this version of JDBC. You don't have to explicitly load the driver by calling Class.forName anymore. When your application attempts to connect the database for the first time,DriverManager automatically loads the driver found in the application CLASSPATH. This is one of the great additions in this version of JDBC.
Although DriverManager now automatically loads the driver, creating an appropriate DataSource object is still the preferred way to retrieve a connection. It allows transparency and portability because the properties of the data source instance can be modified to retrieve a connection to a different database. You don't need to change any application code to connect to a different database instance, or a completely different type of database that requires loading a different driver.

UsingResultSet Becomes More Flexible

The hierarchy of the ResultSet interfaces now offers several important features for programming flexibility. TheRowSet sub-interface provides a scrollable, updatable, and offline-editable ResultSet. TheWebRowSet sub-interface provides the ability to read data from database tables, serialize them to an XML document, and deserialize from XML back to result set. I've discussed these features in more detail in the OnJava article " Making the Most of JDBC with WebRowSet." Although theRowSet interface hierarchy was introduced in previous version of JDBC, the current version's support for the SQLXML data type (discussed later) along with these features makes JDBC programming easier and more flexible.

More APIs Become Available

More APIs have been added to this version of JDBC to provide access to commonly implemented features of SQL:2003. In addition, several new methods have been added to commonly used interfaces to support easier and better data manipulation.
Now, let's review some working code and discuss the result of the Example1 class below. It will connect to an embedded Apache Derby database and display the query results in the console. Although JDBC 4.0 has been out for several months now, I found Apache Derby is the only database to provide a driver that even partially supports the JDBC 4.0 specification at the time of this writing (March 2007). All examples in this article are developed using JDK 1.6and Apache Derby database version 10.2.2.0. For all code examples, I will show only snippets of the actual Java source file that are relevant to the discussion. See the Resources section for complete sample code discussed in this article.

 
public class Example1 { public static void main(String[] args) { ... String dbName = "example1"; String tableName = "stu1"; ds = new EmbeddedDataSource40(); ds.setDatabaseName(dbName); String connectionURL = "jdbc:derby:"+dbName+";create=true"; try { con = ds.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery("select * from "+tableName); int colCount= rs.getMetaData().getColumnCount(); for (int j=0; j< colCount; j++){ System.out.print(rs.getMetaData().getColumnName(j+1) + "\t"); } while (rs.next()) { System.out.print("\n"); for (int i = 0; i < colCount; i++) { System.out.print(rs.getString(i + 1) + "\t"); } } } catch (SQLException e) { e.printStackTrace(); } finally{ //close connections } } } 

If you have some data in the stu1 table of theexample1 database, then compiling and runningExample1.java should print contents in the console. Here is what I got.

 
ID NAME COURSE 1001 John Doe Statistics 1002 Jack McDonalds Linear Algebra 

If you want to see the DriverManager automatically load the JDBC driver, then you can replace thecon=ds.getConnection() call in Example1with con=DriverManager.getConnection(connectionURL). The class should produce exactly the same result. As you can see from this example, you no longer have to load the driver with an explicit Class.forName() call.

Exception Handling

How do you differentiate a robust Java program from those that aren't? In my view, exception handling is one of the most important factors. A robust Java program handles exceptions very well, giving the program an ability to recover itself from such conditions. On the other hand, poor exception handling may result in a program producing the wrong result or it may ultimately break the application!
JDBC 4.0 adds some simple but powerful features to handle exceptions, including support for chained exception and use of the enhanced for-each loop to retrieve such chained exceptions, if any. Example2 below shows part of a Java class that handles chained exceptions in this new way.

 
public class Example2 { public static void main(String[] args) { String dbName = "example"; String tableName = "student4"; try { con = ds.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery("select * from " + tableName); } catch (SQLException sx) { for(Throwable e : sx ) { System.err.println("Error encountered: " + e); } } finally{ //close connections } } } 

I ran the class in Example2.java, specifyingstudent4 as a table name that does not exist in the database. It raised a chained exception in following call.

 
rs = stmt.executeQuery("select * from " + tableName); 

In a real application, you need to catch such chained exceptions, inspect them, and take a suitable action. In this example, however, I will show how to print them to the error console. Here is how to do just that.

 
for(Throwable e : sx ) { System.err.println("Error encountered: " + e); } 

Here's the output running the class Example2:

 
Error encountered: java.sql.SQLSyntaxErrorException: Table/View 'STUDENT4' does not exist. Error encountered: java.sql.SQLException: Table/View 'STUDENT4' does not exist. Exception in thread "main" java.lang.NullPointerException at ex.Examlpe2.main(Examlpe2.java:51) 

With JDBC 4.0, you now can retrieve and iterate over chained exceptions with a small amount of code. In previous versions, you had to retrieve individual chained exceptions iteratively by calling getNextException in the caught exception.

Data Type Support

This version of JDBC adds some new data types and enhances support for others. I like the fact that the need for XML support has rightly been identified, and a new interface,SQLXML, has been added. In my view this interface deserves a separate section for discussion.

SQLXML and XML Support

SQLXML is a mapping in the Java programming language for theXML data type in SQL. XML is a built-in type that stores an XML value as a column value in a row of the target table. By default, drivers implement an SQLXMLobject as a logical pointer to the XML data rather than the data itself. An SQLXML object is valid for the duration of the transaction in which it was created.
In the Example3 class below, I show how to create an SQLXML object from the current connection and update the underlying table value.

 
public class Example3 { public static void main(String[] args) { ... con = ds.getConnection(); SQLXML sx= con.createSQLXML(); sx.setString("Math is Fun"); String psx ="insert into "+tableName+ " ( id, textbook) values(?,?) "; PreparedStatement pstmt = con.prepareStatement(psx); pstmt.setString(1,"1000"); pstmt.setSQLXML(2,sx); pstmt.executeUpdate(); ... } } 

This example shows just a very basic thing you can do. Things will get interesting if we can explore further. But before going too far, let me show what happened when I ran theExample3.java. Unfortunately, I was unable to retrieve theSQLXML object and the program exited with following disappointing message:

 
java.sql.SQLFeatureNotSupportedException: Feature not implemented: no details. at org.apache.derby.impl.jdbc.SQLExceptionFactory40. getSQLException(Unknown Source) ... ... ... ... at ex.Example3.main(Example3.java:62) 

It turns out that this version of Apache Derby does not provide support for creating an SQLXML object from aConnection. But you should still be able to see what I'm trying to do in Example3: I wanted to insert a new row with an id column value of 1000 and atextbook column (which is SQLXML type) value of Math is Fun in the target table.
I want to conclude this SQLXML section by showing the following code snippet that would read an XML value from a table and parse it into a Document object.

 
SQLXML sqlxml = rs.getSQLXML(column); InputStream binaryStream = sqlxml.getBinaryStream(); DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder(); Document doc = parser.parse(binaryStream); 

Isn't it nice to be able to read a column value from underlying table and build a XML document directly? I think it is a very nice feature.

The ROWID Data Type

SQL ROWID identifies a row within a table and is the fastest way to access it. A new RowId interface has been added in this version to provide access to theROWID SQL type from a Java class.

Enhanced Support for Large Object Types

Version 2.0 of JDBC provided support for large SQL objects such as CLOB, BLOB, ARRAY, andSTRUCT by adding corresponding interfaces:Clob, Blob, Array, andStruct. More methods are now added in this version of JDBC to support manipulation of such objects.

Support for National Character Set Conversion

SQL:2003 provides support for SQL data types ofNCHAR, NVARCHAR,LONGNVARCHAR, and NCLOB. They are analogous to CHAR, VARCHAR,LONGVARCHAR, and CLOB except for the fact that the values are encoded using different character sets, namely the national character set (NCS). You may want to choose the NCS data types over the regular character data types if your data need extensive character processing operations. This version of JDBC adds or enhances NCS support in the following APIs.

• Several setter and update methods have been added to thePreparedStatement, CallableStatement, andResultSet interfaces to support NCS conversion. Methods such as setNString,setNCharacterStream, and setNClob are a few examples.
• Read and write methods have been added to theSQLInput and SQLOutput interfaces to support NClob and NString objects.

API Changes

Most of the enhancements in JDBC 4.0 have been at the API level, which I will briefly discuss in this section. For more details, please refer to the JSR 221:JDBC 4.0 API specification.

Array

A new free method has been added to theArray interface to free the array object and release the resources that it holds.

Connectionand PooledConnection

Connection interface now provides a bunch of methods to support the creation of large objects such ascreateClob, createBlob, etc. Other additions are overloaded versions of getter and setter methods for the client info, and methods to check the validity of the current connection.
The PooledConnection interface now providesaddStatementEventListener andremoveStatementEventListener methods that come in handy if you need to register or deregister theStatementEventListener interface, which is introduced in this version of JDBC. An instance of this interface registers to be notified of events occurring on PreparedStatements that are in the Statement pool. For example, if registered, the driver calls the statementClosedmethod on all StatementEventListeners when it detects that a PreparedStatement is closed on the statement.

DatabaseMetaData

Different relational DBMSs often support different features, implement features in different ways, and may use different data types. This may cause portability issues because a portion of code that works for one database may fail on another, based on the underlying implementation. Such issues may be addressed to some extent using information returned by calling methods in this interface. For example, suppose you are writing some code that will pass a SQL statement to create a table. You may want to find out what data types can be used in a CREATE TABLEstatement by calling the getTypeInfo method in this interface.
This version of JDBC adds several methods to support querying database metadata info. In Example4, I show a code snippet to demonstrate a new feature that allows retrieving a list of database schemas that satisfies a typical search pattern.

 
con = ds.getConnection(); DatabaseMetaData dmd = con.getMetaData(); rs=dmd.getSchemas("TABLE_CAT", "SYS%"); //iterate over the rs and print to console 

First I called dmd.getCatalogs and iterated over the result set, which gave me only the catalog value asTABLE_CAT. I then used this value to ask what schemas in the database have names that start with SYS by calling rs=dmd.getSchemas("TABLE_CAT", "SYS%"). Here is the list of table schema I got:

 
SYS SYSCAT SYSCS_DIAG SYSCS_UTIL SYSFUN SYSIBM SYSPROC SYSSTAT 

Scalar Function Support

A scalar function operates on a predefined set of input values and returns some result. For example, the result of the scalar function call ABS(number) returns the absolute value of the number. These scalar functions can be used as part of SQL strings from the Java code. This version of JDBC requires that the driver should support them if the underlying database supports the following new scalar functions:CHAR_LENGTH, CHARACTER_LENGTH,CURRENT_DATE, CURRENT_TIME,CURRENT_TIMESTAMP, EXTRACT,OCTET_LENGTH, and POSITION.

Statement, PreparedStatement, andCallableStatement

The Statement interface now provides anisClosed method to query if the statement is closed,setPoolable to set if you want this statement to be poolable or non-poolable, and isPoolable to check if the current statement is poolable.
The PreparedStatement andCallableStatement interfaces now provide more methods for inserting large objects, using InputStream andReader respectively.

Wrapper

This version of the API adds a new Wrapperinterface that provides a mechanism for accessing an instance of a resource, which may have been wrapped for architectural reasons. The Wrapper pattern, a.k.a. the Adapter pattern, is employed by many JDBC driver implementations to provide extensions beyond the traditional JDBC API that are specific to a data source. The main intention of this interface is to eliminate non-standard means to access vendor-specific resources. You can find out an object that implements the given interface by calling the unwrap method in the instance of this interface. Because the unwrap method call is a heavyweight operation, you should use the isWrapperFormethod of this interface to check if the class that implements this interface is either a direct or indirect wrapper for an object before actually unwrapping it.
It would have been really nice to wrap up with another example on how to unwrap the JDBC adapter but the "JDBC 4.0-only features" section of the Apache Derby reference manual mentions following about the Wrapper: "JDBC 4.0 introduces the concept of wrapped JDBC objects ... For Derby, this is a vacuous exercise because Derby does not expose any of these extensions." There is no point on trying to do a vacuous exercise!

Conclusion

I've discussed some new features and enhancements in JDBC 4.0 in areas like driver and connection management, exception handling, support for additional data types (including XML), and API changes providing more flexibility for data manipulation. These new features are meant to provide ease of use and programmer productivity. Although the API specification has been out for several months now, most commercial database vendors have yet to provide drivers that support this version of JDBC at the time of writing. When more vendors start supporting for JDBC 4.0--and of course that should include the vendor of your preferred database--I'm sure you will enjoy all these great new features that provide lot of flexibility and ease of programming to you as a programmer.
Finally, I feel that it will be nice have a single web page that lists the database vendors and the most recent version of JDBC they support. There is a searchable JDBC Data Access API page on the Sun Developer Network (SDN) that attempts to provide this, but the information available there does not seem to be up to date.

Resources

• Sample codefor this article
• JSR 221: JDBC 4.0 API specification
• JSR 270: JavaTM SE 6 release contents
• Apache Derby database