Database TDD Part 18: New Types

by Jeff Langr

November 07, 2005

So far the database layer supports only strings. That’s actually one way to go–persist everything in the database as strings, and parse the information upon retrieval. I’m not going to do that, although I’ve done it before and it works. It really can get people upset. Instead I’ll build a mechanism to managing mapping between Java types and SQL representation of those types.

To drive out the change, I’ll first update the Customer class with the ability to store a balance, based on charges made against the customer’s account:

CustomerTest

    ...
    public class CustomerTest extends TestCase {
       private Customer customer;
       private static final String ID = "123";
       private static final String NAME = "Smelt Systems";
    
       protected void setUp() {
          customer = new Customer(ID, NAME);
       }
    
       public void testCreate() {
          assertField(ID, customer.getId(), Customer.ID);
          assertField(NAME, customer.getName(), Customer.NAME);
       }
    
       public void testBalance() {
          assertEquals(0, customer.getBalance());
          customer.charge(100);
          assertEquals(100, customer.getBalance());
          customer.charge(500);
          assertEquals(600, customer.getBalance());
       }
       ...
    }

Customer

    package domain;
    
    import java.util.*;
    
    import persistence.*;
    
    public class Customer implements Persistable {
       static final String ID = "id";
       static final String NAME = "name";
       static final String BALANCE = "balance";
       private Map values = new HashMap();
    
       public Customer(String id, String name) {
          values.put(ID, id);
          values.put(NAME, name);
          values.put(BALANCE, 0);
       }
    
       public String getId() {
          return get(ID);
       }
    
       public String getName() {
          return get(NAME);
       }
    
       public String get(String key) {
          return (String)values.get(key);
       }
    
       public int getBalance() {
          return (Integer)values.get(BALANCE);
       }
    
       public void charge(int amount) {
          values.put(BALANCE, getBalance() + amount);
       }
    }

In Customer, as the simplest solution, I change the values map to support any type of value, instead of just String values. There now appears to be some lack of symmetry between the getBalance and charge methods. I figure there will be some code commonality when dealing with numeric values, but I’ll wait to see what that looks like.

A modified CustomerAccessTest leads to a quick attempt at what the solution might look like from the CustomerAccess side of the fence.

CustomerAccessTest

    ...
    public class CustomerAccessTest extends TestCase {
       public void testPersist() {
          final String name = "a";
          final String id = "1";
          final int amount = 100;
    
          Customer customer = new Customer(id, name);
          customer.charge(amount);
          CustomerAccess access = new CustomerAccess();
          access.save(customer);
          Customer retrievedCustomer = access.find(id);
          assertEquals(id, retrievedCustomer.getId());
          assertEquals(name, retrievedCustomer.getName());
          assertEquals(amount, retrievedCustomer.getBalance());
       }
    }

CustomerAccess

    ...
    public class CustomerAccess extends DataAccess<Customer> {
       ...
       public String[] getColumns() {
          return new String[] { Customer.ID, Customer.NAME, Customer.BALANCE };
       }
       ...
       public Customer create(List row) {
          Customer customer = new Customer(row.get(0), row.get(1));
          customer.charge(Integer.parseInt(row.get(2)));
          return customer;
       }
    }

The change to the CustomerAccess create method spews badly. The problem is that the get method returns a string. In fact, code all over the place assumes persisted values are always strings. The core of the problem is in JdbcAccess. Currently, a results row is bound to String. My solution for now is to bind it to the Object type, and worry about type mapping at a higher level.

JdbcAccessTest

    ...
    public class JdbcAccessTest extends TestCase {
       ...
       public void testExecuteQuery() {
          access.execute(createTableSQL());
          List row = access.executeQuery(createCountSQL());
          assertEquals(1, row.size());
          assertEquals(0, getLong(row, 0));
       }
       ...
       private long count() {
          List row = access.executeQuery(createCountSQL());
          return getLong(row, 0);
       }
       ...
       private long getLong(List row, int column) {
          return (Long)row.get(column);
       }
    ...
    }

I also recognized that count should have been defined as a long all along.

JdbcAccess

    ...
    public class JdbcAccess {
       ...
       public List executeQuery(String sql) {
          try {
             createStatement();
    
             ResultSet results = statement.executeQuery(sql);
             List row = null;
             if (results.next())
                row = getRow(results);
             results.close();
    
             connection.close();
             return row;
          }
          catch (SQLException e) {
             throw new JdbcException(sql, e);
          }
       }
    
       private List getRow(ResultSet results) throws SQLException {
          List row = new ArrayList();
          for (int i = 1; i <= results.getMetaData().getColumnCount(); i++)
             row.add(results.getObject(i));
          return row;
       }
       ...
    }

