Repository Testing

By / 21 October 2026 / 6 minutes of reading
  1. Repository testing checks that JPA repository methods and queries work correctly against a real database.
  2. It is used to catch wrong derived-method names, wrong @Query JPQL and constraint problems early, before the service or controller layers run.
  3. @DataJpaTest loads only the JPA layer (entities and repositories), so the test starts quickly.
  4. Tests run against a separate MySQL test database (studentdb_test), so your real data is never touched.

Features

  • Tests only the persistence layer, with no controller or service loaded.
  • Runs against a real MySQL test database instead of a fake one.
  • Rolls back every test automatically, so tests stay independent.
  • Verifies derived queries such as findByEmail and findByDepartment.
  • Verifies custom @Query JPQL such as sorting and filtering.
  • Verifies database constraints such as a unique email.

Hands-on Experiment: Creating a spring boot application to understand how JPA repository queries can be tested against a controlled test database.

The following steps are involved in developing this hands-on experiment.

Step 1: Generate the Project

Step 2: Project Structure

Step 3: pom.xml

Step 4: application.properties

Step 5: Create Entity Class

Step 6: Create Repository Interface

Step 7: Create Service Class

Step 8: Create Controller Class

Step 9: Main Application Class

Step 10: Run the Main Application Class

Step 1: Configure the Project on Spring Initializr

Go to start.spring.io and set:

  • Project: Maven
  • Language: Java
  • Spring Boot version: 3.2.x
  • Group: com.example
  • Artifact: repotestdemo
  • Name: repotestdemo
  • Package name: com.example.repotestdemo
  • Packaging: Jar
  • Java: 17
  • Dependencies: Spring Web, Spring Data JPA, MySQL Driver.

Click Generate to download the ZIP, then extract and import it into your IDE as a Maven project.

Step 2: Generated Project Structure

Step 3: Explore 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.2.5</version>
        <relativePath/>
    </parent>

    <groupId>com.example</groupId>
    <artifactId>student-repotest-demo</artifactId>
    <version>1.0.0</version>
    <name>student-repotest-demo</name>
    <description>Testing Student repository operations against a MySQL test database</description>

    <properties>
        <java.version>17</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</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>
            </plugin>
        </plugins>
    </build>
</project>

Step 4: application.properties

spring.application.name=student-repotest-demo
server.port=8082

# Main (real) database - created automatically if it does not exist. Change username/password as needed.
spring.datasource.url=jdbc:mysql://localhost:3306/studentdb?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=password

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=trueCode language: PHP (php)

Step 5: Create Entity Class

package com.example.repotestdemo.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "students")
public class Student {

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

    private String name;

    @Column(unique = true)
    private String email;

    private String department;

    public Student() {
    }

    public Student(String name, String email, String department) {
        this.name = name;
        this.email = email;
        this.department = department;
    }

    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 String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }
}

Step 6: Create below Repository Interface

package com.example.repotestdemo.repository;

import com.example.repotestdemo.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

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

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {

    // Derived queries
    Optional<Student> findByEmail(String email);

    boolean existsByEmail(String email);

    List<Student> findByDepartment(String department);

    List<Student> findByNameContainingIgnoreCase(String keyword);

    // Custom JPQL query
    @Query("SELECT s FROM Student s WHERE s.department = :dept ORDER BY s.name ASC")
    List<Student> findByDepartmentSortedByName(@Param("dept") String department);
}

Step 7: Create below Service class

package com.example.repotestdemo.service;

import com.example.repotestdemo.entity.Student;
import com.example.repotestdemo.repository.StudentRepository;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import org.springframework.web.server.ResponseStatusException;

import java.util.List;

@Service
public class StudentService {

    private final StudentRepository studentRepository;

    public StudentService(StudentRepository studentRepository) {
        this.studentRepository = studentRepository;
    }

    public Student addStudent(Student student) {
        if (studentRepository.existsByEmail(student.getEmail())) {
            throw new ResponseStatusException(HttpStatus.CONFLICT,
                    "Email already registered: " + student.getEmail());
        }
        return studentRepository.save(student);
    }

    public List<Student> getAllStudents() {
        return studentRepository.findAll();
    }

    public Student getStudentById(Long id) {
        return studentRepository.findById(id)
                .orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND,
                        "Student not found with id: " + id));
    }

    public List<Student> getByDepartment(String department) {
        return studentRepository.findByDepartmentSortedByName(department);
    }

    public List<Student> searchByName(String keyword) {
        return studentRepository.findByNameContainingIgnoreCase(keyword);
    }

    public void deleteStudent(Long id) {
        if (!studentRepository.existsById(id)) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND,
                    "Student not found with id: " + id);
        }
        studentRepository.deleteById(id);
    }
}

Step 8: Create Controller class

package com.example.repotestdemo.controller;

import com.example.repotestdemo.entity.Student;
import com.example.repotestdemo.service.StudentService;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/students")
public class StudentController {

    private final StudentService studentService;

    public StudentController(StudentService studentService) {
        this.studentService = studentService;
    }

