Database TDD Part 5: Encapsulating JDBC

by Jeff Langr

October 13, 2005

You want your persistence layer to not leak its implementation details into the rest of your application. Most systems make this mistake, however: the fact that SQL via JDBC is being used is explicit in a large number of classes. Changing to a new persistence solution, such as JDO, CMP EJBs, or Hibernate, becomes a major undertaking.

Even if you’re careful to isolate all your JDBC calls to a single class, as I’m trying to do, it’s easy to ooze JDBC-specific code into the rest of your app. The culprit is java.sql.SQLException. Any code that must acknowledge this exception is now dependent on JDBC.

An easy solution would be to throw the more abstract and palatable type, Exception. But I prefer just to not propagate checked exceptions. They are a nuisance. “Inbetween” code rarely can do anything with them; top-level code can usually only report them through the user interface. Developers often lazily catch them and log an error that no one discovers until some insidious defect has escaped. For all the promises of checked exceptions, they tend to do little more than force littering of the code with try/catch blocks and throws clauses. Checked exceptions cause unnecessary duplication.

For the JdbcAccess class, then, I’ve chosen to propagate a runtime exception of a custom type. It could be of type RuntimeException, but using a custom type imparts some immediately useful information.

Driving the custom runtime exception through tests, I end up with a couple tests that look much alike. Here’s one of them:

       public void testExecuteException() {
          try {
             access.execute(BADLY_FORMED_SQL);
             fail(FAILURE_MESSAGE);
          }
          catch (JdbcException e) {
             assertException(e);
          }
       }
    
       private void assertException(JdbcException e) {
          assertEquals(BADLY_FORMED_SQL, e.getMessage());
          assertTrue(e.getCause() instanceof java.sql.SQLException);
       }

The other test is for executeQuery. The exception class:

    public class JdbcException extends RuntimeException {
       public JdbcException(String sql, Throwable cause) {
          super(sql, cause);
       }
    }

As I added the first exception test to the JdbcAccessTest class that I had from yesterday, I realized that the tearDown method deleted a sample table created solely for purposes of the test. The exception tests have no such need for a table, and thus don’t create one. This meant that the tearDown method would now throw an exception when it tried to drop a nonexistent table.

Rather than rework existing JdbcAccessTest code, the most straightforward solution is to create another test class. There’s no rule that says you can only have a single test class for each production class. Each test class can be treated as a separate fixture, with its own context that must be set up and possibly destroyed. Here’s the new test class in its entirety:

    import junit.framework.*;
    
    public class JdbcAccessExceptionsTest extends TestCase {
       private JdbcAccess access;
       private static final String BADLY_FORMED_SQL = "badly formed sql";
       private static final String FAILURE_MESSAGE = "expected exception from malformed sql";
       protected void setUp() {
          access = new JdbcAccess();
       }
    
       public void testExecuteException() {
          try {
             access.execute(BADLY_FORMED_SQL);
             fail(FAILURE_MESSAGE);
          }
          catch (JdbcException e) {
             assertException(e);
          }
       }
    
       public void testExecuteQueryException() {
          try {
             access.executeQuery(BADLY_FORMED_SQL);
             fail(FAILURE_MESSAGE);
          }
          catch (JdbcException e) {
             assertException(e);
          }
       }
    
       private void assertException(JdbcException e) {
          assertEquals(BADLY_FORMED_SQL, e.getMessage());
          assertTrue(e.getCause() instanceof java.sql.SQLException);
       }
    }

The modifications to the production JdbcAccess code involve simply adding try/catch blocks to the public methods.

       public void execute(String sql) {
          try {
             createStatement();
             statement.execute(sql);
             closeConnection();
          }
          catch (SQLException e) {
             throw new JdbcException(sql, e);
          }
       }
    
       public List<String> executeQuery(String sql) {
          try {
             createStatement();
    
             ResultSet results = statement.executeQuery(sql);
             results.next();
             List<String> row = getRow(results);
             results.close();
    
             connection.close();
             return row;
          }
          catch (SQLException e) {
             throw new JdbcException(sql, e);
          }
       }

Don’t forget the best part: going back to UserTest, User, and JdbcAccessTest and eliminating all the throws SQLException clauses plus the importstatement. Your IDE should be able to help you here.

You’ll still need an application-level strategy for managing exceptions at the UI level. Best that you discuss this strategy with your team, and come up with the rules that everyone must play by. One of those rules is that tests should always demonstrate that exceptions can arise.

Share your comment

Jeff Langr

About the Author

Jeff Langr has been building software for 40 years and writing about it heavily for 20. You can find out more about Jeff, learn from the many helpful articles and books he's written, or read one of his 1000+ combined blog (including Agile in a Flash) and public posts.