Once I make these changes, the compiler leads me to similar required changes in PersisterTest (3 lines), Persister (2 lines), Persistable (1 line: change return type to Object), PersistableMetadata (1 line), CustomerAccess (3 lines), UserAccess, and SqlGenerator (several lines). It also leads to several changes stemming from StringUtil's commaDelimit method (which can now take an array of Objects).

Digging around a bit more, I note a bigger problem. When creating the insert SQL string, the presumption is that all values are string literals. The result is that these values get wrapped with single quotes. That won't work for numeric columns. The metadata for the domain object is going to have to include type information.

I add the following test to SqlGeneratorTest:

       public void testInsertNumericValue() {
          final Object[] values = { "1", 2 };
          final PersistableType[] types = {
             PersistableType.string, PersistableType.integer };
          assertEquals("insert into t (a,b) values ('1',2)",
             generator.createInsert(TABLE, COLUMNS, values, types));
       }

I'm still trying to take the path of least resistance. Adding a new argument to createInsert can be done without a lot of impact to other tests and code.

PersistableType is a new enum that will contain the mapping logic for each type.

    package persistence;
    
    import util.*;
    
    public enum PersistableType {
       string {
          public String sqlValue(Object input) {
             if (input == null)
                return null;
             return StringUtil.wrap(input.toString(), '\'');
          }
       },
       integer {
          public String sqlValue(Object value) {
             if (value == null)
                return null;
             return value.toString();
          }
       };
       abstract String sqlValue(Object value);
    }

Yes, it has tests:

IntegerTypeTest

    package persistence;
    
    import junit.framework.*;
    
    public class IntegerTypeTest extends TestCase {
       private PersistableType type;
    
       protected void setUp() {
          type = PersistableType.integer;
       }
    
       public void testSqlValue() {
          assertEquals("1", type.sqlValue(1));
       }
    
       public void testNull() {
          assertNull(type.sqlValue(null));
       }
    }

StringTypeTest

    package persistence;
    
    import junit.framework.*;
    
    public class StringTypeTest extends TestCase {
       private PersistableType type;
    
       protected void setUp() {
          type = PersistableType.string;
       }
    
       public void testSqlValue() {
          assertEquals("'a'", type.sqlValue("a"));
       }
    
       public void testNullValue() {
          assertNull(type.sqlValue(null));
       }
    }

If I were to modify methods in SqlGenerator by adding type information, it would break lots of other code. So instead I add overloaded methods.

SqlGenerator

    package persistence;
    
    import util.*;
    
    public class SqlGenerator {
       public String createInsert(
             String tableName, String[] columns, Object[] fields, PersistableType[] types) {
          return String.format("insert into %s (%s) values (%s)", tableName,
                createColumnList(columns), createValuesList(fields, types));
       }
    
       private String createValuesList(Object[] fields, final PersistableType[] types) {
          IndexTransformer transformer = new IndexTransformer() {
             public String transform(Object input, int i) {
                return types[i].sqlValue(input);
             }
          };
          return StringUtil.commaDelimit(fields, transformer);
       }
       ...
    }

The ugly short-term solution I come up with was to introduce a new transformer, one that contains the index of each element passed to it. Once the transform method has that index, it can delegate to the sqlValue method of the appropriate type.

    package util;
    
    public interface IndexTransformer {
       String transform(Object object, int index);
    }
    package util;
    
    public class StringUtil {
       ...
       public static String commaDelimit(Object[] objects, IndexTransformer transformer) {
          StringBuilder builder = new StringBuilder();
          for (int i = 0; i < objects.length; i++) {
             if (i > 0)
                builder.append(',');
             builder.append(transformer.transform(objects[i], i));
          }
          return builder.toString();
       }
       ...
    }

Wow, this is a bit of work. The last part: the Persister class must obtain the appropriate type information. This requires a new method in the PersistableMetadata interface.

    package persistence;
    
    import java.util.*;
    
    public interface PersistableMetadata {
       String getTable();
       String[] getColumns();
       String getKeyColumn();
       T create(List row);
       PersistableType[] getTypes();
    }

The interface change impacts UserAccess, CustomerAccess, and PersisterTest. The method in CustomerAccess looks like:

       public PersistableType[] getTypes() {
          return new PersistableType[] {
             PersistableType.string, PersistableType.string, PersistableType.integer };
       }

Back to Customer, since fields are now carried about as untyped objects, they must be cast and returned appropriately.

    public class Customer implements Persistable {
       ...
       private Map values = new HashMap();
       ...
       public String getId() {
          return (String)get(ID);
       }
    
       public String getName() {
          return (String)get(NAME);
       }
    
       public Object get(String key) {
          return values.get(key);
       }
       ...
    }

Finally, success after about twenty minutes of fiddling around and changes to quite a few classes. Fortunately the changes were small and easy to find by using the compiler. But right now the solution is a bit of a mess. The thing that's bugging me most is the disjoint metadata–all those separate arrays suck. Objects, anyone? Tomorrow will be some code cleanup.

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.