Bob Lee's Blog

Design and Test JDBC Code

Simon Brunning solicited advice on Data Access Object (DAO) design. I have a few tricks up my sleeve that don't demand a great deal of effort (no need to mock up InitialContext for example) but that do result in performant and maintainable tests.

Database Commands

I implement JDBC logic using the command pattern:

  interface DatabaseCommand {
    void execute(java.sql.Connection connection) 
      throws java.sql.SQLException;
  }

DatabaseCommand implementations may create and execute JDBC statements or even other commands against the provided connection. I abstract the connection logic to a single place helping avoid repeated coding and common pitfalls such as leaked database connections. The following example deletes a row given an ID field:

  class DeleteCommand implements DatabaseCommand {

    String id;

    DeleteCommand(String id) {
      this.id = id;
    }
  
    public void execute(Connection c) throws SQLException {
      PreparedStatement ps = 
        connection.prepareStatement(DELETE_SQL);
      ps.setString(1, id);
      ps.execute();
    }
  }

I use prepared statements for security and performance. I usually prefer to store my SQL statements in a properties file but constants work as well.

Testing Commands

Connecting to a remote database from tests can be slow and difficult to maintain, especially if you share a database with other developers. I prefer to test against hsqldb, a lightweight 100% Java database engine that can run completely in memory. I can set up and destroy the entire database within the scope of a single test. Keeping database agnostic (so I can test against hsqldb and still use Oracle in production) can be trying at times but is well worth the effort. My tests set up a minimal database (i.e. only what's needed by the test) and execute commands against it:

  public void testDeleteCommand() throws Exception {
    Connection c = createConnection();
    c.createStatement().execute(CREATE_DATABASE_SQL);

    new DeleteCommand("testId").execute(c);

    // assert that the command succeeded.
    ...
    c.close();
  }

  Connection createConnection() throws Exception {
    // create a new in-memory database.
    Class.forName("org.hsqldb.jdbcDriver");
    return DriverManager.getConnection("jdbc:hsqldb:.", "sa", ""); 
  }

Data Access Object (DAO)

My DAO delegates to command objects. I abstract all of the logic for looking up and closing connections to a single method (execute()). If my application leaks connections, I know where to look:

  public class Dao {

    public void delete(String id) throws SQLException {
      execute(new DeleteCommand(id));
    }

    // other data access methods
    ...

    void execute(DatabaseCommand command) throws SQLException {
      // look up DataSource in JNDI and create a connection.
      Connection c = ...;
      try {
        command.execute(c);
      }
      finally {
        c.close();
      }
    }
  }

Cutting out the network and minimizing the amount of test data has really paid off on my current project. My entire suite of JDBC test cases executes in under a second.

Comments

• Row level security
  I am working on an application that uses DAO objects similar to what you have suggested. The application limits the rows a user can view based on the user’s roles. For example, only users in the ‘international’ department can view international wholesalers. The way I have implemented this is not very pretty.

  Do you have any suggestions for implementing something like this?
  

  Posted by: nickhomeaccount on March 01, 2004 at 07:26 AM

• Row level security
  Great question. I prefer to handle such logic at the domain model layer, i.e. I map the ResultSet to my domain objects and then do something like user.hasAccessTo(wholesaler).

  Posted by: crazybob on March 01, 2004 at 08:51 AM

• Testing with hsqldb
  In a recent project also did testing with an in-memory database for a project that actually runs on Oracle. Initially I also tried hsqldb but I ran into a limitation: hsqldb doesn't have schemas. Our administrators require the applications to run as not schema owners so we have to specify schema prefix for every table in from clauses. So we now use McKoi which supports schemas and also allows an in-memory database.

  Have you had this problem and how have you solved it?

  Posted by: erikj on March 31, 2004 at 11:40 AM

• Testing with hsqldb
  I haven't run into that particular problem, but I do have my SQL statements and table names in a properties file. A few of the properties are hsqldb or oracle-specific. For example, I don't use the same database creation scripts (the Oracle ones would obviously never run against hsqldb). Instead I set up the minimal amount necessary for the test. It sounds like you have your situation figured out. If I ran into the same problem, but wanted to continue using hsqldb, I'd probably abstract the table name code (so that it would use schemas when deployed but just the table name in unit tests).

  Posted by: crazybob on March 31, 2004 at 11:53 AM

• commands
  I also used commands in the past and came up with a nice abstract set of classes (base commands for void and object return ops) that managed the connection, statements and result set. You might apply this as wel lif you don't already :-P. This worked well but I didn't write them by hand. I wrote a simple generator for DAOs and commands. Now using spring and lately pretty often hibernate ... also with the generator (based on middlegen now). I can't seem to convince myself to write database access code by hand anymore ...

  Posted by: dorel on May 07, 2004 at 03:20 AM

This work is licensed under a Creative Commons License.