Spring Boot - Testing JDBC components with @JdbcTest - Example
In this section, we will learn how to test JDBC-based components with @JdbcTest in Spring Boot application.
1. @JdbcTest
Instead of bootstrapping the entire application context for every test, @JdbcTest allows us to initialize the Spring application context with only those beans needed to test JDBC-based components. It will auto-configure DataSource, and JdbcTemplate.
If an embedded database is available on the classpath, @JdbcTest will autoconfigure one for testing purposes.
By default, tests annotated with @JdbcTest are transactional and roll back at the end of each test, means we do not need to clean up saved or modified table data after each test.
Regular @Component, @Repository, @Service or @Controller beans are not scanned when using this annotation.
This approach not only speeds up the testing process but also ensures a focused and efficient testing environment.
This approach is also known as "slicing" the application context.
Find the sample code snippet to use @JdbcTest annotation in unit test class.
@JdbcTest
public class StudentDAOTests{
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void findByName_ReturnsTheStudent() {
//todo...
}
}
When using JUnit 4, this annotation should be used in combination with @RunWith(SpringRunner.class). But for this example we are using JUnit 5, there’s no need to add the equivalent @ExtendWith(SpringExtension.class).
2. Transactions
By default, tests annotated with @JdbcTest are transactional and roll back at the end of each test. We can disable transaction management for a test, use Propagation.NOT_SUPPORTED.
@JdbcTest
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public class StudentDAOTests{
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void findByName_ReturnsTheStudent() {
//todo...
}
}
3. Test Against a Real Database
If you want run the tests against an application configured real database, use Replace.NONE.
@JdbcTest
@AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE)//Testing with real database
public class StudentDAOTests{
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void findByName_ReturnsTheStudent() {
//todo...
}
}
Complete example
Next we will create a spring boot JDBC application, create DAO layer which contains three methods and finally we will write test against H2 in-memory database.
4. Creating spring boot application
First, open the Spring initializr https://start.spring.io
Then, Provide the Group and Artifact name. We have provided Group name com.knf.dev.demo and Artifact jdbctest-example. Here I selected the Maven project - language Java 17 - Spring Boot 3.1.5 , JDBC API, and H2 Database.
Then, click on the Generate button. When we click on the Generate button, it starts packing the project in a .zip(jdbctest-example) file and downloads the project. Then, Extract the Zip file.
Then, import the project on your favourite IDE.
Final Project directory:
Complete pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.5</version>
<relativePath/> <!-- lookup parent from dao -->
</parent>
<groupId>com.knf.dev.demo</groupId>
<artifactId>jdbctest-example</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>jdbctest-example</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<image>
<builder>paketobuildpacks/builder-jammy-base:latest</builder>
</image>
</configuration>
</plugin>
</plugins>
</build>
</project>
spring-boot-starter-test starter will provide following libraries:
- JUnit
- Spring Test & Spring Boot Test
- AssertJ
- Hamcrest
- Mockito
- JSONassert
- JsonPath
Create schema.sql
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250) NOT NULL,
email VARCHAR(250) NOT NULL,
age INT
);
Create test-student-data.sql
INSERT INTO students (id,name, email, age) VALUES
(101,'Alpha', 'alpha@knf.com', 50),
(102,'Beta', 'beta@knf.com', 40),
(103,'Gama', 'gama@knf.com', 30),
(104,'Pekka', 'pekka@knf.com', 20);
Create Model - Student.java
package com.knf.dev.demo.model;
public class Student {
private Long id;
private String name;
private String email;
private Integer age;
public Student() {
}
public Student(Long id, String name, String email, Integer age) {
this.id = id;
this.name = name;
this.email = email;
this.age = age;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
Create StudentDAO.java
package com.knf.dev.demo.dao;
import com.knf.dev.demo.model.Student;
import java.util.List;
public interface StudentDAO {
List<Student> findByAgeGreaterThan(Integer age);
Student findByName(String name);
List<Student> findByAgeLessThan(Integer age);
}
- findByAgeGreaterThan(): If we want to retrieve students whose age is greater than the given age.
- findByName(): This method will get student entity by name.
- findByAgeLessThan(): If we want to retrieve students whose age is less than the given age.
Create StudentDAOImpl.java
package com.knf.dev.demo.dao;
import com.knf.dev.demo.model.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
//@Repository
public class StudentDAOImpl implements StudentDAO {
private final JdbcTemplate jdbcTemplate;
public StudentDAOImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public List<Student> findByAgeGreaterThan(Integer age) {
String sqlQuery = "select id, name, email, age from students where age > ?";
return jdbcTemplate.
query(sqlQuery, this::mapRowToUser,age);
}
@Override
public Student findByName(String name) {
String sqlQuery = "select id, name, email, age from students where name = ?";
return jdbcTemplate.
queryForObject(sqlQuery,
this::mapRowToUser, name);
}
@Override
public List<Student> findByAgeLessThan(Integer age) {
String sqlQuery = "select id, name, email, age from students where age < ?";
return jdbcTemplate.
query(sqlQuery, this::mapRowToUser,age);
}
private Student mapRowToUser(ResultSet resultSet, int rowNum)
throws SQLException {
Student student = new Student(resultSet.getLong("id"),
resultSet.getString("name"),
resultSet.getString("email"),
resultSet.getInt("age"));
return student;
}
}
JdbctestExampleApplication.java
package com.knf.dev.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JdbctestExampleApplication {
public static void main(String[] args) {
SpringApplication.run(JdbctestExampleApplication.class, args);
}
}
Write unit test against H2 in-memory database
When using JUnit 4, this annotation should be used in combination with @RunWith(SpringRunner.class). But for this example we are using JUnit 5, there’s no need to add the equivalent @ExtendWith(SpringExtension.class).
package com.knf.dev.demo;
import com.knf.dev.demo.model.Student;
import com.knf.dev.demo.dao.StudentDAO;
import com.knf.dev.demo.dao.StudentDAOImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.jdbc.Sql;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import static org.assertj.core.api.AssertionsForInterfaceTypes.assertThat;
@JdbcTest
public class StudentDAOTests {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
@Sql({"/test-student-data.sql"})
void findByName_ReturnsTheStudent() {
StudentDAO studentDAO = new StudentDAOImpl(jdbcTemplate);
Student student = studentDAO.findByName("Alpha");
assertThat(student).isNotNull();
assertThat(student.getEmail()).isEqualTo("alpha@knf.com");
assertThat(student.getName()).isEqualTo("Alpha");
assertThat(student.getId()).isEqualTo(101);
assertThat(student.getAge()).isEqualTo(50);
}
@Test
@Sql({"/test-student-data.sql"})
void findByAgeGreaterThan_ReturnsTheListStudents() {
StudentDAO studentDAO = new StudentDAOImpl(jdbcTemplate);
List<Student> students = studentDAO.findByAgeGreaterThan(29);
//Convert list of students to list of id(Integer)
List<Integer> ids = students.stream()
.map(o -> o.getId().intValue())
.collect(Collectors.toList());
assertThat(students.size()).isEqualTo(3);
assertThat(ids).hasSameElementsAs(Arrays.asList(103,102,101));
}
@Test
@Sql({"/test-student-data.sql"})
void findByAgeLessThan_ReturnsTheListStudents() {
StudentDAO studentDAO = new StudentDAOImpl(jdbcTemplate);
List<Student> students = studentDAO.findByAgeLessThan(31);
//Convert list of students to list of id(Integer)
List<Integer> ids = students.stream()
.map(o -> o.getId().intValue())
.collect(Collectors.toList());
assertThat(students.size()).isEqualTo(2);
assertThat(ids).hasSameElementsAs(Arrays.asList(104,103));
}
}
- The @Sql annotation executes SQL scripts and SQL statements using datasource for testing.
- assertThat is used to check the specified value matches the expected value. It will accept the two parameters, the first contains the actual value, and the second will have the object matching the condition.
5. Run the test
Or you can run the test using following command:
mvn test -Dtest=StudentDAOTests
