We can use JUnit to write SQL tests. All we need to do is (1) establish a connection with the database, (2) make sure the database is in the right initial state, (3) execute the SQL query, and (4) check the output.
Consider the following scenario:
- We have an
Invoicetable composed of aname(varchar, length 100) and avalue(double). - We have an
InvoiceDaoclass that uses an API to communicate with the database. The precise API does not matter. - This DAO performs three actions:
save()persists an invoice in the database,all()returns all invoices in the database, andallWithAtLeast()returns all invoices with at least a specified value. Specifically,
A simple JDBC implementation of such a class is shown in listings 9.17, 9.18, and 9.19.
Listing 9.17 Simple JDBC implementation of InvoiceDao, part 1
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class InvoiceDao {
private final Connection connection; ❶
public InvoiceDao(Connection connection) {
this.connection = connection;
}
public List<Invoice> all() {
try {
PreparedStatement ps = connection.prepareStatement(
➥ "select * from invoice"); ❷
ResultSet rs = ps.executeQuery();
List<Invoice> allInvoices = new ArrayList<>();
while (rs.next()) { ❸
allInvoices.add(new Invoice(rs.getString("name"),
➥ rs.getInt("value")));
}
return allInvoices;
} catch(Exception e) { ❹
throw new RuntimeException(e);
}
}❶ The DAO holds a connection to the database.
❷ Prepares and executes the SQL query
❸ Loops through the results, creating a new Invoice entity for each of them
❹ The JDBC API throws checked exceptions. To simplify, we convert them to unchecked exceptions.
Listing 9.18 Simple JDBC implementation of InvoiceDao, part 2
public List<Invoice> allWithAtLeast(int value) { ❶
try {
PreparedStatement ps = connection.prepareStatement(
➥ "select * from invoice where value >= ?");
ps.setInt(1, value);
ResultSet rs = ps.executeQuery();
List<Invoice> allInvoices = new ArrayList<>();
while (rs.next()) {
allInvoices.add(
new Invoice(rs.getString("name"), rs.getInt("value"))
);
}
return allInvoices;
} catch (Exception e) {
throw new RuntimeException(e);
}
}❶ The same thing happens here: we prepare the SQL query, execute it, and then create one Invoice entity for each row.
Listing 9.19 Simple JDBC implementation of InvoiceDao, part 3
public void save(Invoice inv) {
try {
PreparedStatement ps = connection.prepareStatement(
"insert into invoice (name, value) values (?,?)"); ❶
ps.setString(1, inv.customer);
ps.setInt(2, inv.value);
ps.execute();
connection.commit();
} catch(Exception e) {
throw new RuntimeException(e);
}
}
}❶ Prepares the INSERT statement and executes it
NOTE This implementation is a naive way to access a database. In more complex projects, you should use a professional production-ready database API such as jOOQ, Hibernate, or Spring Data.
Let’s test the InvoiceDao class. Remember, we want to apply the same ideas we have seen so far. The difference is that we have a database in the loop. Let’s start with all(). This method sends a SELECT * FROM invoice to the database and gets back the result. But for this query to return something, we must first insert some invoices into the database. The InvoiceDao class also provides the save() method, which sends an INSERT query. This is enough for our first test.
Listing 9.20 First step of our SQL test
public class InvoiceDaoIntegrationTest {
private Connection connection; ❶
private InvoiceDao dao; ❶
@Test
void save() {
Invoice inv1 = new Invoice("Mauricio", 10); ❷
Invoice inv2 = new Invoice("Frank", 11);
dao.save(inv1); ❸
List<Invoice> afterSaving = dao.all(); ❹
assertThat(afterSaving).containsExactlyInAnyOrder(inv1);
dao.save(inv2); ❺
List<Invoice> afterSavingAgain = dao.all();
assertThat(afterSavingAgain)
.containsExactlyInAnyOrder(inv1, inv2);
}
}❶ This test requires a connection to the database and an invoice DAO.
❹ Gets all invoices from the database and ensures that the database only contains the invoice we inserted
❺ Inserts another invoice and ensures that the database contains both of them
This test method creates two invoices (inv1, inv2), persists the first one using the save() method, retrieves the invoices from the database, and asserts that it returns one invoice. Then it persists another invoice, retrieves the invoices from the database again, and asserts that now it returns two invoices. The test method ensures the correct behavior of both the save() and all() methods. The containsExactlyInAnyOrder assertion from AssertJ ensures that the list contains the precise invoices that we pass to it, in any order. For that to happen, the Invoice class needs a proper implementation of the equals() method.
In terms of testing, our implementation is correct. However, given the database, we have some extra concerns. First, we should not forget that the database persists the data permanently. Suppose we start with an empty database. The first time we run the test, it will persist two invoices in the database. The second time we run the test, it will persist two new invoices, totaling four invoices. This will make our test fail, as it expects the database to have one and two invoices, respectively.
This was never a problem in our previous unit tests: every object we created lived in memory, and they disappeared after the test method was done. When testing with a real database, we must ensure a clean state:
- Before the test runs, we open the database connection, clean the database, and (optionally) put it in the state we need it to be in before executing the SQL query under test.
- After the test runs, we close the database connection.
This is a perfect fit for JUnit’s @BeforeEach and @AfterEach, as shown in the following listing.
Listing 9.21 Setting up and tearing down the database
public class InvoiceDaoIntegrationTest {
private Connection connection;
private InvoiceDao dao;
@BeforeEach
void openConnectionAndCleanup() throws SQLException {
connection = DriverManager.getConnection("jdbc:hsqldb:mem"); ❶
PreparedStatement preparedStatement = connection.prepareStatement(
➥ "create table if not exists invoice (name varchar(100),
➥ value double)"); ❷
preparedStatement.execute();
connection.commit();
connection.prepareStatement("truncate table invoice").execute(); ❸
dao = new InvoiceDao(connection); ❹
}
@AfterEach
void close() throws SQLException {
connection.close(); ❺
}
@Test
void save() { ❻
// ...
}
}❶ Opens a connection to the database. For simplicity, I am using HSQLDB, an in-memory database. In real systems, you may want to connect to the same type of database you use in production.
❷ Ensures that the database has the right tables and schema. In this example, we create the invoice table. You may need something fancier than that in real applications.
❸ Truncates the table to ensure that no data from previous tests is in the database. Again, you may need something fancier in more complex applications.
❺ Closes the connection. You may decide to close the connection only at the end of the entire test suite. In that case, you can use JUnit’s @BeforeAll and @AfterAll.
The openConnectionAndCleanup() method is annotated as @BeforeEach, which means JUnit will run the cleanup before every test method. Right now, its implementation is simplistic: it sends a truncate table query to the database.
NOTE In larger systems, you may prefer to use a framework to help you handle the database. I suggest Flyway or Liquibase. In addition to supporting you in evolving your database schema, these frameworks contain helper methods that help clean up the database and make sure it contains the right schema (that is, all tables, constraints, and indexes are there).
We also open the connection to the database manually, using JDBC’s most rudimentary API call, getConnection. (In a real software system, you would probably ask Hibernate or Spring Data for an active database connection.) Finally, we close the connection in the close() method (which happens after every test method).
Let’s now test the other method: allWithAtLeast(). This method is more interesting, as the SQL query contains a predicate, where value >= ?. This means we have different scenarios to exercise. Here we can use all of our knowledge about boundary testing and think of on and off points.
Figure 9.2 On and off points for the allWithAtLeast() SQL query
Figure 9.2 shows the boundary analysis. The on point is the point on the boundary. In this case, it is whatever concrete number we pass in the SQL query. The off point is the nearest point to the on point that flips the condition. In this case, that is whatever concrete number we pass in the SQL query minus one, since it makes the condition false.
The following listing shows the JUnit test. Note that we add an in point to the test suite. Although it isn’t needed, it is cheap to do and makes the test more readable:
Listing 9.22 Integration test for the atLeast method
@Test void atLeast() { int value = 50; Invoice inv1 = new Invoice("Mauricio", value - 1); ❶ Invoice inv2 = new Invoice("Arie", value); ❶ Invoice inv3 = new Invoice("Frank", value + 1); ❶ dao.save(inv1); ❷ dao.save(inv2); dao.save(inv3); List<Invoice> afterSaving = dao.allWithAtLeast(value); assertThat(afterSaving) .containsExactlyInAnyOrder(inv2, inv3); ❸ }
❶ The on point of the value >= x boundary is x. The off point is x – 1. A random in point can be x + 1.
❷ Persists them all in the database
❸ We expect the method to return only inv2 and inv3.
The strategy we use to derive the test case is very similar to what we have seen previously. We exercise the on and off points and then ensure that the result is correct. Given where value >= ?, where we concretely replace ? with 50 (see the value variable and the inv2 variable), we have 50 as on point and 49 as off point (value - 1 in inv1). In addition, we test a single in point. While doing so is not necessary one more test case is cheap and makes the test strategy more comprehensible.
NOTE Your tests should run against a test database—a database set up exclusively for your tests. Needless to say, you do not want to run your tests against the production database.
Leave a Reply