SpringBoot2.3整合Spring Data JPA实现复杂(多表关联)查询

1. 概述

上一篇介绍了Spring Data JPA单表基本操作，在实际开发中，经常会涉及到多表关联查询，本文主要介绍SpringBoot如何整合Spring Data JPA来实现复杂的多表关联查询。pom核心依赖和yml配置信息见SpringBoot2.3整合Spring Data JPA实现基本操作

2. 表和数据准备

首先新建几张表，sql如下：

CREATE TABLE address_info (
 id BIGINT UNSIGNED auto_increment NOT NULL COMMENT '主键ID',
 province varchar(20) NULL COMMENT '省',
 city varchar(20) NULL COMMENT '市',
 area varchar(20) NULL COMMENT '区',
 create_time DATETIME NULL COMMENT '创建时间',
 update_time DATETIME NULL COMMENT '更新时间',
 CONSTRAINT address_info_pk PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='地址信息表';
CREATE TABLE dept_info (
  id BIGINT UNSIGNED auto_increment NOT NULL COMMENT '主键ID',
  dept_name varchar(30) NULL COMMENT '部门名称',
  create_time DATETIME NULL COMMENT '创建时间',
  update_time DATETIME NULL COMMENT '更新时间',
  CONSTRAINT dept_info_pk PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='部门表';
CREATE TABLE salary_info (
    id BIGINT UNSIGNED auto_increment NOT NULL COMMENT '主键ID',
    emp_id BIGINT UNSIGNED NULL COMMENT '用户ID',
    salary_year INT NULL COMMENT '年份',
    salary_month TINYINT NULL COMMENT '月份',
    salary DECIMAL NULL COMMENT '薪水',
    create_time DATETIME NULL COMMENT '创建时间',
    update_time DATETIME NULL COMMENT '更新时间',
    CONSTRAINT salary_info_pk PRIMARY KEY (id),
    CONSTRAINT emp_id_FK FOREIGN KEY (emp_id) REFERENCES emp_info(id) ON DELETE RESTRICT ON UPDATE RESTRICT
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='薪水表';
CREATE TABLE emp_info (
 id BIGINT UNSIGNED auto_increment NOT NULL COMMENT '主键ID',
 emp_name varchar(30) NULL COMMENT '员工姓名',
 phone varchar(11) NULL COMMENT '手机号',
 dept_id BIGINT UNSIGNED NULL COMMENT '部门编号',
 addr_id BIGINT UNSIGNED NULL COMMENT '地址ID',
 create_time DATETIME NULL COMMENT '创建时间',
 update_time DATETIME NULL COMMENT '更新时间',
 CONSTRAINT emp_info_pk PRIMARY KEY (id),
 CONSTRAINT dept_id_FK FOREIGN KEY (dept_id) REFERENCES dept_info(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
 CONSTRAINT address_id_FK FOREIGN KEY (addr_id) REFERENCES address_info(id) ON DELETE RESTRICT ON UPDATE RESTRICT
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='员工表';

3. 核心实体类

@Getter
@Setter
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@Table(name = "emp_info")
public class EmpInfo {

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

    private String empName;

    private String phone;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "dept_id")
    private DeptInfo deptInfo;

    @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "addr_id")
    private AddressInfo addressInfo;

    private LocalDateTime createTime;

    private LocalDateTime updateTime;

    @OrderBy("id")
    @BatchSize(size = 12)
    @OneToMany(mappedBy = "empInfo", fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<SalaryInfo> salaryInfos;
}

解释：FetchType用于配置获取数据策略，EAGER表示及时加载数据，LAZY表示使用的时候才加载数据；CascadeType用于配置多表级联操作，PERSIST表示给当前实体类操作另一个实体类的权限，MERGE表示当前实体类数据改变时，会相应更新映射实体类数据，REMOVE表示删除当前实体类数据时，会相应删除映射实体类数据，REFRESH表示操作数据前先刷新数据再做操作，DETACH表示删除当前实体类数据时，撤销映射实体类外键关联，ALL表示拥有所有权限。

4. 业务接口

public interface EmpService {

    /**
     * 保存数据
     * @param empInfo
     * @return
     */
    EmpInfo insertEmpInfo(EmpInfo empInfo);

    /**
     * 更新数据
     * @param empInfo
     * @return
     */
    EmpInfo updateEmpInfo(EmpInfo empInfo);

    /**
     * 根据主键ID查询数据
     * @param id
     * @return
     */
    EmpInfo findById(Long id);

    /**
     * 分页查询
     * @param pageNum
     * @param pageSize
     * @return
     */
    Page<EmpInfo> findByPage(int pageNum, int pageSize);

    /**
     * 多条件分页查询
     * @param empName
     * @param pageNum
     * @param pageSize
     * @return
     */
    Page<EmpInfo> findByExampleAndPage(String empName, int pageNum, int pageSize);
}

5. 业务接口实现类

@Service
public class EmpServiceImpl implements EmpService {

    @Autowired
    private EmpRepository empRepository;

    @Override
    public EmpInfo insertEmpInfo(EmpInfo empInfo) {
        return empRepository.save(empInfo);
    }

    @Override
    public EmpInfo updateEmpInfo(EmpInfo empInfo) {
        return empRepository.save(empInfo);
    }

    @Override
    public EmpInfo findById(Long id) {
        return empRepository.findById(id).get();
    }

    @Override
    public Page<EmpInfo> findByPage(int pageNum, int pageSize) {
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        return empRepository.findAll(pageable);
    }

    @Override
    public Page<EmpInfo> findByExampleAndPage(String empName, int pageNum, int pageSize) {
        EmpInfo empInfo = new EmpInfo();
        empInfo.setEmpName(empName);
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        ExampleMatcher exampleMatcher = ExampleMatcher.matching()
                .withMatcher("empName", ExampleMatcher.GenericPropertyMatchers.exact());
        Example<EmpInfo> example = Example.of(empInfo, exampleMatcher);
        return empRepository.findAll(example, pageable);
    }
}