Related Topics: Java EE Journal

J2EE Journal: Article

JDBC 4.0: A Significant Advance on the Standard and Features Worth the Wait

A significant advance on the standard

SQL 2003 also includes extensions to the SELECT syntax that lets you construct XML results from tabular columns. The following code shows how to create a SELECT statement that produces a result set containing two columns: a CustId result column of type integer and a CustInfo column of type SQLXML.

The SELECT statement uses the new SQL/XML extension XMLELEMENT to process multiple base columns into a single XML result column. JDBC 4.0 has also been expanded to support using database metadata methods to determine which SQL/XML constructs are supported on the connection. Applications can then execute any supported SELECT statement with SQL/XML extensions to produce SQLXML result columns that can be processed using the new XML Java bindings.

Connection and Statement Pooling Enhancements
Most deployed JDBC applications use connection pooling, statement pooling, or a combination to obtain better application performance. Pooling is great - except it's not very tunable, it's hard to map end users back to connections in the pool, and if a connection ever becomes invalid inside the pool, expunging only that connection from the pool is nearly impossible. JDBC 4.0 addresses all these drawbacks.

Currently, prepared statement pooling is very atomic - either statement pooling is on or it's off. This kind of operation doesn't fit the programming model many applications use. In common deployments, it's likely that an application will have a certain set of SQL statements that are re-executed multiple times and a few SQL statements that might only be executed once or twice during the life of the application. Unfortunately, existing statement pooling implementations give no weight to a SQL statement executed 100 times versus one that's executed only twice. Again, either a statement goes into the pool, potentially causing another statement to be removed from the pool, or there's no pool. JDBC 4.0 provides a more granular level of statement pooling by letting applications hint to the pool manager about whether a SQL statement should be pooled.

The preparedStatement interface has been expanded by the addition of two new methods: isPoolable() and setPoolable(). The isPoolable() method returns a Boolean flag that denotes whether the SQL statement identified on the preparedStatement object should be pooled (by default, a statement is poolable when it's created). Applications can specifically request that a statement not be pooled by calling setPoolable(false). Using these constructs, application designers gain more control over the performance aspects of their applications. Queries that are reused are pooled and provide optimal performance, and queries that are used infrequently don't affect the pool.

Connection pooling is a mature feature available in all J2EE application servers and is used in many standalone Java applications. One might think that a technology that's been available for so long would have all the kinks worked out. JDBC 4.0 addresses some major concerns of connection pooling and discussions are already underway on connection pooling enhancements for the JDBC specification post-4.0.

Today, when the response time of your database queries is ridiculously slow because your application server is out of CPU cycles, your database appears to be "hung," or you try to monitor the status of your applications only to see that "some JDBC connection" is using all the CPU, the facilities available to help you find the culprit aren't very good. Once a JDBC connection is established, the tracking mechanism between that physical connection and an application's use of the logical connection is lost. The connection pool manager assigns physical connections in the pool to any application that meets authentication requirements; the pool manager doesn't keep any statistics on the application requesting a connection, and the connection itself is a black box to the application. In other words, if you are using a monitoring tool and see that a JDBC connection is "bogging down the system," it's impossible to track down which JDBC application is actually invoking the driver.

To solve this problem, JDBC 4.0 has added setClientInfo() and getClientInfo() to the Connection interface. After connecting, an application can call setClientInfo() to associate client-specific information to the JDBC connection object, such as application name, site name, and department name for the JDBC connection. Monitoring tools can then retrieve this information to help pinpoint where the problem is.

Large-scale deployments often face another problem when a pool is populated with a large number of connections. How does the pool manager detect when a connection has become invalid? Today, there's no facility inside a JDBC driver to check and see if a connection is still valid. The Connection.isClosed() method is sometimes mistakenly thought to do this, but the intent of isClosed() is to check and see if a connection is open or closed, not whether the connection is still usable. If a connection pool manager decides that a connection is invalid or is suspect (through whatever proprietary means is available), the most common technique used is for the pool manager to terminate all the connections in the pool and re-initialize it. This is a very drastic approach to take and is extremely expensive in terms of performance. A new method on the Connection interface, isValid(), has been added so pool managers can specifically request the driver if a connection is still usable. If a connection is invalid, the pool manager can discard only the marred connection and not the contents of the entire pool.

SQLException Improvements
JDBC 4.0 is meant to make it easier for developers to write JDBC applications. There are too many changes to the specification to describe all of the "ease of development" features here; however, one of the features we'll talk about is handling SQLExceptions. Applications can call getSQLState() when a SQLException happens to get the details about the cause of the error. The problem developers face is that there are many different SQLStates that can be returned. Programmatically figuring out what higher-level reason caused the error is straightforward, but time-consuming, error-prone, and monotonous.

JDBC 4.0 expands the java.sql.package's exception hierarchy by providing two distinct subclasses that indicate whether exceptions are transient (and might succeed if retried) or aren't transient (and won't succeed if retried). These subclasses are: SQLNonTransientException and SQLTransientException. SQLNonTransientExceptions are subclassed further into five distinct cases: SQLSyntaxErrorException, SQLInvalidAuthorizationSpecException, SQLIntegrityConstraintViolationException, SQLDataException, and SQLNonTransientConnectionException. SQLTransientExceptions are subclassed into three distinct cases: SQLTimeoutException, SQLTransactionRollbackException, and SQLTransientConnectionException.

The idea behind this change in the specification is that applications might only be concerned with whether this error is "expected" or not. If it's not expected, the operation can just be retried and may well succeed. In this case, there's no checking 30 different SQLStates to see if the statement should be re-executed. An application just checks to see if the SQLException was a SQLTransientException and can then be retried.

If an application needed to determine whether the error was a "programming error," such as an invalid data conversion, it could check the SQLException to see if it was an SQLDataException. Programming a single check is much easier than checking 12 different SQLStates.

Other New Features
We can only touch on a few of the many enhancements for JDBC 4.0 here. It includes support for a new ROWID data type, bindings for the National Character Set, improved management of Clob and Blob objects, an improved mechanism for installing and recognizing JDBC drivers on a system, new annotations and interfaces, and extensive JDBC specification clarifications. Please take a look at the JDBC 4.0 (JSR-221) details on jcp.org and provide feedback that's relevant to your JDBC use.

More Stories By John Goodson

As vice-president of product operations, John Goodson leads the product strategy, direction, and development efforts at DataDirect Technologies. For more than 10 years, he has worked closely with Sun and Microsoft on the development and evolution of database connectivity standards including J2EE, JDBC, .NET, ODBC, and ADO. His active memberships in various standards committees, including the JDBC Expert Group, have helped Goodson's team develop the most
technically advanced data connectivity technologies. He holds a BS in computer science from Virginia Tech.

Comments (2)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.