Specification查询

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

一pom

  1. <project xmlns="http://maven.apache.org/POM/4.0.0"
  2. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <modelVersion>4.0.0</modelVersion>
  5. <groupId>org.fkit</groupId>
  6. <artifactId>springdatajpaspecificationtest</artifactId>
  7. <version>0.0.1-SNAPSHOT</version>
  8. <packaging>jar</packaging>
  9. <name>springdatajpaspecificationtest</name>
  10. <url>http://maven.apache.org</url>
  11. <!-- spring-boot-starter-parent是Spring Boot的核心启动器, 包含了自动配置、日志和YAML等大量默认的配置,大大简化了我们的开发。
  12. 引入之后相关的starter引入就不需要添加version配置, spring boot会自动选择最合适的版本进行添加。 -->
  13. <parent>
  14. <groupId>org.springframework.boot</groupId>
  15. <artifactId>spring-boot-starter-parent</artifactId>
  16. <version>2.0.0.RELEASE</version>
  17. <relativePath />
  18. </parent>
  19. <properties>
  20. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  21. <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  22. <java.version>1.8</java.version>
  23. </properties>
  24. <dependencies>
  25. <!-- 添加spring-boot-starter-web模块依赖 -->
  26. <dependency>
  27. <groupId>org.springframework.boot</groupId>
  28. <artifactId>spring-boot-starter-web</artifactId>
  29. </dependency>
  30. <!-- 添加spring-boot-starter-thymeleaf模块依赖 -->
  31. <dependency>
  32. <groupId>org.springframework.boot</groupId>
  33. <artifactId>spring-boot-starter-thymeleaf</artifactId>
  34. </dependency>
  35. <!-- 添加MySQL依赖 -->
  36. <dependency>
  37. <groupId>mysql</groupId>
  38. <artifactId>mysql-connector-java</artifactId>
  39. </dependency>
  40. <!-- 添加Spring Data JPA依赖 -->
  41. <dependency>
  42. <groupId>org.springframework.boot</groupId>
  43. <artifactId>spring-boot-starter-data-jpa</artifactId>
  44. </dependency>
  45. <dependency>
  46. <groupId>junit</groupId>
  47. <artifactId>junit</artifactId>
  48. <scope>test</scope>
  49. </dependency>
  50. </dependencies>
  51. </project>

二 启动类

  1. package org.fkit.springdatajpaspecificationtest;
  2. import org.springframework.boot.SpringApplication;
  3. import org.springframework.boot.autoconfigure.SpringBootApplication;
  4. @SpringBootApplication
  5. public class App
  6. {
  7. public static void main( String[] args )
  8. {
  9. // SpringApplication 用于从main方法启动Spring应用的类。
  10. SpringApplication.run(App.class, args);
  11. }
  12. }

三 实体类

1 Clazz

  1. package org.fkit.springdatajpaspecificationtest.bean;
  2. import java.io.Serializable;
  3. import java.util.HashSet;
  4. import java.util.Set;
  5. import javax.persistence.Entity;
  6. import javax.persistence.FetchType;
  7. import javax.persistence.GeneratedValue;
  8. import javax.persistence.GenerationType;
  9. import javax.persistence.Id;
  10. import javax.persistence.OneToMany;
  11. import javax.persistence.Table;
  12. @Entity
  13. @Table(name="tb_clazz")
  14. public class Clazz implements Serializable{
  15. private static final long serialVersionUID = 1L;
  16. @Id
  17. @GeneratedValue(strategy=GenerationType.IDENTITY)
  18. private int code ;
  19. private String name ;
  20. // 班级与学生是一对多的关联
  21. @OneToMany(
  22. fetch=FetchType.LAZY,
  23. targetEntity=Student.class,
  24. mappedBy="clazz"
  25. )
  26. private Set<Student> students = new HashSet<>();
  27. public Clazz() {
  28. }
  29. // 班级对象
  30. public Clazz(String name) {
  31. this.name = name;
  32. }
  33. public int getCode() {
  34. return code;
  35. }
  36. public void setCode(int code) {
  37. this.code = code;
  38. }
  39. public String getName() {
  40. return name;
  41. }
  42. public void setName(String name) {
  43. this.name = name;
  44. }
  45. public Set<Student> getStudents() {
  46. return students;
  47. }
  48. public void setStudents(Set<Student> students) {
  49. this.students = students;
  50. }
  51. }

