ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

JDBC 4.0 Enhancements in Java SE 6
Pages: 1, 2, 3, 4

Annotation-Based SQL Queries

The JDBC 4.0 specification leverages annotations (added in Java SE 5) to allow developers to associate a SQL query with a Java class without writing a lot of code to achieve this association. Also, by using the Generics (JSR 014) and metadata (JSR 175) APIs, we can associate the SQL queries with Java objects specifying query input and output parameters. We can also bind the query results to Java classes to speed the processing of query output. We don't need to write all the code we usually write to populate the query result into a Java object. There are two main annotations when specifying SQL queries in Java code: Select and Update.

Select Annotation

The Select annotation is used to specify a select query in a Java class for the get method to retrieve data from a database table. Table 2 shows various attributes of the Select annotation and their uses.

Name Type Description
sql String SQL Select query string.
value String Same as sql attribute.
tableName String Name of the database table against which the sql will be invoked.
readOnly, connected, scrollable Boolean Flags used to indicate if the returned DataSet is read-only or updateable, is connected to the back-end database, and is scrollable when used in connected mode respectively.
allColumnsMapped Boolean Flag to indicate if the column names in the sql annotation element are mapped 1-to-1 with the fields in the DataSet.

Here's an example of Select annotation to get all the active loans from the loan database:

interface LoanAppDetailsQuery extends BaseQuery {
        @Select("SELECT * FROM LoanDetais where LoanStatus = 'A'")
        DataSet<LoanApplication> getAllActiveLoans();

The sql annotation allows I/O parameters as well (a parameter marker is represented with a question mark followed by an integer). Here's an example of a parameterized sql query.

interface LoanAppDetailsQuery extends BaseQuery {
        @Select(sql="SELECT * from LoanDetails
                where borrowerFirstName= ?1 and borrowerLastName= ?2")
        DataSet<LoanApplication> getLoanDetailsByBorrowerName(String borrFirstName,
                String borrLastName);

Update Annotation

The Update annotation is used to decorate a Query interface method to update one or more records in a database table. An Update annotation must include a sql annotation type element. Here's an example of Update annotation:

interface LoanAppDetailsQuery extends BaseQuery {
        @Update(sql="update LoanDetails set LoanStatus = ?1
                where loanId = ?2")
        boolean updateLoanStatus(String loanStatus, int loanId);

SQL Exception Handling Enhancements

Exception handling is an important part of Java programming, especially when connecting to or running a query against a back-end relational database. SQLException is the class that we have been using to indicate database related errors. JDBC 4.0 has several enhancements in SQLException handling. The following are some of the enhancements made in JDBC 4.0 release to provide a better developer's experience when dealing with SQLExceptions:

  1. New SQLException sub-classes
  2. Support for causal relationships
  3. Support for enhanced for-each loop

New SQLException classes

The new subclasses of SQLException were created to provide a means for Java programmers to write more portable error-handling code. There are two new categories of SQLException introduced in JDBC 4.0:

  • SQL non-transient exception
  • SQL transient exception

Non-Transient Exception: This exception is thrown when a retry of the same JDBC operation would fail unless the cause of the SQLException is corrected. Table 3 shows the new exception classes that are added in JDBC 4.0 as subclasses of SQLNonTransientException (SQLState class values are defined in SQL 2003 specification.):

Exception class SQLState value
SQLFeatureNotSupportedException 0A
SQLNonTransientConnectionException 08
SQLDataException 22
SQLIntegrityConstraintViolationException 23
SQLInvalidAuthorizationException 28
SQLSyntaxErrorException 42

Transient Exception: This exception is thrown when a previously failed JDBC operation might be able to succeed when the operation is retried without any intervention by application-level functionality. The new exceptions extending SQLTransientException are listed in Table 4.

Exception class SQLState value
SQLTransientConnectionException 08
SQLTransactionRollbackException 40
SQLTimeoutException None

Causal Relationships

The SQLException class now supports the Java SE chained exception mechanism (also known as the Cause facility), which gives us the ability to handle multiple SQLExceptions (if the back-end database supports a multiple exceptions feature) thrown in a JDBC operation. This scenario occurs when executing a statement that may throw more than one SQLException .

We can use getNextException() method in SQLException to iterate through the exception chain. Here's some sample code to process SQLException causal relationships:

catch(SQLException ex) {
     while(ex != null) {
        LOG.error("SQL State:" + ex.getSQLState());
        LOG.error("Error Code:" + ex.getErrorCode());
        LOG.error("Message:" + ex.getMessage());
        Throwable t = ex.getCause();
        while(t != null) {
            LOG.error("Cause:" + t);
            t = t.getCause();
        ex = ex.getNextException();

Enhanced For-Each Loop

The SQLException class implements the Iterable interface, providing support for the for-each loop feature added in Java SE 5. The navigation of the loop will walk through SQLException and its cause. Here's a code snippet showing the enhanced for-each loop feature added in SQLException.

catch(SQLException ex) {
     for(Throwable e : ex ) {
        LOG.error("Error occurred: " + e);

Pages: 1, 2, 3, 4

Next Pagearrow