網頁

2020/11/5

Spring Data JPA JPQL JOIN 簡單範例

Spring Data JPA 使用JPQL JOIN查詢簡單範例如下。

範例環境:

  • Spring Boot 2.3.2.RELEASE
  • Spring Data JPA 2.3.2.RELEASE
  • JPA 2.2
  • Hibernate 5.4
  • H2 Database
  • Lombok

若要使用JPQL JOIN查詢,則entity類必須設定關聯。

entity關係為一個Department對多個Employee設定如下。

Department

package com.abc.demo.entity;

import lombok.*;

import javax.persistence.*;
import java.io.Serializable;
import java.util.List;
import java.util.Objects;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
public class Department implements Serializable {
    private static final Long serialVersionUID = 1L;

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

    private String name;

    @OneToMany(mappedBy="department")
    private List<Employee> employeeList;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Department that = (Department) o;
        return Objects.equals(id, that.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

}

Employee

package com.abc.demo.entity;

import lombok.*;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Objects;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
public class Employee implements Serializable {
    private static final Long serialVersionUID = 1L;

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

    @ManyToOne
    @JoinColumn(name="DEPARTMENT_ID") // 外鍵欄位名稱
    private Department department;

    private String name;

    private Integer age;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Employee employee = (Employee) o;
        return Objects.equals(id, employee.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", department=" + department +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }

}

對映資料表現有資料如下。

-- EMPLOYEE
INSERT INTO DEPARTMENT (ID, NAME) VALUES (1, 'Marketing');
INSERT INTO DEPARTMENT (ID, NAME) VALUES (2, 'HR');

-- DEPARTMENT
INSERT INTO EMPLOYEE (ID, DEPARTMENT_ID, NAME, AGE) VALUES (1, 1, 'John', 22);
INSERT INTO EMPLOYEE (ID, DEPARTMENT_ID, NAME, AGE) VALUES (2, 1, 'Mary', 25);
INSERT INTO EMPLOYEE (ID, DEPARTMENT_ID, NAME, AGE) VALUES (3, 2, 'Andy', 33);

存取DepartmentDepartmentRepository中使用JPQL JOIN查詢。

DepartmentRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Department;
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.Optional;

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {

    @Query("SELECT d FROM Department d " +
            " JOIN d.employeeList " +
            " WHERE d.id = :id")
    Optional<Department> findByIdJoinEmployee(@Param("id") Long id);


    @Query("SELECT d FROM Department d " +
            " JOIN d.employeeList e " +
            " WHERE e.id = :id")
    Optional<Department> findByEmployeeIdJoinEmployee(@Param("id") long id);
}

存取EmployeeEmployeeRepository中使用JPQL JOIN查詢。

EmployeeRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
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;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query("SELECT e FROM Employee e " +
            " JOIN e.department d " +
            " WHERE d.id = :id")
    List<Employee> findByDepartmentIdJoinDepartment(@Param("id") long id);


    @Query("SELECT e FROM Employee e " +
            " LEFT JOIN e.department d " +
            " WHERE e.age < :age")
    List<Employee> findByAgeLessThanLeftJoinDepartment(@Param("age") int age);

}

測試。

DepartmentRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Department;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest
class DepartmentRepositoryTests {

    @Autowired
    private DepartmentRepository departmentRepository;

    @Transactional
    @Test
    void findByIdJoinEmployee_correct() {
        Department department = departmentRepository.findByIdJoinEmployee(1L)
                .orElse(null);

        Assertions.assertNotNull(department);
        Assertions.assertEquals(2, department.getEmployeeList().size());
    }

    @Transactional
    @Test
    void findByEmployeeIdJoinEmployee_correct() {
        long departmentId = departmentRepository.findByEmployeeIdJoinEmployee(3L)
                .map(Department::getId)
                .orElse(0L);

        Assertions.assertEquals(2L, departmentId);
    }
}

EmployeeRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@SpringBootTest
class EmployeeRepositoryTests {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Transactional
    @Test
    void findByDepartmentIdJoinDepartment_correct() {
        List<Employee> employeeList = employeeRepository.findByDepartmentIdJoinDepartment(1L);
        Assertions.assertEquals(2, employeeList.size());

        String departmentName = employeeList.stream().findFirst()
                .map(e -> e.getDepartment().getName())
                .orElse("");

        Assertions.assertEquals("Marketing", departmentName);
    }

    @Transactional
    @Test
    void findByAgeLessThanLeftJoinDepartment_correct() {
        List<Employee> employeeList = employeeRepository.findByAgeLessThanLeftJoinDepartment(30);
        Assertions.assertEquals(2, employeeList.size());
    }
}

參考github


沒有留言:

張貼留言