2 Student

  1. package org.fkit.springdatajpaspecificationtest.bean;
  2. import java.io.Serializable;
  3. import javax.persistence.Entity;
  4. import javax.persistence.FetchType;
  5. import javax.persistence.GeneratedValue;
  6. import javax.persistence.GenerationType;
  7. import javax.persistence.Id;
  8. import javax.persistence.JoinColumn;
  9. import javax.persistence.ManyToOne;
  10. import javax.persistence.Table;
  11. @Entity
  12. @Table(name="tb_student")
  13. public class Student implements Serializable{
  14. private static final long serialVersionUID = 1L;
  15. @Id
  16. @GeneratedValue(strategy = GenerationType.IDENTITY)
  17. private int id;
  18. private String name ;
  19. private String address ;
  20. private int age ;
  21. private char sex;
  22. // 学生与班级是多对一的关系,这里配置的是双向关联
  23. @ManyToOne(fetch=FetchType.LAZY,
  24. targetEntity=Clazz.class
  25. )
  26. @JoinColumn(name="clazzId",referencedColumnName="code")
  27. private Clazz clazz ;
  28. public Student() {
  29. }
  30. public Student(String name, String address, int age, char sex,
  31. Clazz clazz) {
  32. super();
  33. this.name = name;
  34. this.address = address;
  35. this.age = age;
  36. this.sex = sex;
  37. this.clazz = clazz;
  38. }
  39. public int getId() {
  40. return id;
  41. }
  42. public void setId(int id) {
  43. this.id = id;
  44. }
  45. public String getName() {
  46. return name;
  47. }
  48. public void setName(String name) {
  49. this.name = name;
  50. }
  51. public String getAddress() {
  52. return address;
  53. }
  54. public void setAddress(String address) {
  55. this.address = address;
  56. }
  57. public int getAge() {
  58. return age;
  59. }
  60. public void setAge(int age) {
  61. this.age = age;
  62. }
  63. public char getSex() {
  64. return sex;
  65. }
  66. public void setSex(char sex) {
  67. this.sex = sex;
  68. }
  69. public Clazz getClazz() {
  70. return clazz;
  71. }
  72. public void setClazz(Clazz clazz) {
  73. this.clazz = clazz;
  74. }
  75. }

四 Repository

1 ClazzRepository

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

2 StudentRepository

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

