Database TDD Part 22: Multiple Rows

by Jeff Langr

November 11, 2005

Lots of code today. The application needs to be able to obtain a list of all the user names that are registered in the system. Starting at the application layer:

ApplicationTest

    package application;
    
    import java.util.*;
    
    import junit.framework.*;
    
    public class ApplicationTest extends TestCase {
       final String name1 = "name1";
       final String password1 = "password1";
       final String name2 = "name2";
       final String password2 = "password2";
    
       private Application application;
    
       protected void setUp() {
          application = new Application();
          application.setUserAccess(new MockUserAccess());
       }
    
       public void testVerifyUser() {
          assertFalse(application.isRegistered(name1, password1));
          application.registerUser(name1, password1);
          assertTrue(application.isRegistered(name1, password1));
       }
    
       public void testRegisteredUsers() {
          application.registerUser(name1, password1);
          application.registerUser(name2, password2);
          Set users = application.getRegisteredUsers();
          assertEquals(2, users.size());
          assertTrue(users.contains(name1));
          assertTrue(users.contains(name2));
       }
    }

Application

    package application;
    
    import java.util.*;
    
    import domain.*;
    
    public class Application {
       private UserAccess userAccess = new UserAccess();
    
       public void registerUser(String name, String password) {
          User user = new User(name, password);
          userAccess.save(user);
       }
    
       public boolean isRegistered(String name, String password) {
          User user = userAccess.find(name);
          return user != null;
       }
    
       void setUserAccess(UserAccess userAccess) {
          this.userAccess = userAccess;
       }
    
       public Set getRegisteredUsers() {
          Set results = new HashSet();
          for (User user: userAccess.getAll())
             results.add(user.getName());
          return results;
       }
    }

To build this solution, I drove all the way down from the application layer, programming by intent. Here in the Application class, the job was to flesh out what the getRegisteredUsers method might look like. I declared the need for a getAll method defined in the data access class.

Getting the code to work meant updating the mock class.

MockUserAccess

    ...
    public class MockUserAccess extends UserAccess {
       private static User user;
       private static List users = new ArrayList();
    
       public User find(String key) {
          return MockUserAccess.user;
       }
    
       public void save(Persistable persistable) {
          MockUserAccess.user = (User)persistable;
          users.add(user);
       }
    
       public List getAll() {
          return users;
       }
    }

Note that there are two kinds of things going on here. I’m not worrying about that yet.

DataAccess

       public List getAll() {
          return new Persister(this).getAll();
       }

I just realized that this method won’t be getting tested as long as I’m using the mock class in ApplicationTest. Looks like a future exercise needs to be an acceptance level test. Right now I’m only concerned about building the units. Acceptance tests are a good end-of-day exercise. I figure so far, from blog entry #1 to #22, I’m probably up to 2:30 in the afternoon, were I to have been working on this codebase nonstop.

