Joining Tables With Spring Data JPA Specifications

Spring Data JPA Specifications provide a powerful way to dynamically build queries based on various criteria. They are particularly useful for creating complex queries involving joins between multiple tables. In this tutorial, we will demonstrate how to use Spring Data JPA Specifications to join tables using a Student and Course entity as an example.

Prerequisites

• JDK 17 or later
• Maven or Gradle
• IDE (IntelliJ IDEA, Eclipse, etc.)

Step 1: Set Up a Spring Boot Project

1.1 Create a New Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

• Spring Web
• Spring Data JPA
• H2 Database

Download and unzip the project, then open it in your IDE.

1.2 Configure application.properties

Set up the application properties for your project to connect to the H2 database. This file is located in the src/main/resources directory.

# src/main/resources/application.properties

# H2 Database configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.datasource.platform=h2

# JPA configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

# H2 Console configuration
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

Explanation:

• Configures the connection to the H2 in-memory database.
• Enables SQL logging.
• Sets up JPA to update the database schema automatically.
• Enables the H2 console for easy database management.

Step 2: Define Entity Classes

2.1 Create the Student Entity

Create an entity class to represent a student in the database.

package com.example.demo.entity;

import jakarta.persistence.*;
import java.util.Set;

@Entity
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    private int age;

    @ManyToMany
    @JoinTable(
      name = "student_course",
      joinColumns = @JoinColumn(name = "student_id"),
      inverseJoinColumns = @JoinColumn(name = "course_id"))
    private Set<Course> courses;

    // Getters and setters
    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Set<Course> getCourses() {
        return courses;
    }

    public void setCourses(Set<Course> courses) {
        this.courses = courses;
    }
}

2.2 Create the Course Entity

Create an entity class to represent a course in the database.

package com.example.demo.entity;

import jakarta.persistence.*;
import java.util.Set;

@Entity
public class Course {

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

    @ManyToMany(mappedBy = "courses")
    private Set<Student> students;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Set<Student> getStudents() {
        return students;
    }

    public void setStudents(Set<Student> students) {
        this.students = students;
    }
}

Explanation:

• @Entity: Specifies that the class is an entity and is mapped to a database table.
• @ManyToMany: Defines a many-to-many relationship between Student and Course entities.
• @JoinTable: Specifies the join table for the many-to-many relationship.

Step 3: Create the Repository Interfaces

3.1 Create the StudentRepository

Create a repository interface to perform CRUD operations on the Student entity.

package com.example.demo.repository;

import com.example.demo.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

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

3.2 Create the CourseRepository

Create a repository interface to perform CRUD operations on the Course entity.

package com.example.demo.repository;

import com.example.demo.entity.Course;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
}

Explanation:

• JpaRepository<Student, Long>: Provides CRUD operations for the Student entity.
• JpaSpecificationExecutor<Student>: Provides support for dynamic queries using specifications.
• JpaRepository<Course, Long>: Provides CRUD operations for the Course entity.

Step 4: Create Specifications

4.1 Create the StudentSpecifications

Create a class to define specifications for querying the Student entity.

package com.example.demo.specification;

import com.example.demo.entity.Course;
import com.example.demo.entity.Student;
import org.springframework.data.jpa.domain.Specification;

import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.JoinType;

public class StudentSpecifications {

    public static Specification<Student> hasCourseWithTitle(String courseTitle) {
        return (root, query, criteriaBuilder) -> {
            Join<Student, Course> courses = root.join("courses", JoinType.INNER);
            return criteriaBuilder.equal(courses.get("title"), courseTitle);
        };
    }

    public static Specification<Student> hasAgeGreaterThan(int age) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.greaterThan(root.get("age"), age);
    }
}

Explanation:

• hasCourseWithTitle: Defines a specification to find students enrolled in a course with the specified title.
• hasAgeGreaterThan: Defines a specification to find students older than the specified age.

Step 5: Create Service and Controller Layers

5.1 Create the StudentService

Create a service class to handle business logic related to students.

package com.example.demo.service;

