JDK 6 and JDBC 4.0 Advanced Concepts

This article addresses some of the advanced data management concepts starting with a new annotations capability added to the JDBC 4.0 specification.

Annotations

Annotations were introduced into the language with JDK 1.5, and now they are making an impact with JDBC 4.0. An annotation is a declarative programming model where comments, associated with a code element, are used to inject code at runtime.

The PreparedStatement example in this chapter can be rewritten as an annotation, greatly reducing the amount of code required by the application developer.

The annotation solution consists of two elements. The first is the declaration of a Query Interface, extending an interface BaseQuery in the java.sql. package. And the second element is a QueryObject used to execute the query.

Start by declaring the interface. You will not have to implement the interface; that will be done for you based on the declared annotation. The annotation is a @Select, it takes the SQL statement as a parameter and maps the parameter of the method with the ?# IN parameter on the statement. Note that unlike the ResultSet object returned in the previous example (in Chapter 6 “Persisting Your Application Using Databases” Professional Java, JDK 6 Edition, Wrox, 2007, ISBN: 978-0-471-77710-6), the DataSet collection is typed with your user-defined class Car:

package wrox.ch6.jdbc;
import java.sql.BaseQuery;
import java.sql.DataSet;
import java.sql.Select;
public interface QueryAnnotationExample extends BaseQuery {

  @Select(sql="SELECT ID, MODEL, MODEL_YEAR FROM CAR
               WHERE MODEL_YEAR = ?1")
  public DataSet<Car> getCarsModelYear( String year );
}

Next, use the object factory to create and execute this statement. That is, by passing the query interface as a parameter, all the work was done for you, and the results are mapped to the collection of objects you specified in the interface:

  public void testQueryAnnotation(  ) {
   QueryAnnotationExample qae = null;
   try {
    String url = "jdbc:derby://localhost:1527/wrox;create=true";
    Connection con = DriverManager.getConnection(url , "APP",
                                                 "password");
    qae = con.createQueryObject(QueryAnnotationExample.class);
  } catch (SQLException e) {
     e.printStackTrace();
  }
   Collection<Car> cars = qae.getCarsModelYear("1999");

Here is a simple loop to print out the results of the query:

   for ( Car c : cars) {
      System.out.println(" car id=" + c.getId() +
                         " model="+c.getModel() +
                         " year="+ c.getYear() );
  }
  }

When this query executes the output will be:

car id=1 model=Honda Accord year=null

You might be thinking, “The year parameter couldn’t have been null. I was filtering on 1999. Why is the year parameter returning null from the query?”

The answer relates back to the Car class definition. The annotation API maps the columns to properties by name. So ID mapped ID, model mapped to model, but year didn’t map to MODEL_YEAR as it was declared in the database. The solution is to either change the parameter to be the same name as the database columns or add a column name annotation to the Car class. @ResultColumn(name="MODEL_YEAR") tells the annotation API the name of the column to which to map the year field.

import java.sql.ResultColumn;
public class Car {
Long id;
String model;
@ResultColumn(name="MODEL_YEAR")
String year;

If you re-execute the example the model year will be populated with the correct information from the statement. A huge time saver compared to working with traditional PreparedStatement.

The next section discusses supporting database transactions.

Managing Transactions

Transaction management is extremely important when dealing with data sources. Transaction management ensures data integrity and data consistency; without it, it would be very easy for applications to corrupt data sources or cause problems with the synchronization of the data. Therefore, all JDBC drivers are required to provide transaction support.

What Is a Transaction?

To explain transactions best, take using an ATM as an example. The steps to retrieve money are as follows:

  1. Swipe your ATM card.
  2. Enter your PIN.
  3. Select the withdrawal option.
  4. Enter the amount of money to withdraw.
  5. Agree to pay the extremely high fee.
  6. Collect your money.

If anything was to go wrong along the way and you didn’t receive your money, you would definitely not want that to reflect on your balance. So a transaction encompasses all the preceding steps and has only two possible outcomes: commit or rollback. When a transaction commits, all the steps had to be successful. When a transaction fails, there should not be any damage done to the underlying data source. In this case, the data that stores your account balance!

Standard Transactions

JDBC transactions are extremely simple to manage. Transaction support is implemented by the DBMS, which eliminates your having to write anything — code-wise — that would be cumbersome. All the methods you need are contained in the Connection object. There are two main methods you need to be concerned about: Connection.commit and Connection.rollback. There isn’t a begin transaction method because the beginning of a transaction is implied when the first SQL statement is executed.

JDBC 3.0 introduced a concept called a savepoint. Savepoints allow you to save moments in time inside a transaction. For example, you could have an application that sends a SQL statement, then invokes a savepoint, tries to send another SQL statement, but a problem arises and you have to rollback. Now instead of rolling back completely, you can choose to rollback to a given savepoint. The following code example demonstrates JDBC transactions and the new savepoint method, Connection.setSavepoint:

Statement stmt = cConn.createStatement();
int nRows = stmt.executeUpdate("INSERT INTO PLAYERS (NAME) " +
                                     VALUES ('Roger Thomas')");
// Create our save point
Savepoint spOne = cConn.setSavepoint("SAVE_POINT_ONE");
nRows = stmt.executeUpdate("INSERT INTO PLAYERS (NAME) " +
                                     VALUES ('Jennifer White')");
// Rollback to the original save point
cConn.rollback(spOne);
// Commit the transaction.
cConn.commit();

From this example, the second SQL statement never gets committed because it was rolled back to SAVE_POINT_ONE before the transaction was committed.

This article is adapted from Professional Java by W. Clay Richardson (Wrox, 2007, ISBN: 978-0-471-77710-6), from Chapter 6, “Persisting Your Application Using Databases.”

Copyright 2007 by WROX. All rights reserved. Reproduced here by permission of the publisher.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read