java - Spring/JPA/Hibernate 如何在一个存储库中执行两个实体的连接

Question

首先假设我们有两个表。一个表是员工表，包含以下列:

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))

Here is a sample of the table's values

DEPARTMENT:------------------------dept_id    | dept_label------------------------ 1         | Sales------------------------ 2         | Technology------------------------ 3         | Finance

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时，我们需要创建两个类:

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;
    }

}

部门.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):

员工存储库

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();

}

最后，将分类查询绑定(bind)到应用程序端点的 Java Controller :

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") )，所有内容都按原样返回。

我的主要问题是，如果我需要部门内部的内容，当查询位于特定类(表)(即 Employee)内部时，如何返回 JOIN QUERY？

我已经尝试过 @JoinColumn 注释，但效果不佳(也许我做错了)。

有什么想法吗？谢谢。

Answer 1

您不必使用原始连接来做到这一点，只需使用适当的关系映射即可。 Employee 和 Departament 之间的关系听起来像 @ManyToOne 或 @ManyToMany。您将能够例如 employee.getDepartament() 或通过 employee.departament.name=:name 进行查询

http://www.objectdb.com/api/java/jpa/ManyToMany

您甚至可以映射双向关系，以便您能够获取员工的部门信息，以及给定部门的所有员工

PS。 @JoinColumn 用于指定用于连接的数据库列，它与通过选定的命名策略(通常是entityname_id)创建的列不同。实际的关系映射是通过声明 @OneToOne @OneToMany @ManyToMany 来完成的，这些可以但不必与 @ 一起使用连接列。这是严格的 JPA 问题。

Here you have complete documentation of JPA 2.1 specification它详细描述了如何声明关系以及@MappedSuperclass、继承策略和所有其他有用的东西。