Skip to main content

Design and Test JDBC Code

Posted by crazybob on February 27, 2004 at 1:51 PM PST

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.

Related Topics >>