Specification查询

朴灿烈づ我的快乐病毒、 2021-07-24 20:05 372阅读 0赞

**一pom**

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0  http://maven.apache.org/xsd/maven-4.0.0.xsd">
         <modelVersion>4.0.0</modelVersion>
         <groupId>org.fkit</groupId>
         <artifactId>springdatajpaspecificationtest</artifactId>
         <version>0.0.1-SNAPSHOT</version>
         <packaging>jar</packaging>
         <name>springdatajpaspecificationtest</name>
         <url>http://maven.apache.org</url>
         
         <parent>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-parent</artifactId>
               <version>2.0.0.RELEASE</version>
               <relativePath />
         </parent>
         <properties>
               <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
               <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
               <java.version>1.8</java.version>
         </properties>
         <dependencies>
               
               <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-web</artifactId>
               </dependency>
               
               <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-thymeleaf</artifactId>
               </dependency>
               
               <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
               </dependency>
               
               <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-data-jpa</artifactId>
               </dependency>
               <dependency>
                    <groupId>junit</groupId>
                    <artifactId>junit</artifactId>
                    <scope>test</scope>
               </dependency>
         </dependencies>
    </project>

**二 启动类**

package org.fkit.springdatajpaspecificationtest;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class App
    {
        public static void main( String[] args )
        {
            // SpringApplication 用于从main方法启动Spring应用的类。
            SpringApplication.run(App.class, args);
        }
    }

**三 实体类**

1 Clazz

