Setting up infrastructure for SQL tests

In our example, it was simple to open a connection, reset the database state, and so on, but that may become more complicated (or lengthy) when your database schema is complicated. Invest in test infrastructure to facilitate your SQL testing and make sure that when a developer wants to write an integration test, they do not need to set up connections manually or handle transactions. This should be a given from the test suite class.

A strategy I often apply is to create a base class for my integration tests: say, SQLIntegrationTestBase. This base class handles all the magic, such as creating a connection, cleaning up the database, and closing the connection. Then the test class, such as InvoiceDaoTest, which would extend SQLIntegrationTestBase, focuses only on testing the SQL queries. JUnit allows you to put BeforeEach and AfterEach in base classes, and those are executed as if they were in the child test class.

Another advantage of having all the database logic in the test base class is that future changes will only need to be made in one place. Listing 9.23 shows an implementation example. Note how the InvoiceDaoIntegrationTest code focuses primarily on tests.

Listing 9.23 Base class that handles the database-related logic

public class SqlIntegrationTestBase {
 
  private Connection connection;
  protected InvoiceDao dao;                                               ❶
 
  @BeforeEach                                                             ❷
  void openConnectionAndCleanup() throws SQLException {
    // ...
  }
 
  @AfterEach                                                              ❷
  void close() throws SQLException {
    // ...
  }
 
}
 
public class InvoiceDaoIntegrationTest extends SqlIntegrationTestBase {   ❸
 
  @Test
  void save() {                                                           ❹
    // ...
  }
 
  @Test
  void atLeast() {                                                        ❹
    // ...
  }
 
}

❶ Makes the InvoiceDao protected so we can access it from the child classes

❷ The methods are the same as before.

❸ InvoiceDaoTest now extends SqlIntegrationTestBase.

❹ The test class focuses on the tests themselves, as the database infrastructure is handled by the base class.

I will not provide a complete code example, because it changes from project to project. Instead, the following sections list what I do in such an integration test base class.

Opening the database connection

This means opening a JDBC connection, a Hibernate connection, or the connection of whatever persistence framework you use. In some cases, you may be able to open a single connection per test suite instead of one per test method. In this case, you may want to declare it as static and use JUnit’s BeforeAll to open and AfterAll to close it.

Opening and committing the transaction

In more complex database operations, it is common to make them all happen within a transaction scope. In some systems, your framework handles this automatically (think of Spring and its @Transactional annotations). In other systems, developers do it by hand, calling something that begins the transaction and later something that commits it.

You should decide on how to handle transactions in your test. A common approach is to open the transaction and, at the end of the test method, commit the transaction. Some people never commit the transaction, but roll it back once the test is over. Because this is an integration test, I suggest committing the transaction for each test method (and not for the entire test class, as we did for the connection).

Resetting the state of the database

You want all your tests to start with a clean database state. This means ensuring the correct database schema and having no unexpected data in the tables. The simplest way to do this is to truncate every table at the beginning of each test method. If you have many tables, you truncate them all. You can do this by hand (and manually add one truncate instruction per table in the code) or use a smarter framework that does it automatically.

Some developers prefer to truncate the tables before the test method, and others after. In the former case, you are sure the database is clean before running the test. In the latter, you ensure that everything is clean afterward, which helps ensure that it will be clean the next time you run it. I prefer to avoid confusion and truncate before the test method.

Helper methods that reduce the amount of code in the tests

SQL integration test methods can be long. You may need to create many entities and perform more complex assertions. If code can be reused by many other tests, I extract it to a method and move it to the base class. The test classes now all inherit this utility method and can use it. Object builders, frequent assertions, and specific database operations that are often reused are good candidates to become methods in the base class.