Springboot使用Specification连表查询LEFT

素颜马尾好姑娘i 2023-01-16 04:59 247阅读 0赞

大致目录机构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70][]

package com.frank.leftQuery.entity;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.*;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 18:32
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_student")
    public class Student {
        @Id
        private Long id;
    
        private String name;
    
        private String address;
    
        private String accountNumber;
    
        private String grade;
    }

package com.frank.leftQuery.entity;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.persistence.*;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 18:36
     * @Description: ${todo}
     */
    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Table(name = "t_teacher")
    public class Teacher {
        @Id
        @GeneratedValue
        private Long id;
        private String name;
        private String address;
        private String accountNumber;
        // 一个老师下面有很多个学生 根据账号关联的
        @OneToMany
        // 由于没有使用外键 所以才使用了关联表 关联的列列名必须是两个表名_id
        @JoinTable(name = "t_student_t_teacher",
                joinColumns = {@JoinColumn(name = "t_teacher_id")},
                inverseJoinColumns = {@JoinColumn(name = "t_student_id")})
        private List<Student> studentList;
    }

package com.frank.leftQuery.repository;
    
    import com.frank.leftQuery.entity.Teacher;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Component;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:21
     * @Description: ${todo}
     */
    @Component
    public interface TeacherRepository extends PagingAndSortingRepository<Teacher, Integer>, JpaSpecificationExecutor<Teacher> {
    }

package com.frank.leftQuery.repository;
    
    import com.frank.leftQuery.entity.Student;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Component;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:21
     * @Description: ${todo}
     */
    @Component
    public interface TStudentRepository extends PagingAndSortingRepository<Student, Integer>, JpaSpecificationExecutor<Student> {
    }

package com.frank.leftQuery.service;
    
    import com.frank.leftQuery.domain.TeacherRequest;
    import com.frank.leftQuery.entity.Teacher;
    import com.frank.leftQuery.repository.TeacherRepository;
    import com.frank.leftQuery.specification.TeacherSpecification;
    import org.apache.commons.lang.StringUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.stereotype.Service;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:22
     * @Description: ${todo}
     */
    @Service
    public class TeacherService {
    
        @Autowired
        private TeacherRepository teacherRepository;
    
        @Autowired
        private TeacherSpecification teacherSpecification;
    
        public Page<Teacher> getTeacherList(TeacherRequest request) {
            PageRequest pageRequest = PageRequest.of(0, 10);
            return teacherRepository.findAll(teacherSpecification.getTeacherSpecification(request), pageRequest);
        }
    
    }

package com.frank.leftQuery.specification;
    
    import com.frank.jpaSpecification.entity.PageStudentRequest;
    import com.frank.leftQuery.domain.TeacherRequest;
    import com.frank.leftQuery.entity.Teacher;
    import com.frank.leftQuery.entity.Student;
    import org.apache.commons.lang.StringUtils;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.stereotype.Component;
    
    import javax.persistence.criteria.Join;
    import javax.persistence.criteria.JoinType;
    import javax.persistence.criteria.Predicate;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 14:27
     * @Description: ${todo}
     */
    @Component
    public class TeacherSpecification {
    
        /**
         * root 就是mobile实例  root.get("name") name是属性名 不是数据库字段名
         * @param
         * @return
         * */
        public Specification<Teacher> getTeacherSpecification(TeacherRequest request) {
            return (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> predicateList = new ArrayList<>();
                // 主表老师的筛选生效的
                // 筛选教师名字
                if (StringUtils.isNotBlank(request.getName())) {
                    Predicate teacherPre = criteriaBuilder.equal(root.get("name"), request.getName());
                    predicateList.add(teacherPre);
                }
                // 筛选账户号
                if (StringUtils.isNotBlank(request.getAccountNumber())) {
                    Predicate teacherPre = criteriaBuilder.equal(root.get("accountNumber"), request.getAccountNumber());
                    predicateList.add(teacherPre);
                }
                // 使用左连接查询
                Join<Teacher, Student> teacherJoin = root.join("studentList", JoinType.LEFT);
                // todo 关联表 学生表的筛选未生效
                if (StringUtils.isNotBlank(request.getAccountNumber())) {
                    Predicate studentPre = criteriaBuilder.equal(teacherJoin.get("accountNumber"), request.getAccountNumber());
                    predicateList.add(studentPre);
                }
                if (StringUtils.isNotBlank(request.getStudentName())) {
                    Predicate studentPre = criteriaBuilder.equal(teacherJoin.get("name"), request.getStudentName());
                    predicateList.add(studentPre);
                }
                if (StringUtils.isNotBlank(request.getGrade())) {
                    Predicate studentPre = criteriaBuilder.equal(teacherJoin.get("grade"), request.getGrade());
                    predicateList.add(studentPre);
                }
                // 去重
                criteriaQuery.distinct(true);
                return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
            };
        }
    
    }

