4.20 Testing Database Logic

4.20.1 Problem

You want to test database logic using JUnit.

4.20.2 Solution

Write scripts to generate a stable testing database and test against that data.

4.20.3 Discussion

Testing against a database is challenging in many organizations because you have to define predictable data.^[12] The only truly reliable approach is to create the test data in a private database automatically for each set of tests. When the tests are finished, you should destroy the test data. If you create the test database manually, you run the risk of corruption over time as people modify data that your tests assume is present.

^[12] This is a political battle in many companies, because the database administrators might not give programmers the permission to create new tables or perform other functions necessary to create test data.

For very large databases, you may have to settle for either creating clean test data daily or weekly, or loading a subset of the database with well-known testing records.

We recommend that you follow the technique outlined in Recipe 4.7 to perform one-time setup and tear down before and after a group of tests. You can create the test data in the one-time setup, and remove it in the one-time tear down. Once you have control of the data, you can test against that data:

public void testDeleteEmployee(  ) throws SQLException {
    EmployeeDAO dao = new EmployeeDAO(  );
    assertNotNull("Employee 'ericBurke' should be present",
            dao.getEmployee("ericBurke"));
    dao.deleteEmployee("ericBurke");
    assertNull("Employee 'ericBurke' should not be present",
            dao.getEmployee("ericBurke"));
}

Another challenge is the fact that early tests might modify data in ways that interfere with later tests. In these cases, you can either write functions to clean up data after the earlier tests run, or you can build your test suites manually. By building the suites manually, you can control the order in which the tests run:

public static Test suite(  ) {
    TestSuite suite = new TestSuite(  );
    suite.addTest(new TestEmployeeDB("testCreateEmployee"));
    suite.addTest(new TestEmployeeDB("testUpdateEmployee"));
    suite.addTest(new TestEmployeeDB("testDeleteEmployee"));
    return suite;
}

Database-specific copy, backup, and restore mechanisms are sometimes tremendously faster than reinitializing the database with a series of SQL statements. For example, if your database is MS SQL Server, you can copy over a known testing database .mdf/.ldf file to get your database to a known state very quickly.