昨天读了一篇关于JDBC4.0设计与性能提高的文章，由于小弟英语翻译水准实在有限，就不在这里献丑了，就把原文给大家转载出来供大家阅读:

转载自：http://www.javaworld.com/javaworld/jw-05-2006/jw- 0501-jdbc .html

Design and performance improvements with JDBC 4.0

Effectively utilize JDBC'S features to get desired results with less code

Summary
Java Database Connectivity (JDBC) 4.0 is ready for release by mid 2006 as a part of Java Standard Edition 6.0. How can you leverage the new specification to improve the design and performance of database access and interactions in Java applications? This article discusses the new features of JDBC 4.0, illustrates its solutions to some existing problems, and presents its improvements in design and performance through examples. (2,100 words; May 1, 2006)

By Shashank Tiwari

ava Database Connectivity (JDBC), which has existed from the first public version of the core Java language, has evolved significantly over the last 10 years. In its current version, 4.0, which will be packaged with Java Standard Edition 6.0 (Java SE is Sun's new name for J2SE), it shows significant improvements in design and provides a richer API, with focus on ease of development and improvement in productivity.

This article discusses some of the important changes in the JDBC specification that either improve the design or facilitate better performance. The article does not enlist or survey every single change incorporated as a part of Java Specification Request 221, the JDBC 4.0 initiative.

After reading this article, you should be ready to leverage the new features in your next set of applications.

Annotations and the generic DataSet
I assume you are already aware of annotations and generics, which were introduced in Java with J2SE 5.0. JDBC 4.0 introduces annotations and the generic DataSet. This change aims to simplify execution of SQL queries (in scenarios that return a single result set) and SQL DML (data manipulation language) statements (that return either a row count or nothing).

The new API defines a set of Query and DataSet interfaces. The Query interface defines a set of methods decorated with the JDBC annotations. These decorated methods describe the SQL select and update statements, and specify how the result set should be bound to a DataSet. The DataSet interface is a parameterized type, as defined by generics. The DataSet interface provides a type-safe definition for the result set data.

All Query interfaces inherit from the BaseQuery interface. A concrete implementation of the interface can be instantiated using either the Connection.createQueryObject() or DataSource.createQueryObject() methods and passing a Query interface type as its parameter.

A DataSet interface inherits from java.util.List. A data class describing the columns of the result set data, returned by an annotated method of the Query interface, is its parameter type. A DataSet can be manipulated and operated upon both in a connected and disconnected mode. Thus, the DataSet is implemented either as a ResultSet or a CachedRowSet, depending on its operating mode: connected or disconnected. DataSet, being a sub-interface of the java.util.List, allows access of its data rows with the Iterator pattern, using the java.util.Iterator interface.

The data class or the user-defined class, which is a parameter type of the DataSet interface, can be specified in two ways: as a structure or as a JavaBeans object. Either method achieves the goal of binding result set data columns to user-defined class definitions, but the JavaBeans component model is more elegant and facilitates object definition reuse within other frameworks that support the JavaBeans model.

Listing 1 illustrates code snippets for a simple example to show how the new API is used to create and run SQL queries, define result set data using a user-defined class, and bind the returned result set to the user-defined specifications.

Listing 1. Employee user-defined type and employeeQueries

pubic class Employee {
   private int employeeId;
   private String firstName;
   private String lastName;

   public int getEmployeeId() {
      return employeeId;
   }
  
   public setEmployeeId(int employeeId) {
      this.employeeId = employeeId;
   }

   public String getFirstName() {
      return firstName;
   }

   public setFirstName(String firstName) {
      this.firstName = firstName;
   }

   pubic String lastName() {
      return lastName;
   }

   public setLastName(String lastName) {
      this.lastName = lastName;
   }
}


interface EmployeeQueries extends BaseQuery {
   @Select (sql="SELECT employeeId, firstName, lastName FROM employee")
   DataSet<Employee> getAllEmployees ();

   @Update (sql="delete from employee")
   int deleteAllEmployees ();
}


Connection con = ...

EmployeeQueries empQueries = con.createQueryObject (EmployeeQueries.class);

DataSet<Employee> empData = empQueries.getAllEmployees ();


Exception-handling enhancements
The exception-handling functionality in the JDBC API prior to version 4.0 is limited and often insufficient. SQLException is thrown for all types of errors. There is no classification of exceptions, and no hierarchy defines them. The only way to get some meaningful information is to retrieve and analyze the SQLState value. SQLState values and their corresponding meanings change from datasource to datasource; hence, getting to the root of the problem and efficiently handling exceptions proves to be a tedious task.

JDBC 4.0 has enhanced exception-handling capability and alleviates some of the mentioned problems. The key changes are as follows:

• Classification of SQLException into transient and non-transient types
• Support for chained exceptions
• Implementation of the Iterable interface

The SQLTransientException is thrown where a previously failed operation may succeed on retrial. The SQLNonTransientException is thrown where retrial will not lead to a successful operation unless the cause of the SQLException is corrected.

Figure 1 illustrates the subclasses of SQLTransientException and SQLNonTransientException.

Figure 1. SQL exception classes: Transient and non-transient

Support for chained exceptions are now included. New constructors add extra parameters to capture possible causes for the exception. Multiple SQLExceptions could be iterated over in a loop, and getCause() could be called to determine the exception's possible cause. The getCause() method can return non-SQLExceptions if they are the underlying cause of the exceptions.

The SQLException class now implements the Iterable interface and supports the J2SE 5.0 for each loop for easier and more elegant looping.

Listing 2 depicts the usage of the new for-each-loop construct:

Listing 2. For each loop

catch(SQLException ex) {
   for(Throwable t : ex) {
      System.out.println("exception:" + t);
   }
}


SQL/XML
A large amount of data now exists in the XML format. Databases have extended support for the XML data type by defining a standard XML type in the SQL 2003 specification. Most database vendors have an implementation of the XML data type in their new releases. With the inclusion of such a type, an XML dataset or document could be one of the fields or column values in a row of a database table. Prior to JDBC 4.0, perhaps the best way to manipulate such data within the JDBC framework is to use proprietary extensions from the driver vendors or access it as a CLOB type.

JDBC 4.0 now defines SQLXML as the Java data type that maps the database SQL XML type. The API supports processing of an XML type as a string or as a StAX stream. Streaming API for XML, which for Java has been adopted via JSR 173, is based on the Iterator pattern, as opposed to the Simple API for XML Processing (SAX), which is based on the Observer pattern.

Invoking the Connection object's createSQLXML() method can create a SQLXML object. This is an empty object, so the data can be attached to it by either using the setString() method or by associating an XML stream using the createXMLStreamWriter() method with the object. Similarly, XML data can be retrieved from a SQLXML object using getString() or associating an XML stream using createXMLStreamReader() with the object.

The ResultSet, the PreparedStatement, and the CallableStatement interfaces have getSQLXML() methods for retrieving a SQLXML data type. PreparedStatement and CallableStatement also have setSQLXML() methods to add SQLXML objects as parameters.

The SQLXML resources can be released by calling their free() methods, which might prove pertinent where the objects are valid in long-running transactions. DatabaseMetaData's getTypeInfo() method can be called on a datasource to check if the database supports the SQLXML data type, since this method returns all the data types it supports.

Connections and Statements
The Connection interface definitions have been enhanced to analyze connection state and usage to facilitate efficiency.

Sometimes database connections are unusable though they may not necessarily be closed and garbage collected. In such situations, the database appears slow and unresponsive. In most of these circumstances, reinitializing the connections is perhaps the only way to resolve the problem. When using the JDBC API prior to version 4.0, there is no way to distinguish between a stale connection and a closed connection. The new API adds an isValid() method to the Connection interface to query if the connection is still valid.

Also, database connections are often shared among clients, and sometimes some clients tend to use more resources than others, which can lead to starvation-like situations. The Connection interface defines a setClientInfo() method to define client-specific properties, which could be utilized to analyze and monitor resource utilization by the clients.

RowId
The RowId in many databases is a unique way to identify a row in a table. Queries using RowId in the search criteria are often the fastest way to retrieve data, especially true in the case of the Oracle and DB2 databases. Since java.sql.RowId is now a built-in type in Java, you could utilize the performance benefits associated with its usage. RowIds are most useful in identifying unique and specific rows when duplicate data exists and some rows are identical. However, it is important to understand that RowIds often are unique only for a table and not for the entire database; they may change and are not supported by all databases. RowIds are typically not portable across datasources and thus should be used with caution when working with multiple datasources.

A RowId is valid for the lifetime defined by the datasource and as long as the row is not deleted. The DatabaseMetadata.getRowIdLifetime() method is called to determine the RowId's lifetime. The return type is an enumeration type as summarized in the table below.

RowIdLifetime enum type	Definition
ROWID_UNSUPPORTED	Datasource does not support RowId type
ROWID_VALID_OTHER	Implementation-dependent lifetime
ROWID_VALID_TRANSACTION	Lifetime is at least the containing transaction
ROWID_VALID_SESSION	Lifetime is at least the containing session
ROWID_VALID_FOREVER	Unlimited lifetime

The ROWID_VALID_TRANSACTION, ROWID_VALID_SESSION, and ROWID_VALID_FOREVER definitions are true as long as the row is not deleted. It is important to understand that a new RowId is assigned if a row is deleted and reinserted, which sometimes could happen transparently at the datasource. As an example, in Oracle, if the "enable row movement" clause is set on a partitioned table and an update of the partition key causes the row to move from one partition to another, the RowId will change. Even without the "enable row movement" flag with the "alter table table_name" move, the RowId could change.

Both the ResultSet and CallableStatement interfaces have been updated to include a method called getRowID(), which returns a javax.sql.RowId type.

Listing 3 shows how RowId could be retrieved from a ResultSet and from a CallableStatement.

Listing 3. Get RowId

//The method signatures to retrieve RowId from a ResultSet is as follows:
   RowId getRowId (int columnIndex)
   RowId getRowId (String columnName)
...

Statement stmt = con.createStatement ();

ResultSet rs = stmt. ExecuteQuery (…);

while (rs.next ()) {

...

java.sql.RowId rid = rs.getRowId (1);

...

}

//The method signatures to retrieve RowId from a CallableStatement is as follows:
   RowId getRowId (int parameterIndex)
   RowId getRowId (String parameterName)

Connection con;
...

CallableStatement cstmt = con.prepareCall (…);
...

cstmt.registerOutParameter (2, Types.ROWID);

...

cstmt.executeUpdate ();

...

java.sql.RowId rid = cstmt.getRowId (2);


The RowId can be used to refer uniquely to a row and thus can be used to retrieve the rows or update the row data. When fetching or updating using RowId references, it is important to know the validity of the RowId's lifetime to assure consistent results. It is also advisable to simultaneously use another reference, such as the primary key, to avoid inconsistent results in circumstances where the RowId could change transparently.

The RowId values can also be set or updated. In the case of an updateable ResultSet, the updateRowId() method could be used to update the RowId for a particular row in a table.

Both the PreparedStatement and the CallableStatement interfaces support a setRowId() method, with different signatures, to set the RowId as a parameter value. This value could be used to refer to data rows or to update the RowId value for a particular row in a table.

The facility to set or update the RowId provides the flexibility to control the unique row identifiers and could be used to make such identifiers unique across the tables used. Perhaps, portability of RowId across supporting datasources could also be achieved by explicitly setting consistent values across them. However, because system-generated RowIds are often efficient, and transparent tasks could alter RowIds, they are best used by an application as a read-only attribute.

Leverage nonstandard vendor implemented resources
The new JDBC API defines a java.sql.Wrapper interface. This interface provides the ability to access datasource-vendor-specific resources by retrieving the delegate instance using the corresponding wrapped proxy instance.

This Wrapper interface has 17 sub-interfaces as per the current specification and includes Connection, ResultSet, Statement, CallableStatement, PreparedStatement, DataSource, DatabaseMetaData, and ResultSetMetaData, among others in the list. This is an excellent design as it facilitates datasource-vedor-specific resource implementation at almost all stages of the query-creation and result-set-retrieval lifecycles.

The unwrap() method returns the object that implements the given interface to allow access to vendor-specific methods. The isWrapperFor() method returns a Boolean value. It returns true if it implements the interface, or if it directly or indirectly is a wrapper for the object.

As an example, when using Oracle, Oracle JDBC drivers provide update batching extensions that are better performing and more efficient as compared to the standard JDBC batch-updating mechanisms. For earlier JDBC versions, this implies using the Oracle-specific definitions, such as OraclePreparedStatement, in the code. This compromises code portability. With the new API, many such efficient implementations can be wrapped and exposed within the standard JDBC definitions.

Service provider mechanism for driver loading
In a nonmanaged or standalone program scenario, prior to JDBC 4.0, you would have to load the JDBC driver class explicitly by invoking the Class.forName method, as shown in Listing 4:

Listing 4. Class.forName

Class.forName ("com.driverprovider.jdbc.jdbcDriverImpl");

With JDBC 4.0, if the JDBC driver vendors package their drivers as services, defined under the server provider mechanism definitions as per the JAR specification, the DriverManager code would implicitly load the driver by searching for it in the classpath. The benefit of this mechanism is that the developer does not need to know about the specific driver class and can write a little less code when using JDBC. Also, since the driver class name is no longer in the code, a name change would not require recompilations. If multiple drivers are specified in the classpath, then DriverManger will try and establish a connection using the first driver it encounters in the classpath and iterate further if required.

Conclusion
In this article, I have discussed some of the new and improved features of JDBC 4.0. Many of these new features enhance a developer's productivity and facilitate development. Also, the specification does not eradicate the possible use of extra JDBC frameworks to provide templating facilities and advanced exception-handling capabilities. However, there is some criticism as well. Some believe that annotations effectively lead to hard-coding in code, which causes problems in code maintainability.