Specification查询
一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>
<!-- spring-boot-starter-parent是Spring Boot的核心启动器, 包含了自动配置、日志和YAML等大量默认的配置,大大简化了我们的开发。
引入之后相关的starter引入就不需要添加version配置, spring boot会自动选择最合适的版本进行添加。 -->
<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>
<!-- 添加spring-boot-starter-web模块依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 添加spring-boot-starter-thymeleaf模块依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!-- 添加MySQL依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 添加Spring Data JPA依赖 -->
<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
十 测试
还没有评论,来说两句吧...