PersisterTest

    package persistence;
    
    import java.util.*;
    
    import junit.framework.*;
    
    public class PersisterTest extends TestCase {
       private static final String TABLE = "x";
       private static final Object RETURN_OBJECT1 = "object";
       private static final Object RETURN_OBJECT2 = "object2";
       private static final String BAD_KEY = "not found";
       private static final String COLUMN1 = "a";
       private static final String COLUMN2 = "b";
       private static final String ROW1_VALUE1 = "a1";
       private static final String ROW1_VALUE2 = "a2";
       private static final String ROW2_VALUE1 = "b1";
       private static final String ROW2_VALUE2 = "b2";
       private static final Column[] COLUMNS = { new StringColumn(COLUMN1),
             new StringColumn(COLUMN2) };
    
       private JdbcAccess access;
       private String lastSql;
       private PersistableMetadata metadata;
       private Persister persister;
       private Persistable persistable;
    
       protected void setUp() {
          access = new JdbcAccess() {
             public void execute(String sql) {
                lastSql = sql;
             }
    
             public Map executeQueryExpectingOneRow(String sql,
                   Column[] columns) {
                lastSql = sql;
                if (sql.indexOf(BAD_KEY) > -1)
                   return null;
                return createRow1();
             }
    
             public List> executeQuery(String sql,
                   Column[] columns) {
                lastSql = sql;
    
                List> results = new ArrayList>();
                results.add(createRow1());
                results.add(createRow2());
                return results;
             }
          };
    
          metadata = new PersistableMetadata() {
             public String getTable() {
                return TABLE;
             }
    
             public String getKeyColumn() {
                return COLUMNS[0].getName();
             }
    
             public Column[] getColumns() {
                return COLUMNS;
             }
    
             public Object create(Map row) {
                if (row.get(COLUMN1).equals(ROW1_VALUE1)
                      && row.get(COLUMN2).equals(ROW1_VALUE2))
                   return RETURN_OBJECT1;
                if (row.get(COLUMN1).equals(ROW2_VALUE1)
                      && row.get(COLUMN2).equals(ROW2_VALUE2))
                   return RETURN_OBJECT2;
                return null;
             }
          };
    
          persistable = new Persistable() {
             public Object get(String key) {
                if (key.equals(COLUMN1))
                   return ROW1_VALUE1;
                if (key.equals(COLUMN2))
                   return ROW1_VALUE2;
                return null;
             }
          };
    
          persister = new Persister(metadata, access);
       }
    
       protected Map createRow1() {
          return createRow(ROW1_VALUE1, ROW1_VALUE2);
       }
    
       protected Map createRow2() {
          return createRow(ROW2_VALUE1, ROW2_VALUE2);
       }
    
       protected Map createRow(String value1, String value2) {
          Map row = new HashMap();
          row.put(COLUMN1, value1);
          row.put(COLUMN2, value2);
          return row;
       }
    
       public void testSave() {
          persister.save(persistable);
    
          String expectedSql = String.format(
                "insert into %s (%s,%s) values ('%s','%s')", TABLE, COLUMN1,
                COLUMN2, ROW1_VALUE1, ROW1_VALUE2);
          assertEquals(expectedSql, lastSql);
       }
    
       public void testFindBy() {
          final String key = ROW1_VALUE1;
          assertEquals(RETURN_OBJECT1, persister.find(key));
          String expectedSql = String.format("select %s,%s from %s where %s='%s'",
                COLUMN1, COLUMN2, TABLE, COLUMN1, key);
          assertEquals(expectedSql, lastSql);
       }
    
       public void testFindNotFound() {
          assertNull(persister.find(BAD_KEY));
       }
    
       public void testGetAll() {
          persister.save(persistable);
    
          Persistable persistable2 = new Persistable() {
             public Object get(String key) {
                if (key.equals(COLUMN1))
                   return ROW2_VALUE1;
                if (key.equals(COLUMN2))
                   return ROW2_VALUE2;
                return null;
             }
          };
          persister.save(persistable2);
          List results = persister.getAll();
    
          String expectedSql = String.format("select %s,%s from %s", COLUMN1,
                COLUMN2, TABLE);
          assertEquals(expectedSql, lastSql);
    
          assertEquals(2, results.size());
          assertTrue(results.contains(RETURN_OBJECT1));
          assertTrue(results.contains(RETURN_OBJECT2));
       }
    }

This class got a bit of refactoring. The concept for testing “get all” is pretty much the same as testing find-by, except that there are multiple rows that need to be verified. I’m still not enamored of the complexity in setting up the tests in this class.

Persister

    ...
    public class Persister {
       ...
       public List getAll() {
          String sql = new SqlGenerator().createSelectAll(metadata.getTable(), metadata.getColumns());
          List> rows = access.executeQuery(sql, metadata.getColumns());
          List results = new ArrayList(rows.size());
          for (Map row: rows)
             results.add(metadata.create(row));
          return results;
       }
    }

The new method in Persister is much like the find method, except that once again it’s dealing with multiple rows. Here I had to comment code in getAlluntil I was able to get to a point where it would compile. That meant a diversion to get the new SqlGenerator and JdbcAccess methods in place. That’s my process: write a test, attempt to implement method by intent, comment out non-compiling code, write a test at the lower level as necessary, uncomment out compile code and retest. There are other ways of doing this, of course.