五 Service

  1. package org.fkit.springdatajpaspecificationtest.service;
  2. import java.util.ArrayList;
  3. import java.util.HashMap;
  4. import java.util.List;
  5. import java.util.Map;
  6. import javax.annotation.Resource;
  7. import javax.persistence.criteria.CriteriaBuilder;
  8. import javax.persistence.criteria.CriteriaQuery;
  9. import javax.persistence.criteria.JoinType;
  10. import javax.persistence.criteria.Path;
  11. import javax.persistence.criteria.Predicate;
  12. import javax.persistence.criteria.Root;
  13. import org.fkit.springdatajpaspecificationtest.bean.Clazz;
  14. import org.fkit.springdatajpaspecificationtest.bean.Student;
  15. import org.fkit.springdatajpaspecificationtest.repository.ClazzRepository;
  16. import org.fkit.springdatajpaspecificationtest.repository.StudentRepository;
  17. import org.springframework.data.domain.Page;
  18. import org.springframework.data.domain.PageRequest;
  19. import org.springframework.data.domain.Sort;
  20. import org.springframework.data.jpa.domain.Specification;
  21. import org.springframework.stereotype.Service;
  22. import org.springframework.transaction.annotation.Transactional;
  23. import org.springframework.util.StringUtils;
  24. @Service
  25. public class ShcoolService {
  26. // 注入数据访问层接口对象
  27. @Resource
  28. private StudentRepository studentRepository;
  29. @Resource
  30. private ClazzRepository clazzRepository;
  31. @Transactional
  32. public void saveClazzAll(List<Clazz> clazzs) {
  33. clazzRepository.saveAll(clazzs);
  34. }
  35. @Transactional
  36. public void saveStudentAll(List<Student> students) {
  37. studentRepository.saveAll(students);
  38. }
  39. /**
  40. * 根据性别查询学生信息
  41. * @param clazzName
  42. * @return
  43. */
  44. @SuppressWarnings("serial")
  45. public List<Map<String, Object>> getStusBySex(char sex) {
  46. List<Student> students = studentRepository.findAll(new Specification<Student>() {
  47. @Override
  48. public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
  49. CriteriaBuilder cb) {
  50. // root.get("sex")表示获取sex这个字段名称,equal表示执行equal查询
  51. // 相当于 select s from Student s where s.sex = ?1
  52. Predicate p1 = cb.equal(root.get("sex"), sex);
  53. return p1;
  54. }
  55. });
  56. List<Map<String, Object>> results = new ArrayList<>();
  57. // 遍历查询出的学生对象,提取姓名,年龄,性别信息
  58. for(Student student:students){
  59. Map<String , Object> stu = new HashMap<>();
  60. stu.put("name", student.getName());
  61. stu.put("age", student.getAge());
  62. stu.put("sex", student.getSex());
  63. results.add(stu);
  64. }
  65. return results;
  66. }
  67. /**
  68. * 动态查询学生信息 :可以根据学生对象的姓名(模糊匹配), 地址查询(模糊匹配),性别,班级查询学生信息
  69. * 如果没有传输参数,默认查询所有的学生信息
  70. * @param clazzName
  71. * @return
  72. */
  73. @SuppressWarnings("serial")
  74. public List<Map<String, Object>> getStusByDynamic(Student student) {
  75. List<Student> students = studentRepository.findAll(new Specification<Student>() {
  76. @Override
  77. public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
  78. CriteriaBuilder cb) {
  79. // 本集合用于封装查询条件
  80. List<Predicate> predicates = new ArrayList<Predicate>();
  81. if(student!=null){
  82. /** 是否传入了姓名来查询 */
  83. if(!StringUtils.isEmpty(student.getName())){
  84. predicates.add(cb.like(root.<String> get("name"),"%" + student.getName() + "%"));
  85. }
  86. /** 是否传入了地址来查询 */
  87. if(!StringUtils.isEmpty(student.getAddress())){
  88. predicates.add(cb.like(root.<String> get("address"),"%" + student.getAddress() + "%"));
  89. }
  90. /** 是否传入了性别来查询 */
  91. if(student.getSex() != '\0'){
  92. predicates.add(cb.equal(root.<String> get("sex"),student.getSex()));
  93. }
  94. /** 判断是否传入了班级信息来查询 */
  95. if(student.getClazz()!=null && !StringUtils.isEmpty(student.getClazz().getName())){
  96. root.join("clazz", JoinType.INNER);
  97. Path<String> clazzName = root.get("clazz").get("name");
  98. predicates.add(cb.equal(clazzName, student.getClazz().getName()));
  99. }
  100. }
  101. return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
  102. }
  103. });
  104. List<Map<String, Object>> results = new ArrayList<>();
  105. // 遍历查询出的学生对象,提取姓名,年龄,性别信息
  106. for(Student stu :students){
  107. Map<String , Object> stuMap = new HashMap<>();
  108. stuMap.put("name", stu.getName());
  109. stuMap.put("age", stu.getAge());
  110. stuMap.put("sex", stu.getSex());
  111. stuMap.put("address", stu.getAddress());
  112. stuMap.put("clazzName", stu.getClazz().getName());
  113. results.add(stuMap);
  114. }
  115. return results;
  116. }
  117. /**
  118. * 分页查询某个班级的学生信息
  119. * @param clazzName 班级名称
  120. * @param pageIndex 代表当前查询第几页
  121. * @param pageSize 代表每页查询的最大数据量
  122. * @return
  123. */
  124. @SuppressWarnings("serial")
  125. public Page<Student> getStusByPage(String clazzName , int pageIndex , int pageSize ) {
  126. // 指定排序参数对象:根据id,进行降序查询
  127. Sort sort = new Sort(Sort.Direction.DESC, "id");
  128. // 分页查询学生信息,返回分页实体对象数据
  129. // pages对象中包含了查询出来的数据信息,以及与分页相关的信息
  130. Page<Student> pages = studentRepository.findAll(new Specification<Student>() {
  131. @Override
  132. public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> query,
  133. CriteriaBuilder cb) {
  134. root.join("clazz", JoinType.INNER);
  135. Path<String> cn = root.get("clazz").get("name");
  136. Predicate p1 = cb.equal(cn, clazzName);
  137. return p1 ;
  138. }
  139. },PageRequest.of(pageIndex-1, pageSize, sort));
  140. return pages;
  141. }
  142. }