package com.frank.leftQuery.domain;
    
    import lombok.Data;
    
    /**
     * @author 小石潭记
     * @date 2021/4/23 22:49
     * @Description: ${todo}
     */
    @Data
    public class TeacherRequest {
        private String name;
        private String accountNumber;
        private String studentName;
        private String grade;
    }

package com.frank.leftQuery.controller;
    
    import com.frank.leftQuery.domain.TeacherRequest;
    import com.frank.leftQuery.entity.Teacher;
    import com.frank.leftQuery.service.TeacherService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    /**
     * @author 小石潭记
     * @date 2020/12/12 19:02
     * @Description: ${todo}
     */
    @RestController
    @RequestMapping("/teacher")
    public class TeacherController {
    
        @Autowired
        private TeacherService teacherService;
    
        @GetMapping
        public Page<Teacher> index(TeacherRequest request) {
            return teacherService.getTeacherList(request);
        }
    
    }

查询老师

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1][]

这里会查询出关联表里的所有的学生数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 2][]

接下来筛选学生等级为A的却失效了，因为中间表值关联的两个表的id，查询的sql并不会添加学生表的条件。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 3][]

由于不使用外键（阿里巴巴规范也不推荐使用外键了~~）所以创建了中间表

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        5.6.40 - MySQL Community Server (GPL)
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  8.2.0.4675
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    -- 导出  表 test.t_student 结构
    CREATE TABLE IF NOT EXISTS `t_student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
      `address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
      `account_number` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
      `grade` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '等级',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    -- 正在导出表  test.t_student 的数据：~5 rows (大约)
    DELETE FROM `t_student`;
    /*!40000 ALTER TABLE `t_student` DISABLE KEYS */;
    INSERT INTO `t_student` (`id`, `name`, `address`, `account_number`, `grade`) VALUES
    	(1, '陈同学', '重庆', '1001', 'A'),
    	(2, '谭同学', '丰都', '1001', 'B'),
    	(3, '文同学', '涪陵', '1002', 'A'),
    	(4, '刘同学', '忠县', '1003', 'C'),
    	(5, '肖同学', '万州', '1002', 'A');
    /*!40000 ALTER TABLE `t_student` ENABLE KEYS */;
    
    
    -- 导出  表 test.t_student_t_teacher 结构
    CREATE TABLE IF NOT EXISTS `t_student_t_teacher` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `t_teacher_id` int(11) NOT NULL,
      `t_student_id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    -- 正在导出表  test.t_student_t_teacher 的数据：~5 rows (大约)
    DELETE FROM `t_student_t_teacher`;
    /*!40000 ALTER TABLE `t_student_t_teacher` DISABLE KEYS */;
    INSERT INTO `t_student_t_teacher` (`id`, `t_teacher_id`, `t_student_id`) VALUES
    	(1, 1, 1),
    	(2, 1, 2),
    	(3, 2, 3),
    	(4, 3, 4),
    	(5, 2, 5);
    /*!40000 ALTER TABLE `t_student_t_teacher` ENABLE KEYS */;
    
    
    -- 导出  表 test.t_teacher 结构
    CREATE TABLE IF NOT EXISTS `t_teacher` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
      `address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
      `account_number` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    
    -- 正在导出表  test.t_teacher 的数据：~3 rows (大约)
    DELETE FROM `t_teacher`;
    /*!40000 ALTER TABLE `t_teacher` DISABLE KEYS */;
    INSERT INTO `t_teacher` (`id`, `name`, `address`, `account_number`) VALUES
    	(1, '小明老师', '四川', '1001'),
    	(2, '小蓝老师', '重庆', '1002'),
    	(3, '小花老师', '北京', '1003');
    /*!40000 ALTER TABLE `t_teacher` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

目前该demo只支持主表的条件过滤，关联表的过滤条件未生效！！！

// A B表 A-->B 一对多
    @Component
    public class SwindlePushEventSpecificationFactory {
    
        /**
         * 根据条件分页查询数据
         * @param request
         * @return
         */
        public Specification<SwindlePolicePushEntity> getSwindlePolicePushSpecification(SwindlePolicePushRequest request) {
            return (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> predicateList = new ArrayList<>();
       // 首先查询多条记录的B表
                String accountNumber = request.getAccountNumber();
                // 事件列表的账号
                if (StringUtils.isNotBlank(accountNumber)) {
                    predicateList.add(criteriaBuilder.equal(root.get("accountNumber"), accountNumber));
                }
                // 生效日期
                LocalDate takeEffectStartDate = request.getTakeEffectStartDate();
                if (takeEffectStartDate != null) {
                    predicateList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("effectiveDate").as(LocalDate.class), takeEffectStartDate));
                }
                // 生效结束日期
                LocalDate takeEffectEndDate = request.getTakeEffectEndDate();
                if (takeEffectEndDate != null) {
                    predicateList.add(criteriaBuilder.lessThanOrEqualTo(root.get("effectiveDate").as(LocalDate.class), takeEffectEndDate));
                }
                
                // --------------使用左连接查询---------
                Join<SwindlePolicePushEntity, SwindleAccountEntity> policePushJoin = root.join("swindleAccount", JoinType.INNER);
       // 再过滤一条记录的A表
                Long id = request.getId();
                if (id != null) {
                    predicateList.add(criteriaBuilder.equal(policePushJoin.get("id"), id));
                }
                // 账号
                if (StringUtils.isNotBlank(accountNumber)) {
                    predicateList.add(criteriaBuilder.equal(policePushJoin.get("accountNumber"), accountNumber));
                }
                
                // 冻结止付的数据
                if (request.getPushType() == PolicePushTypeEnum.FREEZE_PAYMENT.ordinal()) {
                    predicateList.add(criteriaBuilder.equal(policePushJoin.get("freezePaymentFlag"), true));
                }
                // 黑名单的数据
                if (request.getPushType() == PolicePushTypeEnum.BLACK_LIST.ordinal()) {
                    predicateList.add(criteriaBuilder.equal(policePushJoin.get("blackListFlag"), true));
                }
                return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
            };
        }
    
    }
    
    // 在B表的对象里面设置A表的对象
    @ManyToOne
    private SwindleAccountEntity swindleAccount;
    
    // 同时在B表里添加A表记录的id  默认A表名 + "_id"
    @Column(name = "swindle_account_id", insertable = false, updatable = false)
    private Long swindleAccountId;
    
    // 这种结果就是以多条数据为主的结果集
    // 如B表里 4、7对应A表的101
    // 这样出来的结果就是两条B记录，但是里面的A对象都是相同的！！！

