Leading-Edge Java
Upcoming Features in JDBC 4
by Frank Sommers
September 2, 2005

<< Page 3 of 3

Exceptional exceptions

Another area JDBC 4 addresses is error handling, one of the more complex chores when working with databases. Because databases are often remotely accessible resources, problems such as network failures, or the inability of the database management system itself to process a request, can cause exceptions when executing a database operation. In addition, incorrect SQL statements can cause exceptions.

Prior to JDBC 4, most JDBC operations generated a simple SQLException. While that exception contained some information about what went wrong, few developers bothered to use that information. Instead, when encountering an SQLException, most JDBC code tends to abort the transaction, and indicate the error to the caller. JDBC 4 introduces a finer-grained exception hierarchy via both chained exceptions and by dividing exceptions into transient and non-transient categories.

A transient SQL exception extends SQLTransientException, and indicates that a previously failed operation might be able to succeed if the operation is simply retried. For instance, failure to connect to a database is a transient exception, since retrying the connection may cause it to work a second time around. A non-transient exception extends SQLNonTransientException, and signals that retrying the same operation would fail again unless the root cause of the problem is remedied by the user. For instance, an erroneous SQL expression would be a non-transient exception. Both SQLTransientException and SQLNonTransientException are subclasses of SQLException.

JDBC 4 maps the various transient and non-transient exception types to an SQLState values. SQLState is one of the values in an SQLException and corresponds to error codes defined in the SQL standard. When the SQL standard does not define an error state for an exception condition, the default in JDBC 4 is to return an SQLException.

`SQLTransientException`s
`SQLTransientConnectionException`	Indicate some change in the connection's communication connection layer.	`SQLState` 08
`SQLTimeoutException`	When timeout specified by a `Statement` is expired.	No corresponding SQLState.
`SQLTransactionRollbackException`	Indicate that the current `Statement` was automatically rolled back by the database. This caused by a transaction deadlock or serialization problems.	`SQLState` 40

`SQLNonTransientException`s
`SQLDataException`	Indicates data errors, such as invalid arguments to functions, etc.	`SQLState` 22
`SQLIntegrityConstraintViolationException`	A foreign key, primary key, or unique key constraint was violated.	`SQLState` 23
`SQLInvalidAuthorizationSpecException`	Authorization credentials presented during authorization were not valid.	`SQLState` 28
`SQLNonTransientConnectionException`	A non-transient version of the connection exception, indicating some change in the connection's communications layer.	`SQLState` 08
`SQLSyntaxErrorException`	Indicates that an in-progress query has violated syntax rules.	`SQLState` 42

JDBC 4's exceptions are also chained exceptions, and accessing the root cause of an exception can quickly help determine the source of a problem. To support iteration over an exception chain, SQLException defines a new getNextException() method. Invoking this method returns either the next Exception in the exception chain, or null, when the root of the hierarchy is reached. SQLException also supports the getCause() method, which may return a non-SQLException subtype as well (since the cause of an SQLException may be a non SQL-related problem). The following code example illustrates iterating through an SQLException chain:

...
 } 
catch (SQLException e) {
 
while (ex != null) {
	Throwable t = ex.getCause();
	while (t != null) {
		t = t.getCause();
	}
	ex = ex.getNextException();
}

Since JDBC 4 supports the JDK 1.5 for-each construct, the above code can be written as follows:

} 
catch(SQLException e) {
	for (Throwable ex : e) {
		System.out.println("Exception encountered: ...")
	}
}

Type-safe querying and results

Every developer working with JDBC realizes at some point that the code to translate between the relational world of an SQL database and the object-oriented paradigm of a Java application can quickly start to dominate an enterprise Java project. Prior to version 4, the JDBC API offered little help in bridging those worlds. Instead, the EJB entity persistence model and, more recently, object-relational mapping standards and products, such as JDO and Hibernate, provided developers alternative ways to access relational data stores. While these APIs use JDBC under the covers, they hide JDBC from the developer. Instead, they present database records in terms of an application's domain model.

However, introducing O/R mapping into an application just to make it easier to work with relational databases incurs additional complexity that could be avoided by an improved JDBC API. JDBC 4 brings just such an improvement to the development experience with the introduction of annotations and generic types, both new features in JDK 1.5.

To illustrate JDBC's use of annotations, consider a simple User entity with these properties:

class User {
	int userID;
	String name;
	String department;
}

This class may be persisted in a database table created with the following DDL (data definition language):

create table user (int userid, name varchar(128), department varchar(128));

A common "feature" of many JDBC applications is a static class containing the application's SQL statements. For instance, an application accessing a database of users may have a "template" class containing the following query:

class MyQueries {
	public static final String SELECT_ALL_USERS = "select * from user";
}

While factoring all JDBC query statements to a separate class helps reduce clutter, this solution is not very object-oriented. At the same time, interacting with SQL data stores necessarily involves managing strings that represent SQL statements.