package org.fkit.springdatajpaspecificationtest.bean;
    import java.io.Serializable;
    import java.util.HashSet;
    import java.util.Set;
    
    
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.OneToMany;
    import javax.persistence.Table;
    @Entity
    @Table(name="tb_clazz")
    public class Clazz implements Serializable{
    
    
        private static final long serialVersionUID = 1L;
        
        @Id
        @GeneratedValue(strategy=GenerationType.IDENTITY)
        private int code ;
        private String name ;
        // 班级与学生是一对多的关联
        @OneToMany(
                   fetch=FetchType.LAZY,
                   targetEntity=Student.class,
                   mappedBy="clazz"
                )     
        private Set<Student> students = new HashSet<>();
        
        public Clazz() {
            
        }
        // 班级对象
        public Clazz(String name) {
            this.name = name;
        }
        public int getCode() {
            return code;
        }
        public void setCode(int code) {
            this.code = code;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public Set<Student> getStudents() {
            return students;
        }
        public void setStudents(Set<Student> students) {
            this.students = students;
        }
    }

2 Student

package org.fkit.springdatajpaspecificationtest.bean;
    
    
    import java.io.Serializable;
    
    
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.GeneratedValue;
    import javax.persistence.GenerationType;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.Table;
    @Entity
    @Table(name="tb_student")
    public class Student implements Serializable{
    
    
        private static final long serialVersionUID = 1L;
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
        private String name ;
        private String address ;
        private int age ;
        private char sex;
        // 学生与班级是多对一的关系，这里配置的是双向关联
        @ManyToOne(fetch=FetchType.LAZY,
                targetEntity=Clazz.class
                )
        @JoinColumn(name="clazzId",referencedColumnName="code")
        private Clazz clazz ;
        public Student() {
    
    
        }
        public Student(String name, String address, int age, char sex,
                Clazz clazz) {
            super();
            this.name = name;
            this.address = address;
            this.age = age;
            this.sex = sex;
            this.clazz = clazz;
        }
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public String getAddress() {
            return address;
        }
        public void setAddress(String address) {
            this.address = address;
        }
        public int getAge() {
            return age;
        }
        public void setAge(int age) {
            this.age = age;
        }
        public char getSex() {
            return sex;
        }
        public void setSex(char sex) {
            this.sex = sex;
        }
        public Clazz getClazz() {
            return clazz;
        }
        public void setClazz(Clazz clazz) {
            this.clazz = clazz;
        }
    }

**四 Repository**

1 ClazzRepository

package org.fkit.springdatajpaspecificationtest.repository;
    import org.fkit.springdatajpaspecificationtest.bean.Clazz;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    
    public interface ClazzRepository extends JpaRepository<Clazz, Integer> ,JpaSpecificationExecutor<Clazz>{
        
    }

2 StudentRepository

package org.fkit.springdatajpaspecificationtest.repository;
    import org.fkit.springdatajpaspecificationtest.bean.Student;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    
    public interface StudentRepository extends JpaRepository<Student,Integer>,JpaSpecificationExecutor<Student>{
        
    }

**五 Service**

package org.fkit.springdatajpaspecificationtest.service;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    import javax.annotation.Resource;
    import javax.persistence.criteria.CriteriaBuilder;
    import javax.persistence.criteria.CriteriaQuery;
    import javax.persistence.criteria.JoinType;
    import javax.persistence.criteria.Path;
    import javax.persistence.criteria.Predicate;
    import javax.persistence.criteria.Root;
    
    
    import org.fkit.springdatajpaspecificationtest.bean.Clazz;
    import org.fkit.springdatajpaspecificationtest.bean.Student;
    import org.fkit.springdatajpaspecificationtest.repository.ClazzRepository;
    import org.fkit.springdatajpaspecificationtest.repository.StudentRepository;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.data.domain.Sort;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.util.StringUtils;
    @Service
    public class ShcoolService {
        // 注入数据访问层接口对象
        @Resource
        private StudentRepository studentRepository;
        @Resource
        private ClazzRepository clazzRepository;
        @Transactional
        public void saveClazzAll(List<Clazz> clazzs) {
            clazzRepository.saveAll(clazzs);
        }
        @Transactional
        public void saveStudentAll(List<Student> students) {
            studentRepository.saveAll(students);
        }
        
        /**
         * 根据性别查询学生信息
         * @param clazzName
         * @return
         */
        @SuppressWarnings("serial")
        public List<Map<String, Object>> getStusBySex(char sex) {
            List<Student> students = studentRepository.findAll(new Specification<Student>() {
    
    
                @Override
                public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
                        CriteriaBuilder cb) {
                   // root.get("sex")表示获取sex这个字段名称,equal表示执行equal查询
                   // 相当于 select s from Student s where s.sex = ?1
                   Predicate p1 = cb.equal(root.get("sex"), sex);
                   return p1;
                }
            });
            List<Map<String, Object>>  results = new ArrayList<>();
            // 遍历查询出的学生对象，提取姓名，年龄，性别信息
            for(Student student:students){
                Map<String , Object> stu = new HashMap<>();
                stu.put("name", student.getName());
                stu.put("age", student.getAge());
                stu.put("sex", student.getSex());
                results.add(stu);
            }
            return results;
        }
        
        
        /**
         * 动态查询学生信息 ：可以根据学生对象的姓名(模糊匹配)， 地址查询(模糊匹配),性别,班级查询学生信息
         *               如果没有传输参数,默认查询所有的学生信息
         * @param clazzName
         * @return
         */
        @SuppressWarnings("serial")
        public List<Map<String, Object>> getStusByDynamic(Student student) {
            List<Student> students = studentRepository.findAll(new Specification<Student>() {
                @Override
                public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
                        CriteriaBuilder cb) {
                    // 本集合用于封装查询条件
                    List<Predicate> predicates = new ArrayList<Predicate>();  
                    if(student!=null){
                        /** 是否传入了姓名来查询  */
                        if(!StringUtils.isEmpty(student.getName())){
                            predicates.add(cb.like(root.<String> get("name"),"%" + student.getName() + "%"));
                        }
                        /** 是否传入了地址来查询  */
                        if(!StringUtils.isEmpty(student.getAddress())){
                            predicates.add(cb.like(root.<String> get("address"),"%" + student.getAddress() + "%"));
                        }
                        /** 是否传入了性别来查询 */
                        if(student.getSex() != '\0'){
                            predicates.add(cb.equal(root.<String> get("sex"),student.getSex()));
                        }
                        /** 判断是否传入了班级信息来查询 */
                        if(student.getClazz()!=null && !StringUtils.isEmpty(student.getClazz().getName())){
                            root.join("clazz", JoinType.INNER);
                            Path<String> clazzName = root.get("clazz").get("name");
                            predicates.add(cb.equal(clazzName, student.getClazz().getName()));
                        }
                    }
                    return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
                }
            });
            List<Map<String, Object>>  results = new ArrayList<>();
            // 遍历查询出的学生对象，提取姓名，年龄，性别信息
            for(Student stu :students){
                Map<String , Object> stuMap = new HashMap<>();
                stuMap.put("name", stu.getName());
                stuMap.put("age", stu.getAge());
                stuMap.put("sex", stu.getSex());
                stuMap.put("address", stu.getAddress());
                stuMap.put("clazzName", stu.getClazz().getName());
                results.add(stuMap);
            }
            return results;
        }
        /**
         * 分页查询某个班级的学生信息
         * @param clazzName 班级名称
         * @param pageIndex 代表当前查询第几页
         * @param pageSize 代表每页查询的最大数据量
         * @return
         */
        @SuppressWarnings("serial")
        public Page<Student> getStusByPage(String clazzName , int pageIndex , int pageSize ) {
            // 指定排序参数对象：根据id，进行降序查询
            Sort sort = new Sort(Sort.Direction.DESC, "id");
            // 分页查询学生信息，返回分页实体对象数据
            // pages对象中包含了查询出来的数据信息，以及与分页相关的信息
            Page<Student> pages = studentRepository.findAll(new Specification<Student>() {
                @Override
                public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
                        CriteriaBuilder cb) {
                    root.join("clazz", JoinType.INNER);
                    Path<String> cn = root.get("clazz").get("name");
                    Predicate p1 = cb.equal(cn, clazzName);
                    return p1 ;
                }
            },PageRequest.of(pageIndex-1, pageSize, sort));
            return pages;
        }
        
    }

