Spring Data JPA -- 多表操作(一对多)

本是古典何须时尚 2023-07-08 02:44 28阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70][]

### 前言 ###

在实际的开发中，一对多是家常便饭。举个栗子，比如一个部门，在部门里面包含了多个员工，其中，部门就是一，而员工当然就是多。接下来就以这个栗子进行开发。

以下是我所用到的表数据：

表employee: ![20200224180817993.png][]

表department：![20200224180856178.png][]

###  1.新建两个实体类 ###

Employee类：

package com.chen.domain.entity;
    
    import lombok.Getter;
    import lombok.Setter;
    
    import javax.persistence.*;
    
    @Getter
    @Setter
    @Entity
    @Table(name = "test_employee")
    public class Employee {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private int id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "age")
        private int age;
    
        @ManyToOne(targetEntity = Department.class)
        @JoinColumn(name = "dept_id",referencedColumnName = "id")
        private Department department;
    }

说明：因为Employee是属于多的关系，那么我们就需要用到@ManyToOne(targetEntity = Department.class)，代表多对一，配置级联关系，然后使用@JoinColumn(name = "dept\_id",referencedColumnName = "id")配置两个表之间的映射关系，其中name = "dept\_id"表示外键名称，referencedColumnName = "id"表示该外键与Department类的id属性关联。

Department类：

package com.chen.domain.entity;
    
    import lombok.Getter;
    import lombok.Setter;
    
    import javax.persistence.*;
    
    @Getter
    @Setter
    @Entity
    @Table(name = "test_department")
    public class Department {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private Integer id;
    
        @Column(name = "name")
        private String name;
    
    }

### 2.新建dao层接口 ###

package com.chen.dao;
    
    import com.chen.domain.entity.Employee;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    import java.util.Map;
    
    @Repository
    public interface EmployeeRepository extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {
    
        @Query(value = "SELECT e.name,e.age,d.name AS department FROM test_employee e LEFT JOIN test_department d on e.dept_id=d.id ",nativeQuery = true)
        List<Map<String,Object>> findAllEmployee();
    }

### 3.新建service层，用来实现dao层接口中的方法 ###

package com.chen.service;
    
    import com.chen.dao.EmployeeRepository;
    import com.chen.domain.entity.Employee;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import javax.transaction.Transactional;
    import java.util.List;
    import java.util.Map;
    
    @Service
    @Transactional
    public class EmployeeService {
    
        @Autowired
        private EmployeeRepository employeeRepository;
    
        public List<Employee> findAll(){
            return employeeRepository.findAll();
        }
    
        public List<Map<String, Object>> findAllEmployee(){
            return employeeRepository.findAllEmployee();
        }
    }

### 4.使用controller进行业务调用 ###

package com.chen.controller;
    
    import com.chen.domain.entity.Employee;
    import com.chen.restful.RestResponse;
    import com.chen.service.EmployeeService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    import java.util.Map;
    
    @RestController
    public class EmployeeController {
    
        @Autowired
        private EmployeeService employeeService;
    
        @PostMapping("findAll")
        public RestResponse findAll(){
            List<Employee> allEmployee = employeeService.findAll();
            return RestResponse.ok().data(allEmployee);
        }
    
        @PostMapping("findAllEmployee")
        public RestResponse findAllEmployee(){
            List<Map<String, Object>> allEmployee = employeeService.findAllEmployee();
            return RestResponse.ok().data(allEmployee);
        }
    
    }

### 5.测试 ###

第一个先测试JPA中自带的方法：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 1][]

接下来测试自己定义的方法：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 2][]

### 6.放弃外键 ###

一般在项目中，是不会在表中建立外键，原因有很多，比如影响效率或因为外键级联的关系导致增删改出现问题，我的做法是直接放弃外键，就是在表与表之间不设立外键关系，当然，还是要配标识来进行关联。目前不清楚这种做法是否是合理的，但经过我自己测试，是完全可用，且不会出现问题。这样一来的话，便可以省去很多不必要的操作，就连非常复杂的多表操作也可以轻易实现。

接下来我把Employee表中的外键配置去掉，换成一个普通的字段：

package com.chen.domain.entity;
    
    import lombok.Getter;
    import lombok.Setter;
    
    import javax.persistence.*;
    
    @Getter
    @Setter
    @Entity
    @Table(name = "test_employee")
    public class Employee {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private int id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "age")
        private int age;
    
        @Column(name = "dept_id")
        private int deptId;
    
        //@ManyToOne(targetEntity = Department.class)
        //@JoinColumn(name = "dept_id",referencedColumnName = "id")
        //private Department department;
    }

然后同样进行请求测试：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 3][]

可以发现，结果跟设了外键级联的结果一样。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210706134156174.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2,size_16,color_FFFFFF,t_70
[20200224180817993.png]: https://img-blog.csdnimg.cn/20200224180817993.png
[20200224180856178.png]: https://img-blog.csdnimg.cn/20200224180856178.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200224181813298.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200224181853737.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200224183837759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTA5NzQ2,size_16,color_FFFFFF,t_70