Integration Testing Spring Boot Repository Layer

Integration testing is important for ensuring that the components of your application work together as expected. In this guide, we will focus on integration testing the repository layer of a Spring Boot application using a MySQL database. We’ll use JUnit 5 for writing tests, Spring Data JPA for managing database interactions, and MySQL as the database for real integration testing.

What is Integration Testing?

Integration testing involves testing multiple components of an application to ensure that they work together correctly. In the context of a Spring Boot application, integration testing often includes verifying the interaction between the repository layer and the actual database. This ensures that the application correctly handles data persistence, retrieval, and manipulation.

Why Use MySQL for Integration Testing?

While unit tests often use in-memory databases like H2 for faster execution, integration testing benefits from using the actual database used in production, such as MySQL. This helps in:

• Simulating real-world scenarios: Ensuring the queries, configurations, and behavior align with the production environment.
• Testing schema compatibility: Verifying that schema generation and migrations work as expected in MySQL.
• Catching environment-specific issues: Identifying problems that might arise from database-specific behaviors, like indexing and performance.

Step 1: Add Maven Dependencies

To set up Spring Data JPA with MySQL and Lombok for reducing boilerplate code, add the following dependencies to your pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.0.0</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

• spring-boot-starter-data-jpa: Provides Spring Data JPA and Hibernate for database operations.
• mysql-connector-j: The MySQL JDBC driver is required to connect the application to the MySQL database.
• lombok: Reduces boilerplate code by auto-generating setters, getters, and constructors.
• spring-boot-starter-test: Provides JUnit and other testing tools.

Step 2: Project Structure

The following diagram shows the structure of your project:

src/main/java
│
├── net
│   └── javaguides
│       └── springboot
│           ├── model
│           │   └── Employee.java
│           └── repository
│               └── EmployeeRepository.java
│
src/test/java
│
├── net
│   └── javaguides
│       └── springboot
│           └── repository
│               └── EmployeeRepositoryTests.java

Step 3: Configure MySQL Database

You need to configure the application.properties file to connect to a MySQL database. Add the following properties:

spring.jpa.show-sql=true

spring.datasource.url=jdbc:mysql://localhost:3306/ems
spring.datasource.username=root
spring.datasource.password=Mysql@123

spring.jpa.hibernate.ddl-auto=update

• spring.jpa.show-sql=true: Displays the SQL queries being executed.
• spring.datasource.url: The URL of the MySQL database.
• spring.datasource.username and spring.datasource.password: Credentials to access the MySQL database.
• spring.jpa.hibernate.ddl-auto=update: Automatically updates the database schema based on entity definitions.

Step 4: Create the Employee Entity

We’ll create the Employee entity that maps to the employees table in the MySQL database:

import lombok.*;

import jakarta.persistence.*;

@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Builder

@Entity
@Table(name = "employees")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "first_name", nullable = false)
    private String firstName;

    @Column(name = "last_name", nullable = false)
    private String lastName;

    @Column(nullable = false)
    private String email;
}

Explanation:

• @Entity: Marks the class as a JPA entity that maps to the employees table.
• @Table(name = "employees"): Specifies the table name in the database.
• Lombok Annotations: @Setter, @Getter, @AllArgsConstructor, @NoArgsConstructor, and @Builder automatically generate code like setters, getters, constructors, and a builder pattern.

Step 5: Create the Repository Layer

The repository interface EmployeeRepository will extend JpaRepository to provide basic CRUD operations. We will also define custom queries using JPQL and native SQL.

import net.javaguides.springboot.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Optional;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    Optional<Employee> findByEmail(String email);

    // Custom JPQL query with index parameters
    @Query("select e from Employee e where e.firstName = ?1 and e.lastName = ?2")
    Employee findByJPQL(String firstName, String lastName);

    // Custom JPQL query with named parameters
    @Query("select e from Employee e where e.firstName =:firstName and e.lastName =:lastName")
    Employee findByJPQLNamedParams(@Param("firstName") String firstName, @Param("lastName") String lastName);

    // Custom native SQL query with index parameters
    @Query(value = "select * from employees e where e.first_name =?1 and e.last_name =?2", nativeQuery = true)
    Employee findByNativeSQL(String firstName, String lastName);

    // Custom native SQL query with named parameters
    @Query(value = "select * from employees e where e.first_name =:firstName and e.last_name =:lastName", nativeQuery = true)
    Employee findByNativeSQLNamed(@Param("firstName") String firstName, @Param("lastName") String lastName);
}

Explanation of Repository Methods:

• findByEmail(String email): Finds an employee by their email address.
• findByJPQL(String firstName, String lastName): A custom JPQL query using indexed parameters to find an employee based on first and last name.
• findByJPQLNamedParams(String firstName, String lastName): A custom JPQL query using named parameters for readability.
• findByNativeSQL(String firstName, String lastName): A custom native SQL query using indexed parameters.
• findByNativeSQLNamed(String firstName, String lastName): A custom native SQL query using named parameters.

Step 6: Testing the Repository Layer - EmployeeRepositoryTests

Now that we have set up the repository and entity, we can move on to writing integration tests for the repository layer. The EmployeeRepositoryTests class will test various repository methods, including custom queries.

package net.javaguides.springboot.repository;

import net.javaguides.springboot.model.Employee;
import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;

import java.util.List;
import java.util.Optional;

@DataJpaTest
public class EmployeeRepositoryTests {

    @Autowired
    private EmployeeRepository employeeRepository;

    private Employee employee;

    @BeforeEach
    public void setup(){
        employee = Employee.builder()
                .firstName("Ramesh")
                .lastName("Fadatare")
                .email("ramesh@gmail.com")
                .build();
    }