六 控制器

  1. package org.fkit.springdatajpaspecificationtest.controller;
  2. import java.util.ArrayList;
  3. import java.util.HashMap;
  4. import java.util.List;
  5. import java.util.Map;
  6. import javax.annotation.Resource;
  7. import org.fkit.springdatajpaspecificationtest.bean.Clazz;
  8. import org.fkit.springdatajpaspecificationtest.bean.Student;
  9. import org.fkit.springdatajpaspecificationtest.service.ShcoolService;
  10. import org.fkit.springdatajpaspecificationtest.vo.PageData;
  11. import org.springframework.data.domain.Page;
  12. import org.springframework.web.bind.annotation.RequestMapping;
  13. import org.springframework.web.bind.annotation.RestController;
  14. @RestController
  15. @RequestMapping("/student")
  16. public class StudentController {
  17. // 注入ShcoolService
  18. @Resource
  19. private ShcoolService shcoolService;
  20. @RequestMapping("/save")
  21. public String save() {
  22. Clazz clazz1 = new Clazz("疯狂java开发1班");
  23. Clazz clazz2 = new Clazz("疯狂java开发2班");
  24. // 保存班级对象数据
  25. List<Clazz> clazzs = new ArrayList<>();
  26. clazzs.add(clazz1);
  27. clazzs.add(clazz2);
  28. shcoolService.saveClazzAll(clazzs);
  29. Student swk = new Student("孙悟空","花果山",700,'男',clazz1);
  30. Student zx = new Student("紫霞仙子","盘丝洞",500,'女',clazz1);
  31. Student zzb = new Student("至尊宝","广州",500,'男',clazz1);
  32. Student tsgz = new Student("铁扇公主","火焰山",500,'女',clazz2);
  33. Student nmw = new Student("牛魔王","广州",500,'男',clazz2);
  34. Student zzj = new Student("蜘蛛精","广州",700,'女',clazz2);
  35. List<Student> students = new ArrayList<>();
  36. students.add(swk);
  37. students.add(zx);
  38. students.add(zzb);
  39. students.add(tsgz);
  40. students.add(nmw);
  41. students.add(zzj);
  42. shcoolService.saveStudentAll(students);
  43. return "保存学生对象成功";
  44. }
  45. @RequestMapping("/getStusBySex")
  46. public List<Map<String, Object>> getStusBySex(char sex){
  47. return shcoolService.getStusBySex(sex);
  48. }
  49. // 动态的查询学生信息
  50. // 可以根据学生对象的姓名(模糊匹配), 地址查询(模糊匹配),性别,班级查询学生信息
  51. @RequestMapping("/getStusByDynamic")
  52. public List<Map<String, Object>> getStusByDynamic(Student student) {
  53. return shcoolService.getStusByDynamic(student);
  54. }
  55. // 分页查询某个班级下的学生信息
  56. @RequestMapping("/getStusByPage")
  57. public PageData getStusByPage(String clazzName , int pageIndex , int pageSize ) {
  58. // 分页查询某个班级的学生信息
  59. Page<Student> page = shcoolService.getStusByPage(clazzName , pageIndex , pageSize);
  60. // 对查询出来的结果数据进行分析
  61. List<Student> students = page.getContent();
  62. List<Map<String,Object>> stuDatas = new ArrayList<>();
  63. for(Student stu :students){
  64. Map<String , Object> stuMap = new HashMap<>();
  65. stuMap.put("id", stu.getId());
  66. stuMap.put("name", stu.getName());
  67. stuMap.put("age", stu.getAge());
  68. stuMap.put("sex", stu.getSex());
  69. stuMap.put("address", stu.getAddress());
  70. stuMap.put("clazzName", clazzName);
  71. stuDatas.add(stuMap);
  72. }
  73. // 将分页查询出的结果数据进行分析,然后把数据存入到PageData对象中去保存起来响应给浏览器展示
  74. PageData data = new PageData();
  75. data.setStuDatas(stuDatas);
  76. data.setPageIndex(page.getNumber()+1);
  77. data.setPageSize(page.getTotalPages());
  78. data.setTotalCount(page.getTotalElements());
  79. data.setPageNum(page.getSize());
  80. return data ;
  81. }
  82. }

