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) { = id;
    public void execute(Connection c) throws SQLException {
      PreparedStatement ps = 
      ps.setString(1, id);

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();

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

    // assert that the command succeeded.

  Connection createConnection() throws Exception {
    // create a new in-memory database.
    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 {
      finally {

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.