04-27 04:46 阅读 126

Springboot使用Specification连表多条件查询（完整demo）

目录结构：

package com.frank.leftJoinQuery.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import javax.persistence.*;

/**

* @author 小石潭记

* @date 2020/12/12 18:32

* @Description: ${todo} B表

@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;

// 同时在B表里添加A表记录的id 默认A表名 + "_id"

// 这里感觉就是 Teacher 小写teacher + "_id"

// private Teacher teacher; 应该是根据这里的teacher来的 @OneToMany(mappedBy="teacher")

// @JsonIgnoreProperties("teacher") 这里的teacher名字需要保持一致

@Column(name = "teacher_id", insertable = false, updatable = false)

private Long teacherId;

@ManyToOne

@JsonIgnoreProperties("studentList") //该注解可防止json转换进入死循环

private Teacher teacher;

}

package com.frank.leftJoinQuery.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

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} A表

@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(mappedBy="teacher")

@JsonIgnoreProperties("teacher") //该注解可防止json转换进入死循环

private List<Student> studentList;

}

package com.frank.leftJoinQuery.repository;

import com.frank.leftJoinQuery.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 Teacher1Repository extends PagingAndSortingRepository<Teacher, Integer>, JpaSpecificationExecutor<Teacher> {

}

package com.frank.leftJoinQuery.repository;

import com.frank.leftJoinQuery.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 TStudent1Repository extends PagingAndSortingRepository<Student, Integer>, JpaSpecificationExecutor<Student> {

}

package com.frank.leftJoinQuery.specification;

import com.frank.leftJoinQuery.domain.StudentRequest;

import com.frank.leftJoinQuery.entity.Student;

import com.frank.leftJoinQuery.entity.Teacher;

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 Student1Specification {

/**

* root 就是mobile实例 root.get("name") name是属性名不是数据库字段名

* @param

* @return

* */

public Specification<Student> getStudentSpecification(StudentRequest 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);

}

// 使用左连接查询注意这里的teacher不能乱起名字的

Join<Student, Teacher> teacherJoin = root.join("teacher", 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);

}

return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();

};

}

package com.frank.leftJoinQuery.specification;

import com.frank.leftJoinQuery.domain.StudentRequest;

import com.frank.leftJoinQuery.entity.Student;

import com.frank.leftJoinQuery.entity.Teacher;

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 Teacher1Specification {

/**

* root 就是mobile实例 root.get("name") name是属性名不是数据库字段名

* @param

* @return

* */

public Specification<Teacher> getTeacherSpecification(StudentRequest 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);

}

// 使用左连接查询注意这里的teacher不能乱起名字的

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.leftJoinQuery.domain;

import lombok.Data;

/**

* @author 小石潭记

* @date 2021/4/23 22:49

* @Description: ${todo}

@Data

public class StudentRequest {

private String name;

private String accountNumber;

private String studentName;

private String grade;

}

package com.frank.leftJoinQuery.service;

import com.frank.leftJoinQuery.domain.StudentRequest;

import com.frank.leftJoinQuery.entity.Student;

import com.frank.leftJoinQuery.entity.Teacher;

import com.frank.leftJoinQuery.repository.TStudent1Repository;

import com.frank.leftJoinQuery.repository.Teacher1Repository;

import com.frank.leftJoinQuery.specification.Student1Specification;

import com.frank.leftJoinQuery.specification.Teacher1Specification;

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 LeftJoinService {

@Autowired

private TStudent1Repository studentRepository;

@Autowired

private Teacher1Repository teacher1Repository;

@Autowired

private Teacher1Specification teacher1Specification;

@Autowired

private Student1Specification student1Specification;

public Page<Student> getStudentList(StudentRequest request) {

PageRequest pageRequest = PageRequest.of(0, 10);

return studentRepository.findAll(student1Specification.getStudentSpecification(request), pageRequest);

}

public Page<Teacher> getTeacherList(StudentRequest request) {

PageRequest pageRequest = PageRequest.of(0, 10);

return teacher1Repository.findAll(teacher1Specification.getTeacherSpecification(request), pageRequest);

}

package com.frank.leftJoinQuery.controller;

import com.frank.leftJoinQuery.domain.StudentRequest;

import com.frank.leftJoinQuery.entity.Student;

import com.frank.leftJoinQuery.entity.Teacher;

import com.frank.leftJoinQuery.service.LeftJoinService;

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.RestController;

/**

* @author 小石潭记

* @date 2020/12/12 19:02

* @Description: ${todo}

@RestController

public class LeftJoinController {

@Autowired

private LeftJoinService leftJoinService;

@GetMapping("/student1")

public Page<Student> getStudent(StudentRequest request) {

return leftJoinService.getStudentList(request);

}

@GetMapping("/teacher1")

public Page<Teacher> getTeacher(StudentRequest request) {

return leftJoinService.getTeacherList(request);

}

测试：根据多的一方学生查询，每个学生对应一个老师

根据一对多来查询，每个老师对应多个学生

-- --------------------------------------------------------

-- 主机: 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 '等级',

`teacher_id` int(11) DEFAULT NULL,

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`, `teacher_id`) VALUES

(1, '陈同学', '重庆', '1001', 'A', 1),

(2, '谭同学', '丰都', '1001', 'B', 1),

(3, '文同学', '涪陵', '1002', 'A', 2),

(4, '刘同学', '忠县', '1003', 'C', 3),

(5, '肖同学', '万州', '1002', 'A', 2);

/*!40000 ALTER TABLE `t_student` 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 */;

参考文章：

https://blog.csdn.net/xpopi/article/details/83915533

https://blog.csdn.net/lyl614976970/article/details/92794120

————————————————

原文链接：https://blog.csdn.net/qq_33371766/article/details/116174254