Springboot使用Specification连表查询LEFT

2023-11-08 08:18:52

大致目录机构

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

}

查询老师

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

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

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

-- --------------------------------------------------------
-- 主机:                           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只支持主表的条件过滤，关联表的过滤条件未生效！！！

码农公寓

相关文章