如果关联表查询副表的指定条件的数据：

public Specification<SwindleAccountEntity> getBlackSwindleAccountSpecification(SwindleBlackRequest request) {
            return (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> predicateList = new ArrayList<>();
                predicateList = getBaseSwindleAccountPredicate(request, root, predicateList, criteriaBuilder);
                predicateList.add(criteriaBuilder.equal(root.get("blackListFlag"), true));
                String customerNumber = request.getCustomerNumber();
                if (StringUtils.isNotBlank(customerNumber)) {
                    predicateList.add(criteriaBuilder.equal(root.get("customerNumber"), customerNumber));
                }
                // 查询有效期
                if (request.getPeriodValidityFlag() != null) {
                    // 主表账户表
                    Subquery<SwindleAccountEntity> subQuery = criteriaQuery.subquery(SwindleAccountEntity.class);
                    // 副表事件表
                    Root<SwindlePolicePushEntity> subRoot = subQuery.from(SwindlePolicePushEntity.class);
                    if (request.getPeriodValidityFlag()) {
                        // 这里是子查询的条件，前者是自身的条件，后者是主表的关联条件，当然where方法的参数是个可变参数，可以根据自己需要加条件
                        subQuery.where(criteriaBuilder.greaterThan(subRoot.get("expirationDate").as(LocalDate.class), LocalDate.now()),
                                criteriaBuilder.equal(subRoot.get("pushType"), PolicePushTypeEnum.BLACK_LIST.ordinal()),
                                // 主表和副表相同的字段
                                criteriaBuilder.equal(root.get("accountNumber"), subRoot.get("accountNumber")));
                    } else {
                        subQuery.where(criteriaBuilder.isNotNull(subRoot.get("expirationDate").as(LocalDate.class)),
                                criteriaBuilder.equal(subRoot.get("pushType"), PolicePushTypeEnum.BLACK_LIST.ordinal()),
                                criteriaBuilder.lessThanOrEqualTo(subRoot.get("expirationDate").as(LocalDate.class), LocalDate.now()),
                                criteriaBuilder.equal(root.get("accountNumber"), subRoot.get("accountNumber")));
                    }
                    // 这句话不加会报错，因为他不知道你子查询要查出什么字段
                    subQuery.select(subRoot.get("id"));
                    predicateList.add(criteriaBuilder.exists(subQuery));
                }
                return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
            };
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70]: /images/20221022/b44302c6290741efa71162146664ecef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1]: /images/20221022/ca83fad630994628a6d1cef701edefa9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 2]: /images/20221022/34951af024e74b9488f47d67e8ba234f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 3]: /images/20221022/5a0a81e44fff4756871fb262a4de87d3.png