Nifty JDBC 4.0

Today I've been playing with some of the new JDBC 4.0 features in the latest JDK6 beta. In particular, I've been scouting around for a lighter-weight alternative to Hibernate when working with small-sized projects, and one that uses annotations rather than XML.

A few weeks ago I tried out Ammentos, and although in the right direction it did not quite handle everything I threw at it. JDBC 4.0, however, seems just the thing with a little extra effort.

First some setup. After grabbing the latest JDK6 build I needed a database with a JDBC 4.0 driver. Some googling revealed that the also nifty Apache Derby database project is such a database, however only special daily builds have support turned on. Fair enough, it was only a download away and JDK6 is still in beta.

Finally, time for some code (ignoring imports, error handling and such):

public static void main(final String[] args)
        throws ClassNotFoundException, SQLException {
    Class.forName(EmbeddedDriver.class.getName());

    final Connection connection = DriverManager.getConnection(
        "jdbc:derby:test-database;create=true");

    final Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE BOB"
            + "(ID INT NOT NULL GENERATED ALWAYS AS IDENTITY,"
            + " NAME VARCHAR(32))");

    connection.setAutoCommit(false);
    connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);

    final BobQuery query = connection.createQueryObject(BobQuery.class);

    final String name = "the Builder";
        
    final BobKeys key = query.addBob(name).get(0);
    final Bob bob = query.findBobByName(name).get(0);

    System.out.println("inserted = selected? " + key.id.equals(bob.id));

    query.close();
    statement.execute("DROP TABLE BOB");
    connection.commit();
    statement.close();
    connection.close();
}

public class Bob {
    public BigDecimal id;
    public String name;
}

@AutoGeneratedKeys
public class BobKeys {
    public BigDecimal id;
}

public interface BobQuery
        extends BaseQuery {
    @Update(sql = "INSERT INTO BOB(name) VALUES(?1)",
            keys = RETURNED_KEYS_DRIVER_DEFINED)
    DataSet addBob(final String name);

    @Select("SELECT * FROM BOB WHERE name = ?1")
    DataSet findBobByName(final String name);

    @Select("SELECT * FROM BOB")
    DataSet findAllBobs();

    @Update("DELETE FROM BOB")
    int removeAllBobs();
}

Everything worked!

(And Derby helpfully creates a scratch database in-place given the proper JDBC URL.)

There were some gotchas:

• The Derby driver did not seem to use the new service feature at least for the embedded case shown here.
• The type for primary key identifiers is BigDecimal, not Long as I guessed at. Improved javadocs would help here.
• I had little luck with the {fieldName} syntax shown in the @Update annotation javadocs for auto-generated keys. I am unsure if the driver, the JDK, the javadocs or the coder is at fault.

JDBC 4.0 is particularly well thought-out for custom persistence layers. I suspect much was borrowed conceptually from Hibernate's excellent work. To use the common DAO pattern as an example, just consider insertion:

public int addBob(final Bob bob) {
    final DataSet keys = addBob(bob.getName());

    if (keys.isEmpty()) return 0;

    bob.id = keys.get(0).id;

    return keys.size();
}

So much boilerplate code saved, a real boon to the coder in the trenches; the code is very readable; no SQLException peppering the entire DAO layer. (Of course my curiosity rises: what does happen when the database cannot insert? — I need to read more on getSQLWarnings().)

JDBC 4.0 is nifty indeed.

UPDATE: As noted in one of the trackbacks, the Derby JDBC 4.0 driver now supports the service discovery mechanism. No more need to call Class.forName. Excellent.