import com.example.demo.entity.Student;
import com.example.demo.repository.StudentRepository;
import com.example.demo.specification.StudentSpecifications;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

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

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

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

    public Optional<Student> getStudentById(Long id) {
        return studentRepository.findById(id);
    }

    public Student createStudent(Student student) {
        return studentRepository.save(student);
    }

    public void deleteStudent(Long id) {
        studentRepository.deleteById(id);
    }

    public List<Student> getStudentsByCourseTitle(String courseTitle) {
        Specification<Student> spec = StudentSpecifications.hasCourseWithTitle(courseTitle);
        return studentRepository.findAll(spec);
    }

    public List<Student> getStudentsByCourseTitleAndAge(String courseTitle, int age) {
        Specification<Student> spec = Specification.where(StudentSpecifications.hasCourseWithTitle(courseTitle))
                                                   .and(StudentSpecifications.hasAgeGreaterThan(age));
        return studentRepository.findAll(spec);
    }
}

Explanation:

• getStudentsByCourseTitle: Retrieves students enrolled in a course with the specified title using a specification.
• getStudentsByCourseTitleAndAge: Retrieves students enrolled in a course with the specified title and older than a specified age using combined specifications.

5.2 Create the StudentController

Create a REST controller to expose endpoints for interacting with students.

package com.example.demo.controller;

import com.example.demo.entity.Student;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

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

    @Autowired
    private StudentService studentService;

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

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

    @PostMapping
    public Student createStudent(@RequestBody Student student) {
        return studentService.createStudent(student);
    }

    @DeleteMapping("/{id}")
    public void deleteStudent(@PathVariable Long id) {
        studentService.deleteStudent(id);
    }

    @GetMapping("/by-course")
    public List<Student> getStudentsByCourseTitle(@RequestParam String courseTitle) {
        return studentService.getStudentsByCourseTitle(courseTitle);
    }

    @GetMapping("/by-course-and-age")
    public List<Student> getStudentsByCourseTitleAndAge(@Request

Param String courseTitle, @RequestParam int age) {
        return studentService.getStudentsByCourseTitleAndAge(courseTitle, age);
    }
}

Explanation:

• @GetMapping("/by-course"): Maps the endpoint to retrieve students by course title.
• @GetMapping("/by-course-and-age"): Maps the endpoint to retrieve students by course title and age.

Step 6: Running and Testing the Application

6.1 Run the Application

Run the Spring Boot application using your IDE or the command line:

./mvnw spring-boot:run

6.2 Access the H2 Console

You can access the H2 console to inspect the database contents at http://localhost:8080/h2-console. Use the following JDBC URL to connect:

• JDBC URL: jdbc:h2:mem:testdb
• Username: sa
• Password: password

6.3 Test the Endpoints

Use a tool like Postman or your browser to test the endpoints.

Create a Student

• URL: http://localhost:8080/students
• Method: POST
• Body:

  {
      "name": "John Doe",
      "email": "john.doe@example.com",
      "age": 20,
      "courses": [{"id": 1, "title": "Math"}]
  }
  

Get All Students

• URL: http://localhost:8080/students
• Method: GET

Get a Student by ID

• URL: http://localhost:8080/students/{id}
• Method: GET

Delete a Student

• URL: http://localhost:8080/students/{id}
• Method: DELETE

Get Students by Course Title

• URL: http://localhost:8080/students/by-course?courseTitle=Math
• Method: GET

Get Students by Course Title and Age

• URL: http://localhost:8080/students/by-course-and-age?courseTitle=Math&age=18
• Method: GET

Conclusion

In this tutorial, you have learned how to use Spring Data JPA Specifications to join tables and build dynamic queries in a Spring Boot 3.2 application. We covered:

• Setting up a Spring Boot project with Spring Data JPA and H2.
• Defining entity classes and relationships.
• Creating repository interfaces with specifications.
• Implementing specifications for dynamic queries.
• Creating service and controller layers.
• Running and testing the application using REST endpoints.

By following these steps, you can effectively manage and query related entities in your Spring Boot applications using Spring Data JPA Specifications.