前言
继上次SpringData-JPA之ExampleMatcher实例查询使用一会之后发现ExampleMatcher对日期的查询特别糟糕,所以才有了Specification查询的研究。
- 20200114:更新对
JpaSpecificationExecutor
的解析,Specification思路2
,以及CriteriaBuilder +CriteriaQuery+Predicate+TypedQuery
查询部分 - 20180811:根据所学所用,重新更新了文章,并增加了Pageable
分页排序
功能。
实现
对应的Repository需要实现JpaSpecificationExecutor接口
public interface EventRepository extends JpaRepository<Event, Integer> , JpaSpecificationExecutor<Event>{
Specification与Controller业务逻辑
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
//分页
//Pageable是接口,PageRequest是接口实现,new PageRequest()是旧方法,PageRequest.of()是新方法
//PageRequest.of的对象构造函数有多个,page是页数,初始值是0,size是查询结果的条数,后两个参数参考Sort对象的构造方法
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification查询构造器
Specification<Event> specification=new Specification<Event>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Event> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
Predicate condition1 = null;
if(StringUtils.isNotBlank(eventTitle)) {
condition1 = criteriaBuilder.like(root.get("eventTitle"),"%"+eventTitle+"%");
}else {
condition1 = criteriaBuilder.like(root.get("eventTitle"),"%%");
}
Predicate condition2 = null;
if(registerTime!=null) {
condition2 = criteriaBuilder.greaterThan(root.get("registerTime"), registerTime);
}else {
condition2 = criteriaBuilder.greaterThan(root.get("registerTime"), new Timestamp(1514736000000L));
}
//Predicate conditionX=criteriaBuilder.and(condition1,condition2);
//query.where(conditionX);
query.where(condition1,condition2);
//query.where(getPredicates(condition1,condition2)); //这里可以设置任意条查询条件
return null; //这种方式使用JPA的API设置了查询条件,所以不需要再返回查询条件Predicate给Spring Data Jpa,故最后return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
ApiReturnUtil.page封装
其实这个大家根据自己的项目自己封装
,这部分不作为核心内容,知识之前有部分网友比较纠结这个工具,所以简单放出来参考一下.
public static ApiReturnObject page(Page returnObject) {
return new ApiReturnObject(returnObject.getNumber()+"",returnObject.getNumberOfElements()+"",returnObject.getTotalElements()+"",returnObject.getTotalPages()+"","00","success",returnObject.getContent());
}
ApiReturnObject的主要内容:
String errorCode="00";
Object errorMessage;
Object returnObject;
String pageNumber;
String pageSize;
String totalElements;
String totalPages;
public ApiReturnObject(String pageNumber,String pageSize,String totalElements,String totalPages,String errorCode, Object errorMessage, Object returnObject) {
super();
this.pageNumber = pageNumber;
this.errorCode = errorCode;
this.errorMessage = errorMessage;
this.returnObject = returnObject;
this.pageSize = pageSize;
this.totalElements = totalElements;
this.totalPages = totalPages;
}
查询效果
返回对象有用的是pageNumber、pageSize、totalElements、totalPages等属性,可对其进行封装
{
"errorCode": "00",
"errorMessage": "success",
"pageNumber": "1",
"pageSize": "2",
"returnObject": [
{
"eventTitle": "1111",
"id": 3,
"registerTime": 1528702813000,
"status": "0"
},
{
"eventTitle": "小明失踪",
"id": 2,
"registerTime": 1526268436000,
"status": "0"
}
],
"totalElements": "5",
"totalPages": "3"
}
可以查询了。网上关于这个的资料也很少。希望可以帮到大家。
可能遇到的错误
Unable to locate Attribute with the the given name [event] on this ManagedType [org.microservice.tcbj.yytsg.checkcentersys.entity.Event]
出现这样的情况,一般是因为实体类中没有这个属性,例如我Event的是eventTitle,写成了event,就会报错。
JpaSpecificationExecutor接口
20200114补充
JPA 提供动态接口JpaSpecificationExecutor,利用类型检查的方式,利用Specification进行复杂的条件查询,比自己写 SQL 更加便捷和安全.
public interface JpaSpecificationExecutor<T> {
/**
* Returns a single entity matching the given {@link Specification}.
*
* @param spec
* @return
*/
T findOne(Specification<T> spec);
/**
* Returns all entities matching the given {@link Specification}.
*
* @param spec
* @return
*/
List<T> findAll(Specification<T> spec);
/**
* Returns a {@link Page} of entities matching the given {@link Specification}.
*
* @param spec
* @param pageable
* @return
*/
Page<T> findAll(Specification<T> spec, Pageable pageable);
/**
* Returns all entities matching the given {@link Specification} and {@link Sort}.
*
* @param spec
* @param sort
* @return
*/
List<T> findAll(Specification<T> spec, Sort sort);
/**
* Returns the number of instances that the given {@link Specification} will return.
*
* @param spec the {@link Specification} to count instances for
* @return the number of instances
*/
long count(Specification<T> spec);
}
Specification
Specification是我们传入进去的查询参数,是一个接口,并且只有一个方法
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
}
一个一目了然的方法
第二个实现思路
,听说这个方法已经过时了,其实这个方法是最好理解的.这里附上作为思路参考.
public Page<Even> findAll(SearchEven even) {
Specification<Even> specification = new Specifications<Even>()
.eq(StringUtils.isNotBlank(even.getId()), "id", even.getId())
.gt(Objects.nonNull(even.getStatus()), "status", 0)
.between("registerTime", new Range<>(new Date()-1, new Date()))
.like("eventTitle", "%"+even.getEventTitle+"%")
.build();
return personRepository.findAll(specification, new PageRequest(0, 15));
}
Criteria+TypedQuery
思路三
,利用EntityManager相关的CriteriaBuilder +CriteriaQuery+Predicate+TypedQuery进行查询.
@PersistenceContext
private EntityManager em;
/**
* CriteriaBuilder 安全查询创建工厂,创建CriteriaQuery,创建查询具体具体条件Predicate
* @author zhengkai.blog.csdn.net
*/
@Override
public List<Even> list(Even even) {
//查询工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//查询类
CriteriaQuery<Even> query = cb.createQuery(Even.class);
//查询条件
List<Predicate> predicates = new LinkedList<>();
//查询条件设置
predicates.add(cb.equal("id", even.getId()));
predicates.add(cb.like("eventTitle", even.getEventTitle()));
//拼接where查询
query.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));
//用JPA 2.0的TypedQuery进行查询
TypedQuery<Even> typedQuery = em.createQuery(query);
return typedQuery.getResultList();
}