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 
Spring Boot doesn’t require any special configuration for h2 database to work.

Create schema.sql

To generate a database schema at application start, the file schema.sql with the corresponding SQL commands for schema creation must be stored in the resources folder.
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);
Later, as the part of testing we will use this file for loading data.

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

Popular posts from this blog

Learn Java 8 streams with an example - print odd/even numbers from Array and List

Java Stream API - How to convert List of objects to another List of objects using Java streams?

Registration and Login with Spring Boot + Spring Security + Thymeleaf

Java, Spring Boot Mini Project - Library Management System - Download

ReactJS, Spring Boot JWT Authentication Example

Top 5 Java ORM tools - 2024

Java - Blowfish Encryption and decryption Example

Spring boot video streaming example-HTML5

Google Cloud Storage + Spring Boot - File Upload, Download, and Delete