JPA 多表查询 动态生成SQL语句

话不多说，直接上代码：
public Specification<PeviewDO> getSpecification(PeviewBO peviewBO) {
    Specification<PeviewDO> specification = new Specification<PeviewDO>() {

        public Predicate toPredicate(Root<PeviewDO> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            //page : 0 开始, limit : 默认为 10
            List<Predicate> predicates = new ArrayList<>();
            //权限
            if (StringUtils.isNotBlank(peviewBO.getRoleLevelCode())) {
                String[] submit_statuses = peviewBO.getRoleLevelCode().split(",");
                List<Predicate> roleLevelCodes = new ArrayList<>();
                for (int i = 0; i < submit_statuses.length; i++) {
                    roleLevelCodes.add(criteriaBuilder.like(root.get("roleLevelCode").as(String.class), "" + submit_statuses[i] + "%"));
                }
                predicates.add(criteriaBuilder.and(criteriaBuilder.or(roleLevelCodes.toArray(new Predicate[predicates.size()]))));
            }

            Join<SalesTargetMasterDO, PeviewDO> join = root.join("salesTargetMaster", JoinType.LEFT);
            predicates.add(criteriaBuilder.equal(join.get("isDel").as(Integer.class), "0"));
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getEmployeeNo())) {
                predicates.add(criteriaBuilder.like(join.get("employeeNo"), peviewBO.getEmployeeNo() + "%"));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getRegion())) {
                predicates.add(criteriaBuilder.equal(join.get("region"), peviewBO.getRegion()));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getSubRegion())) {
                predicates.add(criteriaBuilder.equal(join.get("subRegion"), peviewBO.getSubRegion()));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getDistrict())) {
                predicates.add(criteriaBuilder.like(join.get("district"), peviewBO.getDistrict() + "%"));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getYear())) {
                LocalDate firstDayOfMonth = LocalDate.parse(peviewBO.getYear() + "-01", DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                String lastDayOfMonth = getLastDayOfMonth(peviewBO.getYear());
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(join.get("startDate"), firstDayOfMonth));
                predicates.add(criteriaBuilder.lessThanOrEqualTo(join.get("startDate"), LocalDate.parse(peviewBO.getYear() + "-" + lastDayOfMonth, DateTimeFormatter.ofPattern("yyyy-MM-dd"))));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getRoleType())) {
                predicates.add(criteriaBuilder.like(join.get("roleType"), "%" + peviewBO.getRoleType() + "%"));
            }
            //设置条件
            if (StringUtils.isNotBlank(peviewBO.getPerformanceType())) {
                predicates.add(criteriaBuilder.equal(root.get("performanceType"), peviewBO.getPerformanceType()));
            }

            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    };
    return specification;
}

//获取判断条件
Specification<PeviewDO> specification = salesTargetMasterService.getSpecification(peviewBO);

//studentId必须是实体类属性与数据库对应，否则报ropertyReferenceException异常
List<PeviewDO> peivewDOList = this.masterToDatilRespository.findAll(specification,sort);

下面说一下用的

spring-data-jpa-2.1.15.RELEASE.jar

在子表中添加
@OneToOne(optional = false)
@JoinColumn(name = "pmkb_id", referencedColumnName = "id", insertable = false, updatable = false
        , foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT))
private SalesTargetMasterDO salesTargetMaster;
@OneToOne(optional = false)
@JoinColumn(name = "kbdatail_id", referencedColumnName = "kbdatail_id", insertable = false, updatable = false
        , foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT))
private YejiSalesDetailDO yejiSalesDetailDO;

参考资料如下

https://www.cnblogs.com/arrrrrya/p/7865090.html

JAVA使用easyexcel操作Excel

https://www.yuque.com/easyexcel/doc/fill#5dXFh