6.4 Writing Testable JDBC Code

6.4.1 Problem

You want to design your JDBC code so it is testable.

6.4.2 Solution

Modularize your code so that the JDBC connection is created independently of your database logic. This allows you to test your logic using a mock connection, statement, and result set.

This solution illustrates a generally useful pattern. When you create an object, give it references to the objects it needs to talk to, rather than having it go somewhere and get them. This step gives you the ability to reuse the object in other applications or test it in isolation. The idea is not specific to JDBC.

6.4.3 Discussion

A good unit test exercises a small piece of functionality in isolation from the remainder of the system. You may want to test your JDBC logic without actually creating a real database. Testing without a database is advantageous for numerous reasons:

The tests run faster.
You don't have to keep a testing database in sync with your unit tests.
You can test all sorts of error conditions without going to the trouble of creating invalid data in your database.

Testing against a real database is very important, but can be done separately from your other tests. You may end up with 25 tests that actually use a real database and another 50 tests that simulate the database using mock objects. Effective testing requires a variety of approaches (and plenty of creativity) to ensure adequate coverage and good test performance.

Let's start with a class called AccountFactory containing a method that retrieves an Account object from a database. You might write something like this:

public Account getAccount(String acctNumber) throws DataSourceException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DriverManager.getConnection(this.dbUrl);

        ...lots of JDBC code here

    } catch (SQLException e) {
        throw new DataSourceException(e);
    } finally {
        DbUtil.close(rs);
        DbUtil.close(ps);
        DbUtil.close(conn);
    }
}

The DataSourceException is a custom exception that wraps around the underlying SQLException, shielding the caller from the implementation details of this method. The Connection, PreparedStatement, and ResultSet are created within this method, which makes the method difficult to test without setting up a real database.^[1] Example 6-7 shows refactored code that makes the logic testable.

^[1] You could conceivably test this method by writing a mock JDBC driver and registering it with the JDBC DriverManager class. You would then pass a mock database URL to the DriverManager's getConnection() method.

Example 6-7. Refactored database logic

public Account getAccount(String acctNumber) throws DataSourceException {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(this.dbUrl);
        return getAccount(acctNumber, conn);
    } catch (SQLException e) {
        throw new DataSourceException(e);
    } finally {
        DbUtil.close(conn);
    }
}

Example 6-8 shows the package-scope getAccount( ) method, which contains the actual database access logic. This method also uses another helper method called getAccountType( ), which converts a database account type code into a Java constant. Both of these methods are now testable.

Example 6-8. Package-scope method designed for testing

// a package-scope method that makes it easier for a unit test
// to pass in a mock connection
Account getAccount(String acctNumber, Connection conn)
        throws SQLException, DataSourceException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = conn.prepareStatement(
                "SELECT balance, acctType " +
                "FROM Accounts " +
                "WHERE acctNumber = ?");
        ps.setString(1, acctNumber);
        Account acct = null;
        rs = ps.executeQuery(  );
        if (rs.next(  )) {
            double balance = rs.getDouble("balance");
            String acctTypeStr = rs.getString("acctType");
            int acctType = getAccountType(acctTypeStr);            
            acct = new Account(acctType, acctNumber, balance);
        }
        return acct;
    } finally {
        DbUtil.close(rs);
        DbUtil.close(ps);
    }
}

// convert a database account code, such as "CH", into a Java constant
int getAccountType(String acctTypeStr)
            throws SQLException, DataSourceException {
    if ("SA".equals(acctTypeStr)) {
        return Account.SAVINGS;
    }
    if ("CH".equals(acctTypeStr)) {
        return Account.CHECKING;
    }
    throw new DataSourceException("Unknown account type: " + acctTypeStr);
}

The getAccount( ) method can be tested because the ResultSet and Connection are both interfaces and we can provide mock implementations. The getAccountType( ) method is particularly easy to test because we do not need to setup any mock objects.