Database TDD Part 3

by Jeff Langr

October 11, 2005

Time to refactor. I took about 10 minutes and ran through about three different refactoring passes, running JUnit each time to ensure I maintained a green bar.

First pass: move responsibilities into a separate class, JdbcAccess. It’s actually a move that will stave off duplication at some future point, once I have another domain class with persistence needs. But right now I’m following the single responsibility principle as my primary refactoring driver.

Second pass: eliminate common code duplication. Example: extraction of the createStatement method.

I made a couple other minor refactorings, including inlining the loadDriver method into createConnection (it wasn’t pulling its own weight, violating simple design rule #4). The bigger move was divorcing the ResultSet from the need to populate the user object.

The code still has lots of problems, of course. There’s still the presumption that there’s a single row of data, and worse now, the JdbcAccess class presumes there are always two string columns.

Also: The User class still has to import java.sql (because of SQLException). And there’s more duplication in the SQL strings that we’ve not touched on yet. And worse, we now have a class (JdbcAccess) with no unit test coverage.

Code from User.java:

    public void save() throws SQLException {
       new JdbcAccess().execute(
          String.format("insert into userdata (name, password) values ('%s', '%s')", name, password));
    }
    
    public static User find(String nameKey) throws SQLException {
       JdbcAccess access = new JdbcAccess();
       List row =
          access.executeQuery(String.format("select name, password from userdata where name = '%s'", nameKey));
       return new User(row.get(0), row.get(1));
    }

JdbcAccess.java:

    import java.util.*;
    import java.sql.*;
    
    import com.mysql.jdbc.Driver;
    
    public class JdbcAccess {
       private Connection connection;
       private Statement statement;
    
       public void execute(String sql) throws SQLException {
          createStatement();
          statement.execute(sql);
          closeConnection();
       }
    
       public List executeQuery(String sql) throws SQLException {
          createStatement();
    
          ResultSet results = statement.executeQuery(sql);
          results.next();
          List row = getRow(results);
          results.close();
    
          connection.close();
          return row;
       }
    
       private List getRow(ResultSet results) throws SQLException {
          List row = new ArrayList();
          row.add(results.getString(1));
          row.add(results.getString(2));
          return row;
       }
    
       private void createStatement() throws SQLException {
          createConnection();
          statement = connection.createStatement();
       }
    
       private void createConnection() throws SQLException {
          new Driver();
          connection = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=xxx");
       }
    
       private void closeConnection() throws SQLException {
          connection.close();
       }
    }

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.