Specification对象
Specification对象是查询条件对象,需要自定义自己的 Specification实现类,需要实现如下的方法:
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
说明:root 是要查询对象的实体,可以理解为需要查询的表,query 是查询表达式的载体, criteriaBuilder 是 query 的工厂方法
JpaSpecificationExecutor接口中功能
方法 | 说明 |
---|---|
T findOne(Specification<T> spec); | 查询单个对象 |
List<T> findAll(Specification<T> spec); | 查询列表 |
Page<T> findAll(Specification<T> spec, Pageable pageable); | 分页查询列表 |
List<T> findAll(Specification<T> spec, Sort sort); | 排序查询列表 |
long count(Specification<T> spec); | 统计查询 |
Spring Data JPA 动态SQL步骤
- 编写Entity层
例:在com.rx.entity包中创建一个User类,如下:
package com.rx.entity;
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
@Entity
@Table(name = "user") // 关联数据库表
@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })//标不需要转化为json的属性
public class User implements Serializable{
private static final long serialVersionUID = 1L;
@Id // 主键字段
@GeneratedValue(strategy = GenerationType.IDENTITY) // 字段自动递增
private Integer id;
private String name;
private String password;
private Integer age;
private String birthday;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(Integer id, String name, String password, Integer age, String birthday) {
super();
this.id = id;
this.name = name;
this.password = password;
this.age = age;
this.birthday = birthday;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password + ", age=" + age + ", birthday="
+ birthday + "]";
}
}
- 编写Dto层
例:在com.rx.dto包中创建一个UserDto类,如下:
package com.rx.dto;
import java.io.Serializable;
import org.springframework.stereotype.Component;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
@Component
@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })//标不需要转化为json的属性
public class UserDto implements Serializable {
private static final long serialVersionUID = 1L;
private String name;
private int minAge;
private int maxAge;
private int page;
private int limit;
private String minBirthday;
private String maxBirthday;
public UserDto() {
super();
// TODO Auto-generated constructor stub
}
public UserDto(String name, int minAge, int maxAge, int page, int limit, String minBirthday,
String maxBirthday) {
super();
this.name = name;
this.minAge = minAge;
this.maxAge = maxAge;
this.page = page;
this.limit = limit;
this.minBirthday = minBirthday;
this.maxBirthday = maxBirthday;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getMinAge() {
return minAge;
}
public void setMinAge(int minAge) {
this.minAge = minAge;
}
public int getMaxAge() {
return maxAge;
}
public void setMaxAge(int maxAge) {
this.maxAge = maxAge;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getLimit() {
return limit;
}
public void setLimit(int limit) {
this.limit = limit;
}
public String getMinBirthday() {
return minBirthday;
}
public void setMinBirthday(String minBirthday) {
this.minBirthday = minBirthday;
}
public String getMaxBirthday() {
return maxBirthday;
}
public void setMaxBirthday(String maxBirthday) {
this.maxBirthday = maxBirthday;
}
@Override
public String toString() {
return "UserDto [name=" + name + ", minAge=" + minAge + ", maxAge=" + maxAge + ", page=" + page + ", limit="
+ limit + ", minBirthday=" + minBirthday + ", maxBirthday=" + maxBirthday + "]";
}
}
- 编写Repository层(Dao层)
例:在com.rx.repository包中创建一个UserRepository接口,如下:
package com.rx.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.rx.entity.User;
/**
* 该接口需要继承JpaRepository<T, ID>、JpaSpecificationExecutor<T>这两个接口
* T:实体类、ID:实体类的主键数据类型
*
*/
public interface UserRepository extends JpaRepository<User, Integer>,JpaSpecificationExecutor<User> {
}
- 编写Service层
例:在com.rx.service包中创建一个UserService接口,如下:
package com.rx.service;
import org.springframework.data.domain.Page;
import com.rx.dto.UserDto;
import com.rx.entity.User;
public interface UserService {
/**
* @param dto:查询条件
* @return 分页的用户列表
*/
Page<User> selectDynamic(UserDto dto);
}
例:在com.rx.service包中创建一个UserServiceImp接口实现类,如下:
package com.rx.service;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import com.rx.dto.UserDto;
import com.rx.entity.User;
import com.rx.repository.UserRepository;
/**
* 该类用来实现Service层接口的方法
*
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepository;
@Override
public Page<User> selectDynamic(final UserDto dto) {
//分页查询 springBoot2.0以下版本用new 实例化 、2.0以上版本使用的pageRequest
Pageable pageable = PageRequest.of(dto.getPage()-1, dto.getLimit());
// 实例化Specification对象
Specification<User> specification = new Specification<User>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
// 动态 SQL表达式
Predicate predicate = criteriaBuilder.conjunction();
// 动态 SQL表达式集合
List<Expression<Boolean>> expressions = predicate.getExpressions();
// 根据条件拼接SQL
if(dto.getName()!=null && dto.getName()!="") {
expressions.add(criteriaBuilder.like(root.get("name").as(String.class),"%"+dto.getName()+"%"));
}
if(dto.getMinAge()>0) {
expressions.add(criteriaBuilder.greaterThanOrEqualTo(root.get("age").as(Integer.class), dto.getMinAge()));
}
if(dto.getMaxAge()>0) {
expressions.add(criteriaBuilder.lessThanOrEqualTo(root.get("age").as(Integer.class), dto.getMaxAge()));
}
return predicate;
}
};
return userRepository.findAll(specification, pageable);
}
}
- 编写Controller层
例:在com.rx.controller包中创建一个UserControler类,如下:
package com.rx.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.rx.dto.UserDto;
import com.rx.entity.User;
import com.rx.service.UserService;
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/selectDynamic")
public Page<User> selectDynamic(final UserDto dto){
dto.setPage(1); //设置分页的页数
dto.setLimit(10); //设置分页的条数
return userService.selectDynamic(dto);
}
}
- 编写SpringBoot的启动类
例:在com.rx包中创建一个App类,并添加@EnableJpaRepositories
注解,如下:
package com.rx;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
@EnableJpaRepositories
@SpringBootApplication
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
}
- 启动SpringBoot项目测试即可