Annotations help bridge the gap between objects and strings. JDBC 4 defines a Query interface and associated annotations that lend the above development style a more object-oriented flavor. Query defines methods that are decorated with JDBC annotations corresponding to SQL queries and update statements.

When you invoke a method in a Query, the SQL statement associated with that Query method will be executed, and the results returned and bound to a data type you specify. Consider an annotations-based version of the above query template:

interface MyQueries extends BaseQuery {

	@Query(sql="select * from user")
	DataSet getAllUsers();
}

This Query implementation is a subinterface of BaseQuery. The getAllUsers() method is bound by the @Query annotation to the SQL query statement. When the method is invoked, the runtime system executes the associated query string with the method, and returns the results as a DataSet.

A DataSet is a subinterface of java.util.List, and provides a type-safe view of the data returned from an SQL query. DataSet is a parameterized type: The parameter type is a class that describes the data returned from the query. DataSet acts a bit like a type-safe ResultSet, but it supports both connected and disconnected access to data.

Once a DataSet is returned from a query, you can iterate through its elements. The bit of magic in turning the Query interface into an actual JDBC query, executing that query, and then binding the resulting data to the object type specified as a parameter to DataSet, is accomplished by a set of runtime classes.

In this example, you could obtain an instance of MyQuery from the Connection class:

Connection c = myDataSource.getConnection();
MyQueries myQueries = c.createQueryObject(MyQuery.class);
DataSet users = myQueries.getAllUsers();
for (User u: users) {
	System.out.println("User's name is: " + user.name;
}

You could also create a parameterized annotation element with Query:

interface MyQueries extends BaseQuery {

	@Query(sql="select * from user where department= {department}")
	DataSet getDepartmentUsers(String department);
}

Parameters in the annotation must be specified with a string value inside {...} braces. The parameter's string value must match, in a case-sensitive way, the method parameter name corresponding to that parameter (department, in this case).

In addition, query annotations can contain not only queries, but also updates or deletes. For instance, to remove a user corresponding to a user name, you might use the following annotation:

interface MyQueries extends BaseQuery {

	@Update(sql="delete from user where name= {userName}")
	int deleteUser(String userName);
}

To update a user's department to a new value, you would use this annotation:

interface MyQueries extends BaseQuery {

	@Update(sql="update user set department={deparment} where name= {userName}")
	int updateDeparment(String userName, String department);
}

DataSet also allows you to modify or delete records in a database. For instance, having obtained a DataSet, you could insert a new user into the database via that DataSet:


Connection c = myDataSource.getConnection();
MyQueries q = c.createQueryObject(MyQueries.class);
DataSet users = q.create();
User user = new User();
user.setUserID(1);
user.setName("Joe");
user.setDeparment("Accounting");
users.insert(user);

While such data binding at first appears similar to object-relational mapping tools, all that takes place here is that database columns are bound to an object representing the query results in a type-safe way. The focus remains on SQL, and JDBC 4 does not attempt to map between complex relational and object hierarchies.

Summary

JDBC 4 is currently in early draft review. If you care about the future of Java database access, this is your chance to download the spec and, if you find something in error, to comment during the public comment period. Such comments might actually be heard and appreciated, especially by the scores of developers relying on this upcoming version of the Java data access standard.

Talk back!

Have an opinion about JDBC 4? Discuss this article in the Articles Forum topic, Upcoming Features in JDBC 4.

Resources

[1] JDBC 4 API Specification, JSR 221
http://www.jcp.org/en/jsr/detail?id=221

[2] Eisenberg, A., K. Kulkarni, J. Melton, J. Michels, and F. Zemke. SQL:2003 Has Been Published In ACM SIGMOD Record, Vol. 33, No. 1, March 2004.
http://www.sigmod.org/sigmod/record/issues/0403/E.JimAndrew-standard.pdf

[3] Eisenberg, A., and J. Melton. SQL/XML is Making Good Progress In ACM SIGMOD Record, Vol. 32, No. 2, June 2002.
http://www.sigmod.org/sigmod/record/issues/0206/standard.pdf

[4] StAX XMLStreamReader JavaDoc
https://stax-utils.dev.java.net/nonav/javadoc/api/javax/xml/stream/XMLStreamReader.html

[5] Streaming API for XML (StAX), JSR 173
http://www.jcp.org/en/jsr/detail?id=173
http://dev2dev.bea.com/xml/stax.html

[See also] JDBC 4 Presentation (JavaOne 2005)
https://jdk.dev.java.net/nonav/J12005/jdbc.pdf

About the author

Frank Sommers is a Senior Editor with Artima Developer. He also serves as chief editor of ClusterComputing.org, the IEEE Technical Committee on Scalable Computing's newsletter, and is an elected member of the Jini Community's Technical Oversight Committee. Frank is also founder and president of Autospaces, a company dedicated to delivering rich-client Java enterprise solutions to small and medium-size businesses. Prior to joining Artima, Frank wrote the Jiniology and Web services columns for JavaWorld.

<< Page 3 of 3

This article is sponsored by the Java Community Process.


	Web Artima.com