Unit testing JDBC-based code can be challenging due to its reliance on live database interactions. Using Mockito, we can mock java.sql components such as DataSource, Connection, and ResultSet to test our data-access logic in isolation. This article demonstrates how to mock JDBC interactions to verify business logic without needing a real database.
1. Domain Model and Service
Before we start mocking, let’s define a practical use case and the corresponding classes we will be testing. Our scenario revolves around an inventory system. The system should fetch product records from a database and return only the still available products (not discontinued and in stock).
We will start by creating a simple domain model for a Product, and then implement the InventoryService class that contains the logic we aim to test.
Product.java
public class Product {
private int id;
private String name;
private boolean discontinued;
private int quantity;
public Product(int id, String name, boolean discontinued, int quantity) {
this.id = id;
this.name = name;
this.discontinued = discontinued;
this.quantity = quantity;
}
// hashCode and equals method removed for brevity
@Override
public String toString() {
return "Product{" + "id=" + id + ", name=" + name + ", discontinued=" + discontinued + ", quantity=" + quantity + '}';
}
}
The Product class serves as our domain object representing a product in the system. It includes an ID, name, a flag to determine if the product is discontinued, and the quantity available in stock.
InventoryService.java
public class InventoryService {
private final DataSource dataSource;
public InventoryService(DataSource dataSource) {
this.dataSource = dataSource;
}
public List<Product> fetchAvailableProducts() {
List<Product> available = new ArrayList<>();
String sql = "SELECT * FROM products";
try (
Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
boolean discontinued = rs.getBoolean("discontinued");
int quantity = rs.getInt("quantity");
if (!discontinued && quantity > 0) {
available.add(new Product(id, name, discontinued, quantity));
}
}
} catch (SQLException e) {
throw new RuntimeException("Failed to fetch products", e);
}
return available;
}
}
The InventoryService class contains the business logic for fetching and filtering product data. It uses a DataSource to get a connection and executes a SQL query to fetch all product records. The fetchAvailableProducts method filters out any product that is either discontinued or has a quantity of zero. All valid products are collected into a list and returned.
2. Unit Test: Mocking JDBC with Mockito
In this section, we demonstrate how to write a unit test that mocks all the JDBC components involved in executing a SQL query – DataSource, Connection, PreparedStatement, and ResultSet using Mockito.
public class InventoryServiceTest {
@Mock
DataSource dataSource;
@Mock
Connection connection;
@Mock
PreparedStatement preparedStatement;
@Mock
ResultSet resultSet;
InventoryService inventoryService;
@Test
void shouldReturnOnlyAvailableProducts() throws Exception {
MockitoAnnotations.openMocks(this);
inventoryService = new InventoryService(dataSource);
// Configure mock behavior
when(dataSource.getConnection()).thenReturn(connection);
when(connection.prepareStatement("SELECT * FROM products")).thenReturn(preparedStatement);
when(preparedStatement.executeQuery()).thenReturn(resultSet);
// Simulate result set rows
when(resultSet.next()).thenReturn(true, true, true, false);
when(resultSet.getInt("id")).thenReturn(1, 2, 3);
when(resultSet.getString("name")).thenReturn("Laptop", "Mouse", "Monitor");
when(resultSet.getBoolean("discontinued")).thenReturn(false, true, false);
when(resultSet.getInt("quantity")).thenReturn(10, 50, 0);
// invoke method
List<Product> availableProducts = inventoryService.fetchAvailableProducts();
// Expected result
List<Product> expected = Arrays.asList(
new Product(1, "Laptop", false, 10)
);
// Verify results
assertEquals(expected, availableProducts);
}
}
At the beginning of the test class, we use Mockito’s @Mock annotation to create mock objects for all JDBC interfaces involved in interacting with a database:
@Mock DataSource dataSource;
This mocks the data source used to acquire database connections.@Mock Connection connection;
A mock of theConnectionobject returned by the data source. We override its behavior to provide a mockPreparedStatementwhen a query is prepared.@Mock PreparedStatement preparedStatement;
This object represents the SQL command that gets executed.@Mock ResultSet resultSet;
A simulation of the result returned by a SQL query. We program it to behave like a dataset with multiple rows.
In the shouldReturnOnlyAvailableProducts() test method, we begin by initializing all mock fields using MockitoAnnotations.openMocks(this) and creating an InventoryService instance, injecting the mocked DataSource.
We then define the behavior of each mock in sequence: calling getConnection() on the data source returns a mock Connection, which produces a mock PreparedStatement when a query is prepared. When that statement is executed, it returns a mock ResultSet.
The result set is configured to simulate three rows of product data. The first row represents a “Laptop” that is not discontinued and has sufficient quantity. The second is a “Mouse” that is discontinued, and the third is a “Monitor” that has zero quantity. This controlled data setup allows us to evaluate how the service handles different availability conditions.
Next, we invoke the fetchAvailableProducts() method, which processes the simulated result set and filters out products that are either discontinued or out of stock, retaining only those available for sale.
Finally, we verify the outcome using assertEquals(). Since only the “Laptop” satisfies the criteria, we assert that the returned list contains just that product. We can now execute the test to ensure that the service correctly filters and maps data from the ResultSet, confirming the method behaves as intended under the mocked scenario.
3. Limitations of Mocking JDBC Directly
While mocking JDBC objects such as Connection, PreparedStatement, and ResultSet using frameworks like Mockito is a helpful approach for unit testing database-related logic, it comes with some notable limitations.
First, these tests tend to be fragile and verbose. JDBC has a low-level, imperative API, and mocking it requires carefully simulating every interaction, every method call and its expected behavior. This can make tests difficult to maintain and prone to errors when the underlying SQL logic changes.
Second, mocking JDBC does not test actual integration with the database. We are only verifying how our code interacts with the mocks, not how it behaves with real data or a live database connection. This makes it easier to miss SQL syntax errors, schema mismatches, or other runtime issues that only arise during actual database interactions.
Third, test readability and clarity can suffer. The mocks introduce noise, especially when configuring ResultSet behavior, which can obscure the business logic being tested. This may reduce the value of the test as a source of documentation or behavior illustration.
4. Suggested Alternatives
To address these challenges, consider the following alternatives:
- Use an In-Memory Database (like H2)
Instead of mocking JDBC, we should configure our test to run against an in-memory relational database. This allows us to write real SQL and test against real data, giving us both confidence and simplicity. Spring Boot, for example, supports this with auto-configuration. - Use Testcontainers
Testcontainers is a Java library that provides lightweight, disposable containers for databases and other services. We can spin up a real instance of PostgreSQL, MySQL, etc., in Docker, run our tests against it, and shut it down afterward. This approach brings the benefits of true integration testing without polluting our local environment. - Use Libraries Like JOOQ Mock
Tools like JOOQ Mock allow the mocking of SQL result sets in a cleaner and more type-safe way than plain JDBC. - Abstract with DAO or Repository Interfaces
Introduce a data access abstraction layer (for example, using Spring Data JPA or plain DAOs) and write unit tests for the service layer using mocks, while separately testing the DAO or repository against a real database. This reduces the need to mock low-level JDBC calls and promotes better separation of concerns.
5. Conclusion
In this article, we explored how to mock JDBC for unit testing using Mockito. We built a simple service that queries product data and demonstrated how to mock key JDBC components like DataSource, Connection, PreparedStatement, and ResultSet. By simulating query results, we were able to test the business logic in isolation without needing an actual database connection.
While this approach is effective for verifying logic and ensuring fast unit test execution, it also comes with some drawbacks, such as increased boilerplate, brittle test code, and the lack of integration validation. To create more comprehensive and maintainable tests, consider using alternatives like in-memory databases, Testcontainers, or abstracting database interactions behind DAO layers.
6. Download the Source Code
This article covered how to perform unit testing by mocking JDBC components.
You can download the full source code of this example here: mocking jdbc unit testing