**六 控制器**

package org.fkit.springdatajpaspecificationtest.controller;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    import javax.annotation.Resource;
    
    
    import org.fkit.springdatajpaspecificationtest.bean.Clazz;
    import org.fkit.springdatajpaspecificationtest.bean.Student;
    import org.fkit.springdatajpaspecificationtest.service.ShcoolService;
    import org.fkit.springdatajpaspecificationtest.vo.PageData;
    import org.springframework.data.domain.Page;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    
    @RestController
    @RequestMapping("/student")
    public class StudentController {
        
        // 注入ShcoolService
        @Resource
        private ShcoolService shcoolService;
        @RequestMapping("/save")
        public String save() {
            
            Clazz clazz1 = new Clazz("疯狂java开发1班");
            Clazz clazz2 = new Clazz("疯狂java开发2班");
            // 保存班级对象数据
            List<Clazz> clazzs = new ArrayList<>();
            clazzs.add(clazz1);
            clazzs.add(clazz2);
            shcoolService.saveClazzAll(clazzs);
            
            Student swk = new Student("孙悟空","花果山",700,'男',clazz1);
            Student zx = new Student("紫霞仙子","盘丝洞",500,'女',clazz1);
            Student zzb = new Student("至尊宝","广州",500,'男',clazz1);
            Student tsgz = new Student("铁扇公主","火焰山",500,'女',clazz2);
            Student nmw = new Student("牛魔王","广州",500,'男',clazz2);
            Student zzj = new Student("蜘蛛精","广州",700,'女',clazz2);
            
            List<Student> students = new ArrayList<>();
            students.add(swk);
            students.add(zx);
            students.add(zzb);
            students.add(tsgz);
            students.add(nmw);
            students.add(zzj);
            shcoolService.saveStudentAll(students);
            return "保存学生对象成功";
        }
    
    
        @RequestMapping("/getStusBySex")
        public List<Map<String, Object>> getStusBySex(char sex){
            return shcoolService.getStusBySex(sex);
        }
        // 动态的查询学生信息
        // 可以根据学生对象的姓名(模糊匹配)， 地址查询(模糊匹配),性别,班级查询学生信息
        @RequestMapping("/getStusByDynamic")
        public List<Map<String, Object>> getStusByDynamic(Student student) {
            return shcoolService.getStusByDynamic(student);
        }
        // 分页查询某个班级下的学生信息
        @RequestMapping("/getStusByPage")
        public PageData getStusByPage(String clazzName , int pageIndex , int pageSize ) {
            // 分页查询某个班级的学生信息
            Page<Student> page = shcoolService.getStusByPage(clazzName , pageIndex , pageSize);
            // 对查询出来的结果数据进行分析
            List<Student> students = page.getContent();
            List<Map<String,Object>> stuDatas = new ArrayList<>();
            for(Student stu :students){
                Map<String , Object> stuMap = new HashMap<>();
                stuMap.put("id", stu.getId());
                stuMap.put("name", stu.getName());
                stuMap.put("age", stu.getAge());
                stuMap.put("sex", stu.getSex());
                stuMap.put("address", stu.getAddress());
                stuMap.put("clazzName", clazzName);
                stuDatas.add(stuMap);
            }
            // 将分页查询出的结果数据进行分析，然后把数据存入到PageData对象中去保存起来响应给浏览器展示
            PageData data = new PageData();
            data.setStuDatas(stuDatas);
            data.setPageIndex(page.getNumber()+1);
            data.setPageSize(page.getTotalPages());
            data.setTotalCount(page.getTotalElements());
            data.setPageNum(page.getSize());
            return data ;
        }
    }

**七 VO**

