The pseudo-application I’m building is going to have varied query needs, involving multiple conditionals, matching strings, relational operators, and so on. I want the ability to be more flexible with my where clauses. So far, the existing database layer supports only finding a row by key. I’m going to add the ability to support a second type of criteria, a matching operation against a single column.
Driving from the application down:
ApplicationTest
public void testBrowseCustomers() {
final String id1 = "id1";
final String name1 = "name1";
application.setCustomerAccess(new MockCustomerAccess());
Customer customer1 = new Customer(id1, name1);
application.add(customer1);
assertTrue(application.findMatchingCustomers("a%").isEmpty());
List customers = application.findMatchingCustomers("n%");
assertEquals(1, customers.size());
Customer retrievedCustomer = customers.get(0);
assertEquals(id1, retrievedCustomer.getId());
assertEquals(name1, retrievedCustomer.getName());
}
The above test demonstrates the ability to search against the customer name using SQL wildcard characters (%).
As I often do, I’ll dig my way down through code, laying in the interface, until I find the point at which I can insert some real code, with tests of course. Then I’ll start working my way back up to the application level. The application code I want to get working:
ApplicationTest
public class Application {
...
private CustomerAccess customerAccess = new CustomerAccess();
...
void setCustomerAccess(CustomerAccess customerAccess) {
this.customerAccess = customerAccess;
}
...
public void add(Customer customer) {
customerAccess.save(customer);
}
public List findMatchingCustomers(String namePattern) {
return customerAccess.findMatches(Customer.NAME, namePattern);
}
}
I had to make the Customer constant NAME public. I don’t think that’s a bad thing, but for now I chose to make only that constant public and not any other constants.
I built the MockCustomerAccess class to contain a bit of logic. It converts the SQL pattern matching string into a regex string (presuming that only the wildcard character is specified). This code starts treading some dangerous waters. Mock code should generally be simple and straightforward, meeting only the purposes of the tests. If you find yourself maintaining mock code more than you find yourself maintaining production code, you’ve uncovered a nasty smell. I’ll monitor the need to revisit this code in the future.
MockCustomerAccess
package application;
import java.util.*;
import persistence.*;
import domain.*;
class MockCustomerAccess extends CustomerAccess {
private static List customers = new ArrayList();
public List findMatches(String columnName, String pattern) {
String regexPattern = pattern.replaceAll("%", ".*");
List results = new ArrayList();
for (Customer customer: customers)
if (((String)customer.get(columnName)).matches(regexPattern))
results.add(customer);
return results;
}
public void save(Persistable persistable) {
customers.add((Customer)persistable);
}
}
The findMatches method ends up being implemented on the DataAccess
superclass, since it’s just as generic as anything else. As soon as I
started coding it I recognized that it would have to translate a column
name to a Column object. This makes for simpler client code, and also
encapsulates client code from any changes to the Column interface.
DataAccess
public List findMatches(String columnName, String namePattern) {
Column column = getColumn(columnName);
return new Persister(this).findMatches(column, namePattern);
}
public Column getColumn(String name) {
for (Column column: getColumns())
if (column.getName().equals(name))
return column;
return null;
}
Of course, I built the getColumn method test-first. Here’s the new
test class for DataAccess:
DataAccessTest
package persistence;
import java.util.*;
import junit.framework.*;
public class DataAccessTest extends TestCase {
private static final String NAME1 = "col1";
private static final String NAME2 = "col2";
private static final Column COLUMN1 = new StringColumn(NAME1);
private static final Column COLUMN2 = new StringColumn(NAME2);
public void testGetColumn() {
DataAccess access = new DataAccess() {
public String getTable() {
return null;
}
public String getKeyColumn() {
return null;
}
public Object create(Map row) {
return null;
}
public Column[] getColumns() {
return new Column[] { COLUMN1, COLUMN2 };
}
};
assertEquals(COLUMN1, access.getColumn(NAME1));
assertEquals(COLUMN2, access.getColumn(NAME2));
}
}
Moving down the chain, PersisterTest is always the big bit of pain in writing tests, since it’s where the JDBC mocking comes into play. The listing here shows a bit of refactoring that I did in order to make the tests short and simple.
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_VALUE1_PATTERN = "a%";
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<String, Object> executeQueryExpectingOneRow(String sql,
Column[] columns) {
lastSql = sql;
if (sql.indexOf(BAD_KEY) > -1)
return null;
return createRow1();
}
public List<Map<String, Object>> executeQuery(String sql,
Column[] columns) {
lastSql = sql;
return createTwoRows();
}
};
metadata = new PersistableMetadata() {
public String getTable() {
return TABLE;
}
public String getKeyColumn() {
return COLUMNS[0].getName();
}
public Column[] getColumns() {
return COLUMNS;
}
public Object create(Map<String, Object> 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<String, Object> createRow1() {
return createRow(ROW1_VALUE1, ROW1_VALUE2);
}
protected Map<String, Object> createRow2() {
return createRow(ROW2_VALUE1, ROW2_VALUE2);
}
private List<Map<String, Object>> createTwoRows() {
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
results.add(createRow1());
results.add(createRow2());
return results;
}
protected Map<String, Object> createRow(String value1, String value2) {
Map<String, Object> row = new HashMap<String, Object>();
row.put(COLUMN1, value1);
row.put(COLUMN2, value2);
return row;
}
public void testSave() {
persister.save(persistable);
assertLastSql(String.format(
"insert into %s (%s,%s) values ('%s','%s')", TABLE, COLUMN1,
COLUMN2, ROW1_VALUE1, ROW1_VALUE2));
}
public void testFindBy() {
final String key = ROW1_VALUE1;
assertEquals(RETURN_OBJECT1, persister.find(key));
assertLastSql(String.format("select %s,%s from %s where %s='%s'",
COLUMN1, COLUMN2, TABLE, COLUMN1, key));
}
public void testFindNotFound() {
assertNull(persister.find(BAD_KEY));
}
public void testGetAll() {
assertQueryResults(persister.getAll());
assertEquals(String.format("select %s,%s from %s", COLUMN1,
COLUMN2, TABLE), lastSql);
}
public void testFindMatches() {
assertQueryResults(persister.findMatches(COLUMNS[0], ROW1_VALUE1_PATTERN));
assertLastSql(String.format("select %s,%s from %s where %s like '%s'",
COLUMN1, COLUMN2, TABLE, COLUMN1, ROW1_VALUE1_PATTERN));
}
private void assertLastSql(String sql) {
assertEquals(sql, lastSql);
}
private void assertQueryResults(List results) {
assertEquals(2, results.size());
assertTrue(results.contains(RETURN_OBJECT1));
assertTrue(results.contains(RETURN_OBJECT2));
}
}
The new test method is testFindMatches. It executes the findMatches method against the Persister class. The test then verifies two things: that the correct SQL was built and passed to the JDBC executeQuery method, and that the (hardcoded) results of the query were gathered correctly (assertQueryResults).
Within Persister, the new findMatches method has a lot in common with getAll. Both need to translate a set of results into domain objects. The only distinction is in the where clause of the SQL select statement.
Persister
public List getAll() {
String sql = new SqlGenerator().createSelectAll(metadata.getTable(),
metadata.getColumns());
return executeQuery(sql);
}
public List findMatches(Column column, String pattern) {
String sql = new SqlGenerator().createSelect(metadata.getTable(),
metadata.getColumns(), column, pattern);
return executeQuery(sql);
}
private List executeQuery(String sql) {
List<Map<String, Object>> rows = access.executeQuery(sql, metadata
.getColumns());
List results = new ArrayList(rows.size());
for (Map<String, Object> row : rows)
results.add(metadata.create(row));
return results;
}
Here are SqlGeneratorTest and SqlGenerator. I refactored SqlGenerator after adding createSelect.
SqlGeneratorTest
public class SqlGeneratorTest extends TestCase {
private static Column[] COLUMNS = { new StringColumn("a"),
new StringColumn("b") };
...
public void testFindMatches() {
final String pattern = "v%";
final Column column = COLUMNS[1];
assertEquals("select a,b from t where b like 'v%'", generator
.createSelect(TABLE, COLUMNS, column, pattern));
}
}
SqlGenerator
public class SqlGenerator {
...
public String createSelectAll(String table, Column[] columns) {
return String.format("select %s from %s", createColumnList(columns),
table);
}
public String createFindByKey(String tableName, Column[] columns,
String keyColumn, String keyValue) {
final String criteria = String.format("%s='%s'", keyColumn, keyValue);
return createSelectWithCriteria(tableName, columns, criteria);
}
public String createSelect(String tableName, Column[] columns,
Column column, String pattern) {
final String criteria = String.format("%s like '%s'", column.getName(),
pattern);
return createSelectWithCriteria(tableName, columns, criteria);
}
private String createSelectWithCriteria(String tableName, Column[] columns,
String criteria) {
String whereClause = String.format("where %s", criteria);
return String.format("%s %s", createSelectAll(tableName, columns),
whereClause);
}
}
Where clause criteria needs are going to change. As mentioned earlier, I’ll need multiple conditionals and so on. Right now I’m ok with what I have, but I want to soon shift the burden of constructing a where clause to the client. Otherwise the SqlGenerator class and other code will need to change infinitely into the future, with almost each new query conditional that a client requires. Fixing that is a future installment.