    @PostMapping
    @ResponseStatus(HttpStatus.CREATED)
    public Student addStudent(@RequestBody Student student) {
        return studentService.addStudent(student);
    }

    @GetMapping
    public List<Student> getAllStudents() {
        return studentService.getAllStudents();
    }

    @GetMapping("/{id}")
    public Student getStudentById(@PathVariable Long id) {
        return studentService.getStudentById(id);
    }

    @GetMapping("/department/{department}")
    public List<Student> getByDepartment(@PathVariable String department) {
        return studentService.getByDepartment(department);
    }

    @GetMapping("/search")
    public List<Student> search(@RequestParam String name) {
        return studentService.searchByName(name);
    }

    @DeleteMapping("/{id}")
    public String deleteStudent(@PathVariable Long id) {
        studentService.deleteStudent(id);
        return "Student deleted with id: " + id;
    }
}

Step 9: Main Application

package com.example.repotestdemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class RepoTestDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(RepoTestDemoApplication.class, args);
    }
}

package com.example.repotestdemo.repository;

import com.example.repotestdemo.entity.Student;
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.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.test.context.ActiveProfiles;

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

import static org.junit.jupiter.api.Assertions.*;

// Loads only JPA components (entities + repositories) - no controller, no service.
// replace = NONE  -> use the real MySQL test database instead of an embedded one.
// "test" profile  -> loads src/test/resources/application-test.properties (studentdb_test).
// Each test runs in a transaction that is rolled back automatically, so tests stay independent.
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ActiveProfiles("test")
class StudentRepositoryTest {

    @Autowired
    private StudentRepository studentRepository;

    @BeforeEach
    void setUp() {
        studentRepository.deleteAll();
        studentRepository.save(new Student("Ravi Kumar", "ravi@mail.com", "CSE"));
        studentRepository.save(new Student("Anil Sharma", "anil@mail.com", "CSE"));
        studentRepository.save(new Student("Sita Devi", "sita@mail.com", "ECE"));
    }

    @Test
    @DisplayName("save() stores a student and generates an id")
    void save() {
        Student saved = studentRepository.save(new Student("Mohan", "mohan@mail.com", "IT"));

        assertNotNull(saved.getId());
        assertEquals(4, studentRepository.count());
    }

    @Test
    @DisplayName("findById() returns the saved student")
    void findById() {
        Student saved = studentRepository.save(new Student("Mohan", "mohan@mail.com", "IT"));

        Optional<Student> found = studentRepository.findById(saved.getId());

        assertTrue(found.isPresent());
        assertEquals("Mohan", found.get().getName());
    }

    @Test
    @DisplayName("findByEmail() returns the matching student")
    void findByEmail() {
        Optional<Student> found = studentRepository.findByEmail("sita@mail.com");

        assertTrue(found.isPresent());
        assertEquals("Sita Devi", found.get().getName());
    }

    @Test
    @DisplayName("findByEmail() returns empty for an unknown email")
    void findByEmail_notFound() {
        assertTrue(studentRepository.findByEmail("nobody@mail.com").isEmpty());
    }

    @Test
    @DisplayName("existsByEmail() is true for an existing email and false otherwise")
    void existsByEmail() {
        assertTrue(studentRepository.existsByEmail("ravi@mail.com"));
        assertFalse(studentRepository.existsByEmail("nobody@mail.com"));
    }

    @Test
    @DisplayName("findByDepartment() returns only students of that department")
    void findByDepartment() {
        List<Student> cse = studentRepository.findByDepartment("CSE");

        assertEquals(2, cse.size());
        assertTrue(cse.stream().allMatch(s -> s.getDepartment().equals("CSE")));
    }

    @Test
    @DisplayName("findByNameContainingIgnoreCase() searches by part of the name")
    void findByNameContainingIgnoreCase() {
        List<Student> result = studentRepository.findByNameContainingIgnoreCase("KUMAR");

        assertEquals(1, result.size());
        assertEquals("Ravi Kumar", result.get(0).getName());
    }

    @Test
    @DisplayName("custom @Query returns department students sorted by name")
    void findByDepartmentSortedByName() {
        List<Student> result = studentRepository.findByDepartmentSortedByName("CSE");

        assertEquals(2, result.size());
        assertEquals("Anil Sharma", result.get(0).getName());
        assertEquals("Ravi Kumar", result.get(1).getName());
    }

    @Test
    @DisplayName("deleteById() removes the student")
    void deleteById() {
        Student ravi = studentRepository.findByEmail("ravi@mail.com").orElseThrow();

        studentRepository.deleteById(ravi.getId());

        assertFalse(studentRepository.existsById(ravi.getId()));
        assertEquals(2, studentRepository.count());
    }

    @Test
    @DisplayName("saving a duplicate email violates the unique constraint")
    void duplicateEmail() {
        assertThrows(DataIntegrityViolationException.class, () ->
                studentRepository.saveAndFlush(new Student("Another", "ravi@mail.com", "IT")));
    }
}

Step 10: Run the StudentRepoTestApplication

PREVIOUS
NEXT

Related Posts

Scroll to Top