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);
存取Department的DepartmentRepository中使用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);
}
存取Employee的EmployeeRepository中使用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。
沒有留言:
張貼留言