package org.fkit.springdatajpaspecificationtest.vo;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public class PageData {
        // 定义一个变量用于存放当前页码
        private int pageIndex;
        // 定义一个变量用于保存满足查询条件下用于分页的数据总量
        private long totalCount  ;
        // 定义一个变量用于保存当前条件下总共可以分的总页数
        private int pageSize ;
        // 定义一个变量用于保存当前页码查询出的数据总量
        private int pageNum;
        // 定义一个变量用于保存当前查询出来的学生信息
        private List<Map<String,Object>> stuDatas = new ArrayList<>();
        public int getPageSize() {
            return pageSize;
        }
        public void setPageSize(int pageSize) {
            this.pageSize = pageSize;
        }
        public int getPageIndex() {
            return pageIndex;
        }
        public void setPageIndex(int pageIndex) {
            this.pageIndex = pageIndex;
        }
        public long getTotalCount() {
            return totalCount;
        }
        public void setTotalCount(long totalCount) {
            this.totalCount = totalCount;
        }
        public int getPageNum() {
            return pageNum;
        }
        public void setPageNum(int pageNum) {
            this.pageNum = pageNum;
        }
        public List<Map<String, Object>> getStuDatas() {
            return stuDatas;
        }
        public void setStuDatas(List<Map<String, Object>> stuDatas) {
            this.stuDatas = stuDatas;
        }
    }

**八 配置文件**

########################################################
    ### \u6570\u636E\u6E90\u4FE1\u606F\u914D\u7F6E
    ########################################################
    # \u6570\u636E\u5E93\u5730\u5740
    spring.datasource.url=jdbc:mysql://localhost:3306/springdatajpaspecification
    # \u7528\u6237\u540D
    spring.datasource.username=root
    # \u5BC6\u7801
    spring.datasource.password=
    # \u6570\u636E\u5E93\u9A71\u52A8
    spring.datasource.driverClassName=com.mysql.jdbc.Driver
    #  \u6307\u5B9A\u8FDE\u63A5\u6C60\u4E2D\u6700\u5927\u7684\u6D3B\u8DC3\u8FDE\u63A5\u6570.
    spring.datasource.max-active=20
    #  \u6307\u5B9A\u8FDE\u63A5\u6C60\u6700\u5927\u7684\u7A7A\u95F2\u8FDE\u63A5\u6570\u91CF.
    spring.datasource.max-idle=8
    #  \u6307\u5B9A\u5FC5\u987B\u4FDD\u6301\u8FDE\u63A5\u7684\u6700\u5C0F\u503C
    spring.datasource.min-idle=8
    #  \u6307\u5B9A\u542F\u52A8\u8FDE\u63A5\u6C60\u65F6\uFF0C\u521D\u59CB\u5EFA\u7ACB\u7684\u8FDE\u63A5\u6570\u91CF
    spring.datasource.initial-size=10
    ########################################################
    ### JPA\u6301\u4E45\u5316\u914D\u7F6E
    ########################################################
    # \u6307\u5B9A\u6570\u636E\u5E93\u7684\u7C7B\u578B
    spring.jpa.database=MySQL
    #  \u6307\u5B9A\u662F\u5426\u9700\u8981\u5728\u65E5\u5FD7\u4E2D\u663E\u793Asql\u8BED\u53E5
    spring.jpa.show-sql=true
    #  \u6307\u5B9A\u81EA\u52A8\u521B\u5EFA|\u66F4\u65B0|\u9A8C\u8BC1\u6570\u636E\u5E93\u8868\u7ED3\u6784\u7B49\u914D\u7F6E\uFF0C\u914D\u7F6E\u6210update
    #  \u8868\u793A\u5982\u679C\u6570\u636E\u5E93\u4E2D\u5B58\u5728\u6301\u4E45\u5316\u7C7B\u5BF9\u5E94\u7684\u8868\u5C31\u4E0D\u521B\u5EFA\uFF0C\u4E0D\u5B58\u5728\u5C31\u521B\u5EFA\u5BF9\u5E94\u7684\u8868
    spring.jpa.hibernate.ddl-auto=update
    # Naming strategy
    # \u6307\u5B9A\u547D\u540D\u7B56\u7565
    spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
    # \u6307\u5B9A\u6570\u636E\u5E93\u65B9\u8A00
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

**九 创建数据库springdatajpaspecification**

**十 测试**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/660f8ca205994f3aa5c27bbb915ec72f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 1]: /images/20210724/e1aa025ab9554f208bc0532c51fb2a07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 2]: /images/20210724/95eb665a2ee6479e918a5341d6411d30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70 3]: /images/20210724/8a5394f048c44f5f8b0d2beb814e064e.png