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.

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

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<String> executeQuery(String sql) throws SQLException {
      createStatement();

      ResultSet results = statement.executeQuery(sql);
      results.next();
      List<String> row = getRow(results);
      results.close();

      connection.close();
      return row;
   }

   private List<String> getRow(ResultSet results) throws SQLException {
      List<String> row = new ArrayList<String>();
      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();
   }
}

posted by Jeff L.  # 10/11/2005 10:00:00 AM Database TDD Blogs