Spring/JPA/Hibernate如何在一个存储库中执行两个实体的联接 [英] Spring/JPA/Hibernate How to Perform Join of Two Entities In One Repository
问题描述
首先，我们有两个表.一个表是 Employee 表，其中包含以下列:
First let's say we have two tables. One table is an Employee table with the following columns:
EMPLOYEE:
------------------------
emp_id (int, primary key)
emp_name (varchar(125))
emp_dept (foreign key)
emp_intro (text)
另一个表是具有以下列的部门表:
The other table is a Department table with the following columns:
DEPARTMENT:
-----------
dept_id (int, primary key)
dept_label (varchar(25))
这里是表格值的示例
DEPARTMENT:
------------------------
dept_id | dept_label
------------------------
1 | Sales
------------------------
2 | Technology
------------------------
3 | Finance
为了返回带有状态标签的员工信息，我们需要执行JOIN:
In order to return the employee's info with a status label, we need to either perform a JOIN:
SELECT e, d.dept_label FROM employees JOIN department d ON d.dept_id = e.emp_dept
或多表选择:
SELECT e.emp_id, e.emp_name, d.dept_label, e.emp_intro FROM employees e, department d WHERE e.emp_dept = d.dept_id
但是，在使用JPA/Hibernate时，我们需要创建两个类:
However, when using JPA/Hibernate, we need to create two classes:
Employee.java
package com.example.entities;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "employees")
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "emp_id")
private long emp_id;
@Column(name = "emp_name")
private String emp_name;
@Column(name = "emp_dept")
private Integer emp_dept;
@Column(name = "emp_intro")
private String emp_intro;
public long getEmp_id() {
return emp_id;
}
public void setEmp_id(long emp_id) {
this.emp_id = emp_id;
}
public String getEmp_name() {
return emp_name;
}
public void setEmp_name(String emp_name) {
this.emp_name = emp_name;
}
public Integer getEmp_dept() {
return emp_dept;
}
public void setEmp_dept(Integer emp_dept) {
this.emp_dept = emp_dept;
}
public String getEmp_intro() {
return emp_intro;
}
public void setEmp_intro(String emp_intro) {
this.emp_intro = emp_intro;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
}
Department.java
package com.example.entities;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "departments")
public class Department implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "dept_id")
private long dept_id;
@Column(name = "dept_label")
private String dept_label;
public long getDept_id() {
return dept_id;
}
public void setDept_id(long dept_id) {
this.dept_id = dept_id;
}
public String getDept_label() {
return dept_label;
}
public void setDept_label(String dept_label) {
this.dept_label = dept_label;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
}
然后是存储库(DAO):
Then, there is the repository (DAO):
EmployeeRepository
package com.example.repository;
import.java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.example.entities.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
@Query("select e, d.dept_label FROM Employee e JOIN Department d ON "
+ "d.dept_id = e.emp_id")
public List<Employee> return getEmployees();
}
最后是将分类查询绑定到应用程序端点的Java控制器:
and lastly, the Java controller that binds the classed query to an endpoint of the application:
EmployeeController.java
package com.example.controllers;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.example.entities.Department;
import com.example.entities.Employee;
import com.example.repository.EmployeeRepository;
@Controller
public class EmployeeController {
@Autowired
EmployeeRepository er;
@RequestMapping(value = "/getEmployees")
public @ResponseBody List<Employee> getEmployees() {
return er.getEmployees();
}
}
我已经测试了整个结构，只检索了Employee表中的行(即@Query("SELECT e FROM Employee e"))，一切按原样返回.
I have already tested this entire structure with only retrieving rows inside of the Employee table (i.e. @Query("SELECT e FROM Employee e") ) and everything returns as is.
我的主要问题是什么，当我需要查询部门内部的特定类(表)(员工)时，该查询如何返回JOIN QUERY?
MY MAIN ISSUE is how does one return a JOIN QUERY while the query is inside of a specific class (table), being Employee, if I require contents inside of Department?
我已经尝试过@JoinColumn注解，但效果也不佳(也许我做错了).
I've already tried @JoinColumn annotations and that didn't work as well (perhaps I did it wrong).
有什么想法吗?谢谢.
推荐答案
您不必使用原始联接来执行此操作，只需使用适当的关系映射即可. Employee和Departament之间的关系听起来像@ManyToOne或@ManyToMany.
您将可以例如employee.getDepartament()或通过employee.departament.name=:name
You dont have to use raw joins to do that, just use proper relation mapping. Relation between Employee and Departament sounds like @ManyToOne or @ManyToMany.
You will be able to eg employee.getDepartament() or query by employee.departament.name=:name
http://www.objectdb.com/api/java/jpa/ManyToMany
您甚至可以映射双向关系，这样您就可以从员工以及从给定的部门的所有员工中脱颖而出
You can even map bidirectional relations so you will be able to get deparament from employee, as well as all employees from given deparaments
PS. @JoinColumn用于删除用于连接的DB columnt，它不同于由选定的命名策略(通常为entityname_id)创建的.实际的关系映射是通过声明@OneToOne @OneToMany @ManyToMany来完成的，那些可以但不必与@JoinColumn一起使用.这是严格的JPA问题.
PS. @JoinColumn is used to delare DB columnt used for joins it it is different then created by selected named strategies (usualy entityname_id). Actual relation mapping is done by declaring @OneToOne @OneToMany @ManyToMany and those can but doesn't have to be used with @JoinColumn. It is strict JPA question.
此处有完整的文档JPA 2.1规范
它详细描述了如何声明关系以及@MappedSuperclass，继承策略和所有其他有用的东西.
Here you have complete documentation of JPA 2.1 specification
It describes in details how to declare relations as well as @MappedSuperclass, inheritance strategies and all other usefull stuff.
这篇关于Spring/JPA/Hibernate如何在一个存储库中执行两个实体的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