七 VO

  1. package org.fkit.springdatajpaspecificationtest.vo;
  2. import java.util.ArrayList;
  3. import java.util.List;
  4. import java.util.Map;
  5. public class PageData {
  6. // 定义一个变量用于存放当前页码
  7. private int pageIndex;
  8. // 定义一个变量用于保存满足查询条件下用于分页的数据总量
  9. private long totalCount ;
  10. // 定义一个变量用于保存当前条件下总共可以分的总页数
  11. private int pageSize ;
  12. // 定义一个变量用于保存当前页码查询出的数据总量
  13. private int pageNum;
  14. // 定义一个变量用于保存当前查询出来的学生信息
  15. private List<Map<String,Object>> stuDatas = new ArrayList<>();
  16. public int getPageSize() {
  17. return pageSize;
  18. }
  19. public void setPageSize(int pageSize) {
  20. this.pageSize = pageSize;
  21. }
  22. public int getPageIndex() {
  23. return pageIndex;
  24. }
  25. public void setPageIndex(int pageIndex) {
  26. this.pageIndex = pageIndex;
  27. }
  28. public long getTotalCount() {
  29. return totalCount;
  30. }
  31. public void setTotalCount(long totalCount) {
  32. this.totalCount = totalCount;
  33. }
  34. public int getPageNum() {
  35. return pageNum;
  36. }
  37. public void setPageNum(int pageNum) {
  38. this.pageNum = pageNum;
  39. }
  40. public List<Map<String, Object>> getStuDatas() {
  41. return stuDatas;
  42. }
  43. public void setStuDatas(List<Map<String, Object>> stuDatas) {
  44. this.stuDatas = stuDatas;
  45. }
  46. }

八 配置文件

  1. ########################################################
  2. ### \u6570\u636E\u6E90\u4FE1\u606F\u914D\u7F6E
  3. ########################################################
  4. # \u6570\u636E\u5E93\u5730\u5740
  5. spring.datasource.url=jdbc:mysql://localhost:3306/springdatajpaspecification
  6. # \u7528\u6237\u540D
  7. spring.datasource.username=root
  8. # \u5BC6\u7801
  9. spring.datasource.password=
  10. # \u6570\u636E\u5E93\u9A71\u52A8
  11. spring.datasource.driverClassName=com.mysql.jdbc.Driver
  12. # \u6307\u5B9A\u8FDE\u63A5\u6C60\u4E2D\u6700\u5927\u7684\u6D3B\u8DC3\u8FDE\u63A5\u6570.
  13. spring.datasource.max-active=20
  14. # \u6307\u5B9A\u8FDE\u63A5\u6C60\u6700\u5927\u7684\u7A7A\u95F2\u8FDE\u63A5\u6570\u91CF.
  15. spring.datasource.max-idle=8
  16. # \u6307\u5B9A\u5FC5\u987B\u4FDD\u6301\u8FDE\u63A5\u7684\u6700\u5C0F\u503C
  17. spring.datasource.min-idle=8
  18. # \u6307\u5B9A\u542F\u52A8\u8FDE\u63A5\u6C60\u65F6\uFF0C\u521D\u59CB\u5EFA\u7ACB\u7684\u8FDE\u63A5\u6570\u91CF
  19. spring.datasource.initial-size=10
  20. ########################################################
  21. ### JPA\u6301\u4E45\u5316\u914D\u7F6E
  22. ########################################################
  23. # \u6307\u5B9A\u6570\u636E\u5E93\u7684\u7C7B\u578B
  24. spring.jpa.database=MySQL
  25. # \u6307\u5B9A\u662F\u5426\u9700\u8981\u5728\u65E5\u5FD7\u4E2D\u663E\u793Asql\u8BED\u53E5
  26. spring.jpa.show-sql=true
  27. # \u6307\u5B9A\u81EA\u52A8\u521B\u5EFA|\u66F4\u65B0|\u9A8C\u8BC1\u6570\u636E\u5E93\u8868\u7ED3\u6784\u7B49\u914D\u7F6E\uFF0C\u914D\u7F6E\u6210update
  28. # \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
  29. spring.jpa.hibernate.ddl-auto=update
  30. # Naming strategy
  31. # \u6307\u5B9A\u547D\u540D\u7B56\u7565
  32. spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
  33. # \u6307\u5B9A\u6570\u636E\u5E93\u65B9\u8A00
  34. 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

发表评论

表情:
评论列表 (有 0 条评论,542人围观)

还没有评论,来说两句吧...

相关阅读

    相关 jpa specification条件查询

    1. 1. [spring][] data jpa 通过创建方法名来做查询,只能做简单的查询,那如果我们要做复杂一些的查询呢,多条件分页怎么办,这里,spring data