Mocking JDBC for Unit Testing

1. Overview

In this tutorial, we’ll discuss testing code that uses JDBC objects to interact with the database. Initially, we’ll use Mockito to stub all the java.sql objects involved in acquiring a JDBC Connection, creating a Statement, executing a query and retrieving the data from the ResultSet.

After that, we’ll analyze the pros and cons of this approach and understand why nesting mocks often lead to fragile tests. Finally, we’ll explore alternatives such as opting for tests with a larger scope or refactoring our code to increase its testability.

2. Code Samples

For the code samples in this article, we’ll assume we are working on a Java class responsible for executing an SQL query, filtering the ResultSet, and mapping it to Java objects.

The class we want to test will receive a DataSource as a dependency, and it’ll use this DataSource to acquire a Connection, create a Statement, and execute a query:

class CustomersService {

    private final DataSource dataSource;

    // constructor

    public List<Customer> customersEligibleForOffers() throws SQLException {
        try (
            Connection conn = dataSource.getConnection(); 
            Statement stmt = conn.createStatement()
        ) {
            ResultSet resultSet = stmt.executeQuery("SELECT * FROM Customers");
            List<Customer> customers = new ArrayList<>();

            while (resultSet.next()) {
                Customer customer = mapCustomer(resultSet);
                // more business logic ...
                if (customer.status() == Status.ACTIVE 
                  || customer.status() == Status.LOYAL) {
                    customers.add(customer);
                }
            }
            return customers;
        }
    }

    private Customer mapCustomer(ResultSet resultSet) throws SQLException {
        return new Customer(
            resultSet.getInt("id"),
            resultSet.getString("name"),
            Status.valueOf(resultSet.getString("status"))
        );
    }
}

As we can see in the customersEligibleForOffer() method, we query the Customers table and programmatically filter out the entries with an inadequate status. Then, we leverage ResultSet’s API to get the relevant information for creating Customer objects and return a list of them.

3. Mocking java.sql Objects

To test the CustomerService class, we can try mocking the DataSource dependency. However, we’ll soon realize that a single mock object will not be enough. This happens because the DataSource initiates a Connection, which creates a Statement that produces a ResultSet containing the raw data we want to stub.

Simply put, if we want to use Mockito to stub the data from the ResultSet, we’ll need to introduce four mocks:

@ExtendWith(MockitoExtension.class)
class JdbcMockingUnitTest {

    @Mock
    DataSource dataSource;
    @Mock
    Connection conn;
    @Mock
    Statement stmt;
    @Mock
    ResultSet resultSet;

    @Test
    void whenFetchingEligibleCustomers_thenTheyHaveCorrectStatus() throws Exception {
        // ...
    }

}

Then, we’ll make sure that each mock – when invoked – returns the next one, starting from the DataSource all the way to the ResultSet:

@Test
void whenFetchingEligibleCustomers_thenTheyHaveCorrectStatus() throws Exception {
    CustomersService customersService = new CustomersService(dataSource);

    when(dataSource.getConnection())
      .thenReturn(conn);
    when(conn.createStatement())
      .thenReturn(stmt);
    when(stmt.executeQuery("SELECT * FROM customers"))
      .thenReturn(resultSet);
    
    // ...
}

After that, we’ll add the stubbing for the ResultSet itself. Let’s configure it to return three customers with different statuses:

when(resultSet.next())
  .thenReturn(true, true, true, false);
when(resultSet.getInt("id"))
  .thenReturn(1, 2, 3);
when(resultSet.getString("name"))
  .thenReturn("Alice", "Bob", "John");
when(resultSet.getString("status"))
  .thenReturn("LOYAL", "ACTIVE", "INACTIVE");

Finally, we’ll perform some assertions. In this case, we expect only Alice and Bob to be returned as customers eligible for offers.

Let’s take a look at the whole test:

@Test
void whenFetchingEligibleCustomers_thenTheyHaveCorrectStatus() throws Exception {
    //given
    CustomersService customersService = new CustomersService(dataSource);

    when(dataSource.getConnection())
      .thenReturn(conn);
    when(conn.createStatement())
      .thenReturn(stmt);
    when(stmt.executeQuery("SELECT * FROM customers"))
      .thenReturn(resultSet);

    when(resultSet.next())
      .thenReturn(true, true, true, false);
    when(resultSet.getInt("id"))
      .thenReturn(1, 2, 3);
    when(resultSet.getString("name"))
      .thenReturn("Alice", "Bob", "John");
    when(resultSet.getString("status"))
      .thenReturn("LOYAL", "ACTIVE", "INACTIVE");

    // when
    List<Customer> eligibleCustomers = customersService.customersEligibleForOffers();

    // then
    assertThat(eligibleCustomers).containsExactlyInAnyOrder(
        new Customer(1, "Alice", Status.LOYAL),
        new Customer(2, "Bob", Status.ACTIVE)
    );
}

