The bane of unit test suites are false positives and false negatives. False positives are tests that fail even though the code is correct. False negatives are tests that succeed even though the code is incorrect. False negatives scare me, because I wonder what other little surprises lurk in my code. False positives are just annoying. I have to stop what I should be doing and rummage through some old code, only to find out something has changed in the database, or that the test wasn't well written in the first place.

Some people advocate using mock objects to provide a stable database interface, but they are a poor choice for database programming. They increase the chance of a false negative. Unit tests for database code should validate that everything you intended to store was stored, or everything you intended to load was loaded. If the test is simply mocking up the database interface, it isn't testing the code that will be run in the real world. Waste of time.

False positives arise when the test depends of some data in the database staying the same. Of course, the data will change, because that's why I need a database, so eventually the test will fail even though the code is still correct. Say I have a user object. The most simple test I would use is one that loads the user from the database and checks to see everthing is there. I would first write this test in JUnit:

      public void testLoadNextUser() throws SQLException {
        User user = (User) db.load(db.findNextInQueue()); 
        assertEquals("", user.toString());
      }
    

      public void testLoadNextUser() throws SQLException {
        User user = (User) db.load(db.findNextInQueue()); 
        assertEquals("User [id=1232 username='timb' fullname='Tim Burns']",
           user.toString());
      }
    

I've found that regular expressions are a great way to solve this problem.

      public void testLoadNextUser() throws SQLException {
        User user = (User) db.load(db.findNextInQueue()); 
        Perl5Util re = new Perl5Util();
        assertTrue("Expected user=" + user.toString() + " to match regular expression", 
            re.match("m/User [id=(\\d+)\\s+\\.+/", user.toString());
        // Assert the id is > 0 for sanity
        assertTrue("Id=" + id + " must be > 0", 
            Integer.parseInt(re.group(1)) > 0);
      }
    

The "assertTrue" clause in JUnit is especially useful with regular expression matching, because I can see exactly the string if it doesn't match. The ability to pull out groups and perform other logic on the group entries also is invaluable for writing tests that do not fail when something really is wrong.