Unit testing the JDBC (Java Database Connectivity) layer is crucial for building robust and maintainable database-driven applications. Many developers struggle with testing JDBC code because it directly interacts with external databases. However, with the right approach, you can effectively unit test your JDBC components, ensuring they function as expected.
This article explores strategies, tools, and best practices for unit testing the JDBC layer. Below is our lightweight and efficient JDBC testing framework, designed for developers who need a reliable and scalable approach to database testing. Our solution seamlessly integrates JUnit 5, Liquibase, and an H2 in-memory database, ensuring that your database layer is thoroughly tested without dependencies on external frameworks like Spring Boot.
Why Unit Test the JDBC Layer?
The JDBC layer serves as the bridge between your Java application and the database. Bugs in this layer can lead to incorrect data retrieval, failed transactions, and even security vulnerabilities. Unit testing this layer helps:
- Identify issues early in the development cycle
- Ensure SQL queries work as expected
- Improve application stability and maintainability
- Reduce reliance on manual database testing
Why Choose Our Approach?
- Fast & Efficient: Leverage an in-memory H2 database for rapid test execution without persistent storage concerns.
- Version-Controlled Database Schema: Use Liquibase to manage database migrations and keep your schema consistent across different environments.
- Pure JDBC Implementation: No additional frameworks required—just standard Java and JDBC for direct database interactions.
- JUnit 5 Integration: Write clean and maintainable test cases with modern testing features like annotations and assertions.
- Automated & Reproducible: Ensure database integrity with automated tests that reset the schema for each run.
Key Features
- Zero External Dependencies – Works without Spring Boot or other frameworks.
- Liquibase Migration Support – Keep your database schema in sync effortlessly.
- Optimized for CI/CD – Compatible with automated pipelines for continuous testing.
- Lightweight & Scalable – Run tests quickly without affecting production databases.
How It Works
- Liquibase initializes the schema in an in-memory H2 database before tests run.
- JUnit 5 executes test cases against the database using standard JDBC.
- Tests are isolated to ensure consistent and reproducible results.
Get Started Today
Enhance your database testing with our structured and efficient approach. Whether you're working on a small project or a large-scale enterprise system, this framework provides a robust, flexible, and future-proof testing strategy.
Steps:
- Set up dependencies: Use H2 for an in-memory database, Liquibase for schema migrations, and JUnit 5 for testing.
- Configure Liquibase: Define database changes using a
changelog.xmlfile. - Write a simple DAO: Implement basic CRUD operations.
- Test with JUnit 5: Initialize Liquibase before running tests.
1. Dependencies (Maven)
Add these to your pom.xml:
<dependencies>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.14.1</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>5.0.1</version>
</dependency>
<!-- Liquibase for database migrations -->
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>4.31.0</version>
</dependency>
<!-- H2 Database (in-memory) -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.3.232</version>
<scope>test</scope>
</dependency>
<!-- JUnit 5 -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.11.4</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>5.11.4</version>
<scope>test</scope>
</dependency>
<!-- JDBC Driver -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.47.2.0</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.jacoco</groupId>
<artifactId>jacoco-maven-plugin</artifactId>
<version>0.8.8</version>
</dependency>
</dependencies>
2. Liquibase Changelog (db/changelog.xml)
This defines our database schema.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">
<changeSet id="create_User_Role_Table" author="scelac" >
<preConditions onFail="WARN">
<not>
<tableExists tableName="user_role"/>
</not>
</preConditions>
<comment>Create User Role Table </comment>
<createTable tableName="user_role">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="role_name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="create_User_Table" author="scelac" >
<preConditions onFail="WARN">
<not>
<tableExists tableName="users"/>
</not>
</preConditions>
<comment>Create User Role Table </comment>
<createTable tableName="users" >
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="first_name" type="varchar(50)"/>
<column name="last_name" type="varchar(50)"/>
<column name="user_name" type="varchar(50)">
<constraints nullable="false" unique="true"/>
</column>
<column name="role" type="int">
<constraints nullable="false"/>
</column>
<column name="enabled" type="TINYINT"/>
<column name="created_date" type="DATETIME"/>
<column name="last_login_date" type="DATETIME"/>
</createTable>
</changeSet>
<changeSet id="add_user_roles" author="scelac">
<insert tableName="user_role">
<column name="id" valueNumeric="1"/>
<column name="role_name" value="PM"/>
</insert>
<insert tableName="user_role">
<column name="id" valueNumeric="2"/>
<column name="role_name" value="SCRUM_MASTER"/>
</insert>
<insert tableName="user_role">
<column name="id" valueNumeric="3"/>
<column name="role_name" value="TESTER"/>
</insert>
<insert tableName="user_role">
<column name="id" valueNumeric="4"/>
<column name="role_name" value="DEVELOPER"/>
</insert>
</changeSet>
<changeSet id="add_users" author="scelac" >
<insert tableName="users">
<column name="id" valueNumeric="1"/>
<column name="first_name" value="Jon"/>
<column name="last_name" value="Davis"/>
<column name="user_name" value="This email address is being protected from spambots. You need JavaScript enabled to view it. "/>
<column name="role" value="1"/>
<column name="enabled" value="1"/>
<column name="created_date" value="2023-01-25 19:25:40"/>
<column name="last_login_date" value="2023-01-25 19:25:40"/>
</insert>
<insert tableName="users">
<column name="id" valueNumeric="2"/>
<column name="first_name" value="James"/>
<column name="last_name" value="Flores"/>
<column name="user_name" value="This email address is being protected from spambots. You need JavaScript enabled to view it. "/>
<column name="role" value="4"/>
<column name="enabled" value="1"/>
<column name="created_date" value="2023-01-25 19:25:40"/>
<column name="last_login_date" value="2023-01-25 19:25:40"/>
</insert>
<insert tableName="users">
<column name="id" valueNumeric="3"/>
<column name="first_name" value="Robert"/>
<column name="last_name" value="Green"/>
<column name="user_name" value="This email address is being protected from spambots. You need JavaScript enabled to view it. "/>
<column name="role" value="3"/>
<column name="enabled" value="1"/>
<column name="created_date" value="2023-01-25 19:25:40"/>
<column name="last_login_date" value="2023-01-25 19:25:40"/>
</insert>
<insert tableName="users">
<column name="id" valueNumeric="4"/>
<column name="first_name" value="Michael"/>
<column name="last_name" value="Adams"/>
<column name="user_name" value="This email address is being protected from spambots. You need JavaScript enabled to view it. "/>
<column name="role" value="2"/>
<column name="enabled" value="1"/>
<column name="created_date" value="2023-01-25 19:25:40"/>
<column name="last_login_date" value="2023-01-25 19:25:40"/>
</insert>
</changeSet>
<changeSet id="create_tasks_Table" author="scelac" >
<preConditions onFail="WARN">
<not>
<tableExists tableName="user_role"/>
</not>
</preConditions>
<comment>Create User Task Table </comment>
<createTable tableName="tasks">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="title" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="description" type="LONGTEXT">
</column>
<column name="status" type="VARCHAR(10)">
<constraints nullable="false"/>
</column>
<column name="owner_id" type="int">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
3. JDBC Utility Class (DatabaseUtils.java)
Challenges in Testing the JDBC Layer
Unit testing JDBC code presents unique challenges, including:
- Database Dependency: Unit tests should not rely on an actual database.
- State Management: Databases persist data, making it difficult to reset state between tests.
- Complex Query Execution: Validating SQL queries without executing them is difficult.
- Performance Overhead: Connecting to and interacting with a database slows down test execution.
To overcome these challenges, we use in-memory databases and dependency injection.
This manages the connection and Liquibase migrations. How to use HikariCP for JDBC connection pool, using an In-Memory Database (H2). While mocks are useful, they don’t execute actual SQL queries. For better validation, you can use an in-memory database like H2.
/**
* @author scelac
*/
public class DatabaseTestUtils {
private static final String JDBC_URL = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
private static final String USER = "sa";
private static final String PASSWORD = "";
private static final HikariDataSource dataSource;
static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(JDBC_URL); // In-memory DB
config.setUsername(USER );
config.setPassword(PASSWORD);
config.setMaximumPoolSize(10);
config.setMinimumIdle(2);
config.setIdleTimeout(30000);
config.setMaxLifetime(1800000);
config.setConnectionTimeout(3000);
dataSource = new HikariDataSource(config);
}
public static DataSource getConnection() {
return dataSource;
}
public static void runMigrations() {
try (Connection conn = getConnection().getConnection()) {
Liquibase liquibase = new Liquibase(
"db/changelog.xml"
, new ClassLoaderResourceAccessor()
, new JdbcConnection(conn)
);
liquibase.update("");
} catch (Exception e) {
throw new RuntimeException("Failed to run Liquibase migrations", e);
}
}
}
Benefits of Using H2:
- Executes real SQL queries
- Avoids the need for an external database
- Provides fast, isolated test runs
4. DAO Layer (UserDao.java)
This class interacts with the database below is not all code of the class, on this link, you can find more about how to implement DAO layer.
public class UserDaoImpl extends AbstractDAO<User> implements UserDao {
private static final Logger logger = LogManager.getLogger(UserDaoImpl.class);
public UserDaoImpl(DataSource dataSource) {
super(dataSource);
}
@Override
public User findOne(Long id) {
String sql = "select u.* from users u where u.id = ?";
User user = null;
try (PreparedStatement statement = getConnection().prepareStatement(sql)) {
statement.setLong(1, id);
try (ResultSet result = statement.executeQuery()) {
while (result.next()) {
user =
new User(
result.getLong("id"),
result.getString("user_name"),
result.getString("first_name"),
result.getString("last_name"));
}
}
} catch (SQLException e) {
logger.error(e);
}
return user;
}
@Override
public List<User> selectAllPageable(int pageNumber, int pageSize) {
List<User> users = new ArrayList<>();
String sql = "SELECT u.id, u.user_name, u.first_name, u.last_name " +
"FROM users u LIMIT ? OFFSET ?";
try (PreparedStatement statement = getConnection()
.prepareStatement(sql)) {
int offset = (pageNumber - 1) * pageSize;
statement.setInt(1, pageSize);
statement.setInt(2, offset);
try (ResultSet result = statement.executeQuery()) {
while (result.next()) {
users.add(
new User(
result.getLong("id"),
result.getString("user_name"),
result.getString("first_name"),
result.getString("last_name")));
}
}
} catch (SQLException e) {
logger.error(e);
}
return users;
}
}
5. JUnit 5 Test (UserDaoTest.java)
JUnit 5 test to verify database operations.
class UserDaoImplTest {
private UserDaoImpl userDao;
@BeforeAll
static void setUpBeforeClass() {
DatabaseTestUtils.runMigrations();
}
@BeforeEach
public void setUp() throws Exception {
userDao = new UserDaoImpl(DatabaseTestUtils.getConnection());
}
@Test
void findOneTest() {
User user = userDao.findOne(1l);
assertNotNull(user);
assert user.getId().equals(1l);
}
@Test
void selectAllPageableTest() {
List<User> userList = userDao.selectAllPageable(1, 4);
assert !userList.isEmpty();
assert userList.size() == 4;
}
@Test
void create() {
User user = new User();
user.setId(100l);
user.setUserName("This email address is being protected from spambots. You need JavaScript enabled to view it. ");
user.setUserRole(new UserRole(2l));
userDao.create(user);
User userCreated = userDao.findByUsername("This email address is being protected from spambots. You need JavaScript enabled to view it. ");
assertEquals(userCreated.getUserName(), "This email address is being protected from spambots. You need JavaScript enabled to view it. ");
}
@Test
void update() {
User user = userDao.findByUsername("This email address is being protected from spambots. You need JavaScript enabled to view it. ");
user.setUserName("This email address is being protected from spambots. You need JavaScript enabled to view it. ");
User updated = userDao.update(user);
assertEquals(updated.getUserName(), "This email address is being protected from spambots. You need JavaScript enabled to view it. ");
}
@Test
void delete() {
userDao.deleteByUserName("This email address is being protected from spambots. You need JavaScript enabled to view it. ");
assertNull(userDao.findByUsername("This email address is being protected from spambots. You need JavaScript enabled to view it. "));
}
@Test
void selectAllPaginated() {
PageRequest pageRequest = new PageRequestImpl(1, 5);
PageResponse<User> usersPage = userDao.selectAllPaginated(pageRequest);
assertFalse(usersPage.getContent().isEmpty());
assertTrue(0 < usersPage.getTotalElements());
}
}
Run the test
If you're using Maven, you can run:
mvn test
Or run UserDaoTest.java directly from your IDE.
Advantages of This Approach
- No Spring Boot – Pure JDBC approach.
- Fast and isolated – H2 in-memory DB cleans up after each test.
- Database version-controlled – Using Liquibase ensures schema consistency.
- Easy setup – Only requires JUnit, H2, and Liquibase.
This setup is clean, lightweight, and works well for unit tests that require database interaction.
Unit testing the JDBC layer can be challenging, but in-memory databases make it manageable. By following best practices, you can ensure your JDBC code is reliable, maintainable, and bug-free. Whether you choose to mock JDBC components or use an in-memory database, the key is to keep your tests isolated, fast, and meaningful.