    // JUnit test for save employee operation
    @Test
    public void givenEmployeeObject_whenSave_thenReturnSavedEmployee(){
        // given - precondition or setup
        Employee employee = Employee.builder()
                .firstName("Ramesh")
                .lastName("Ramesh")
                .email("ramesh@gmail.com")
                .build();
        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.save(employee);

        // then - verify the output
        assertThat(savedEmployee).isNotNull();
        assertThat(savedEmployee.getId()).isGreaterThan(0);
    }

    // JUnit test for get all employees operation
    @DisplayName("JUnit test for get all employees operation")
    @Test
    public void givenEmployeesList_whenFindAll_thenEmployeesList(){
        // given - precondition or setup
        Employee employee1 = Employee.builder()
                .firstName("John")
                .lastName("Cena")
                .email("cena@gmail.com")
                .build();

        employeeRepository.save(employee);
        employeeRepository.save(employee1);

        // when - action or the behaviour that we are going test
        List<Employee> employeeList = employeeRepository.findAll();

        // then - verify the output
        assertThat(employeeList).isNotNull();
        assertThat(employeeList.size()).isEqualTo(2);
    }

    // JUnit test for get employee by id operation
    @DisplayName("JUnit test for get employee by id operation")
    @Test
    public void givenEmployeeObject_whenFindById_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        Employee employeeDB = employeeRepository.findById(employee.getId()).get();

        // then - verify the output
        assertThat(employeeDB).isNotNull();
    }

    // JUnit test for get employee by email operation
    @DisplayName("JUnit test for get employee by email operation")
    @Test
    public void givenEmployeeEmail_whenFindByEmail_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        Employee employeeDB = employeeRepository.findByEmail(employee.getEmail()).get();

        // then - verify the output
        assertThat(employeeDB).isNotNull();
    }

    // JUnit test for update employee operation
    @DisplayName("JUnit test for update employee operation")
    @Test
    public void givenEmployeeObject_whenUpdateEmployee_thenReturnUpdatedEmployee(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.findById(employee.getId()).get();
        savedEmployee.setEmail("ram@gmail.com");
        savedEmployee.setFirstName("Ram");
        Employee updatedEmployee = employeeRepository.save(savedEmployee);

        // then - verify the output
        assertThat(updatedEmployee.getEmail()).isEqualTo("ram@gmail.com");
        assertThat(updatedEmployee.getFirstName()).isEqualTo("Ram");
    }

    // JUnit test for delete employee operation
    @DisplayName("JUnit test for delete employee operation")
    @Test
    public void givenEmployeeObject_whenDelete_thenRemoveEmployee(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        employeeRepository.deleteById(employee.getId());
        Optional<Employee> employeeOptional = employeeRepository.findById(employee.getId());

        // then - verify the output
        assertThat(employeeOptional).isEmpty();
    }

    // JUnit test for custom query using JPQL with index
    @DisplayName("JUnit test for custom query using JPQL with index")
    @Test
    public void givenFirstNameAndLastName_whenFindByJPQL_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);
        String firstName = "Ramesh";
        String lastName = "Fadatare";

        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.findByJPQL(firstName, lastName);

        // then - verify the output
        assertThat(savedEmployee).isNotNull();
    }

    // JUnit test for custom query using JPQL with Named params
    @DisplayName("JUnit test for custom query using JPQL with Named params")
    @Test
    public void givenFirstNameAndLastName_whenFindByJPQLNamedParams_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);
        String firstName = "Ramesh";
        String lastName = "Fadatare";

        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.findByJPQLNamedParams(firstName, lastName);

        // then - verify the output
        assertThat(savedEmployee).isNotNull();
    }

    // JUnit test for custom query using native SQL with index
    @DisplayName("JUnit test for custom query using native SQL with index")
    @Test
    public void givenFirstNameAndLastName_whenFindByNativeSQL_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.findByNativeSQL(employee.getFirstName(), employee.getLastName());

        // then - verify the output
        assertThat(savedEmployee).isNotNull();
    }

    // JUnit test for custom query using native SQL with named params
    @DisplayName("JUnit test for custom query using native SQL with named params")
    @Test
    public void givenFirstNameAndLastName_whenFindByNativeSQLNamedParams_thenReturnEmployeeObject(){
        // given - precondition or setup
        employeeRepository.save(employee);

        // when - action or the behaviour that we are going test
        Employee savedEmployee = employeeRepository.findByNativeSQLNamed(employee.getFirstName(), employee.getLastName());

        // then - verify the output
        assertThat(savedEmployee).isNotNull();
    }
}

Explanation of JUnit Test Methods:

• givenEmployeeObject_whenSave_thenReturnSavedEmployee(): Tests if an Employee object is saved correctly using the save() method.
• givenEmployeesList_whenFindAll_thenEmployeesList(): Verifies that the findAll() method returns a list of employees.
• givenEmployeeObject_whenFindById_thenReturnEmployeeObject(): Tests if the repository can retrieve an employee by id using findById().
• givenEmployeeEmail_whenFindByEmail_thenReturnEmployeeObject(): Verifies that the repository can retrieve an employee by email using findByEmail().
• givenEmployeeObject_whenUpdateEmployee_thenReturnUpdatedEmployee(): Tests if an Employee object is updated correctly.
• givenEmployeeObject_whenDelete_thenRemoveEmployee(): Tests if an Employee object is deleted from the database.
• Custom Query Tests (findByJPQL(), findByJPQLNamedParams(), etc.): These tests validate that custom JPQL and native SQL queries return the correct employee records.

Conclusion

In this tutorial, we explored how to write integration tests for the repository layer in a Spring Boot application using JUnit 5 and MySQL. We covered basic CRUD operations and custom queries and ensured the repository interacts correctly with the MySQL database.