How To Do DB Testing (Part I, General Ideas)

Not that I expect this to be a tome of wisdom or anything, but I thought I might put into writing some of the wins we've had lately in being able to test our DB code. First, some background.

I got to design most of the DB layer of a web app. To prototype, I threw together a simple JDBC wrapper class to manage a connection and perform queries. This worked well for starters, but soon the number of queries got unmanageable. So we split out the connection stuff into a base class, and split the queries themselves into various derived classes. This is nice, as it means we can find all of the queries for the whole system in one easily spotted set of source file, and those source files have nothing else in them.

The other thing we did that works really well is to have the query manager class act like an Iterator. The big win here is that next() returns a HashMap<String,String> that contains the data from each row, and as a result, nobody anywhere in the whole system deals with JDBC classes outside of the query manager.

Here's the skeleton:

class QueryManager
{
   protected Iterator<HashMap<String,String>> executeQuery( String sql ) { ... }
}

class SpecificQueryManager extends QueryManager
{
   public Iterator<HashMap<String,String>> getPeople() { executeQuery( "select * from people where ..." ); }
}