That’s it! We can now execute the test and verify that the tested component correctly filters and maps the ResultSet data.

4. Disadvantages

Even though our solution allows testing the filtering and mapping logic, the test is verbose and fragile. While we can extract some helper methods to improve the readability, the test is tightly coupled to the implementation.

Nested mocks require a strict sequence of method invocations. This makes tests fragile, failing even when refactoring doesn’t change our function’s behavior. This fragility arises because our component is tightly coupled to the DataSource it receives as a dependency. If we refactor our code to interact differently with any of the mocked objects, the test will fail even if the underlying behavior remains unchanged.

Simply put, the mocks don’t allow us to change how we acquire the connection, the type of statement we use, or the SQL query itself – without breaking the test. The same happens if we switch to a higher-level API to interact with the database, like JdbcTemplate or JdbcClient.

Sometimes, tests that are too fragile or hard to write reveal deeper design issues. In our case, they expose the lack of a clear separation between our custom logic and the internals of our persistence layer – a violation of the Single Responsibility Principle.

5. Alternatives

We can avoid the anti-pattern of mocks returning other mocks by testing a larger scope. Instead of writing a unit test and mocking JDBC objects, we can write an integration test and embrace database interaction, leveraging tools like the Embedded H2 Database or Testcontainers.

On the other hand, we can refactor our code to separate the two distinct responsibilities. For example, we can apply the Dependency Inversion Principle and ensure that CustomerServiceV2 depends on an interface for fetching data from the database, rather than directly depending on the java.sql API. Usually, we’ll create a custom interface, but for brevity, we’ll use Java’s Supplier:

class CustomersServiceV2 {

    private final Supplier<List<Customer>> findAllCustomers;

    // constructor

    public List<Customer> customersEligibleForOffers() { 
        return findAllCustomers.get()
          .stream()
          .filter(customer -> customer.status() == Status.ACTIVE 
            || customer.status() == Status.LOYAL)
          .toList();
    }

}

Next, we’ll create a class that implements the Supplier<List<Customer>> interface and internally uses JDBC to fetch and map the data:

class AllCustomers implements Supplier<List<Customer>> {

    private final DataSource dataSource;

    // constructor

    @Override
    public List<Customer> get() {
        try (
          Connection conn = dataSource.getConnection(); 
          Statement stmt = conn.createStatement()
        ) {
            ResultSet resultSet = stmt.executeQuery("SELECT * FROM customers");
            List<Customer> customers = new ArrayList<>();
            while (resultSet.next()) {
                customers.add(mapCustomer(resultSet));
            }
            return customers;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private Customer mapCustomer(ResultSet resultSet) throws SQLException {
        // ...
    }

}

Since our domain service is decoupled from the persistence mechanism, we can freely test our business logic without creating mocks for each JDBC object. Moreover, Supplier is a functional interface, meaning we can test CustomerServiceV2 with no mocks at all! Instead, we’ll simply provide a different, in-lined test implementation of the Supplier using a lambda expression:

@Test
void whenFetchingEligibleCustomersFromV2_thenTheyHaveCorrectStatus() {
    // given
    List<Customer> allCustomers = List.of(
        new Customer(1, "Alice", Status.LOYAL),
        new Customer(2, "Bob", Status.ACTIVE),
        new Customer(3, "John", Status.INACTIVE)
    );

    CustomersServiceV2 service = new CustomersServiceV2(() -> allCustomers);

    // when
    List<Customer> eligibleCustomers = service.customersEligibleForOffers();

    // then
    assertThat(eligibleCustomers).containsExactlyInAnyOrder(
        new Customer(1, "Alice", Status.LOYAL),
        new Customer(2, "Bob", Status.ACTIVE)
    );
}

These alternatives enable us to refactor the code or change the way we fetch data from the database without affecting the test outcome. As we can see, both approaches focus more on the system behavior and result in more robust tests.

6. Conclusion

In this article, we learned how to use Mockito to test code that uses JDBC to interact with the database. We discovered that the java.sql API will require us to create multiple, nested mocks – and we covered why this might be considered a bad practice.

Following that, we refactored our code towards a more testable solution and verified it without using any mock whatsoever. Additionally, we discussed the usage of specialized tools such as Embedded H2 Database or Testcontainers that enable us to truly test the integration between our code and the database.