SqlGenerator code was pretty straightforward:

SqlGeneratorTest

       public void testGetAll() {
          assertEquals("select a,b from t", generator.createSelectAll(TABLE, COLUMNS));
       }

SqlGenerator

       public String createSelectAll(String table, Column[] columns) {
          return String.format("select %s from %s", createColumnList(columns),
                table);
       }

Hmm, some minor duplication between that and createFindByKey that I had forgotten about. Let me go back and fix that…

       public String createFindByKey(String tableName, Column[] columns,
             String keyColumn, String keyValue) {
          return String.format("%s where %s='%s'",
             createSelectAll(tableName, columns), keyColumn, keyValue);
       }

You’ll note that in Persister’s getAll method, the return value from executeQuery is a List of rows (maps), instead of a single map that represents a row. In order to build this code incrementally, I first named the new form of the executeQuery method executeQueryMultipleResults. Once I got everything working, I went back and flipped the method names around (so that the abnormal case–executing a query and expecting one row–was the more explicitly named method).

Here’s JdbcAccessTest:

JdbcAccessTest

    package persistence;
    
    import java.util.*;
    
    import junit.framework.*;
    
    public class JdbcAccessTest extends TestCase {
       ...
       private static final String VALUE1 = "a";
       private static final String VALUE2 = "b";
       ...
       public void testExecuteQuery() {
          insertRow();
          assertRow(access.executeQueryExpectingOneRow(createSelectSql(), COLUMNS));
       }
       ...
       public void testExecuteQueryNoResults() {
          assertNull(access.executeQueryExpectingOneRow(createSelectSql() + " where 1=0", COLUMNS));
       }
    
       public void testExecuteQueryMultipleResults() {
          insertRow(VALUE1);
          insertRow(VALUE2);
    
          List> rows = access.executeQuery(createSelectSql(), COLUMNS);
          assertEquals(2, rows.size());
          assertContains(rows, VALUE1);
          assertContains(rows, VALUE2);
       }
    
       private void assertContains(List> rows, String columnValue) {
          Map expectedRow = new HashMap();
          expectedRow.put(COLUMN_NAME, columnValue);
          assertTrue(rows.contains(expectedRow));
       }
    
       private void assertRow(Map row) {
          assertEquals(1, row.size());
          assertEquals(VALUE1, row.get(COLUMN_NAME));
       }
    
       private void insertRow() {
          insertRow(VALUE1);
       }
    
       private void insertRow(String value) {
          String sql = String.format("insert into %s values('%s')", TABLE, value);
          access.execute(sql);
       }
       ...
    }

And finally, JdbcAccess, to which I added one comment regarding something that I might want to look at for performance considerations later.

JdbcAccess

    ...
    public class JdbcAccess {
       ...
       public Map executeQuery(String sql) {
          return executeQueryExpectingOneRow(sql, null);
       }
    
       public Map executeQueryExpectingOneRow(String sql, Column[] columns) {
          List> rows = executeQuery(sql, columns);
          if (rows.isEmpty())
             return null;
          return rows.get(0);
       }
    
       public List> executeQuery(String sql, Column[] columns) {
          try {
             createStatement();
    
             ResultSet results = statement.executeQuery(sql);
             List> rows = new ArrayList>();
    
             if (columns == null) // what if results is empty, wasteful
                columns = createPseudoColumns(results.getMetaData());
    
             while (results.next()) {
                Map row = getRow(results, columns);
                rows.add(row);
             }
             results.close();
    
             connection.close();
             return rows;
          } catch (SQLException e) {
             throw new JdbcException(sql, e);
          }
       }
       ...
    }

One thing I derived from today’s exercise: adding new functionality was a pretty straightforward exercise of digging down through the layers, with a bit of refactoring cleanup along the way. A lot of the code necessary was already in place. As I add more functionality, it creates new groundwork that makes addition of future functionality even easier.

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.