SpringDataJPA's Specification Complex Query Practical Practice
- 2021-12-12 08:43:57
- OfStack
Directory SpringDataJPA Specification Complex Query Preface Realization of Specification and Controller Business Logic ApiReturnUtil. page Encapsulation of Query Effect Possible Errors JpaSpecificationExecutor Interface Specification1 Clear Methods Criteria+TypedQuery Application of JPA Specification in Development Process Why Need Specification Application Scenario JPA Specification Implementation of Complex Query JPA Multi-condition Multi-table Query Spring Data Jpa Simple Fuzzy Query
SpringDataJPA Specification Complex Query
Preface
After the last ExampleMatcher instance query of SpringData-JPA was used for 1 session, it was found that ExampleMatcher queries were particularly bad for dates, so the research of Specification queries came into being.
20200114: Update Parsing for JpaSpecificationExecutor, Specification Idea 2, and CriteriaBuilder + CriteriaQuery + Predicate + TypedQuery Query Section 20180811: According to what you have learned, the article has been updated again, and Pageable paging sorting function has been added.
Realization
The corresponding Repository needs to implement the JpaSpecificationExecutor interface
public interface EventRepository extends JpaRepository<Event, Integer> , JpaSpecificationExecutor<Event>{
Specification and Controller Service Logic
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
ApiReturnUtil. page encapsulation
In fact, this is packaged by everyone according to their own projects, and this part is not regarded as the core content. Some netizens are entangled with this tool before knowledge, so simply put it out for reference.
public static ApiReturnObject page(Page returnObject) {
return new ApiReturnObject(returnObject.getNumber()+"",returnObject.getNumberOfElements()+"",returnObject.getTotalElements()+"",returnObject.getTotalPages()+"","00","success",returnObject.getContent());
}Main contents of 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;
}
Query effect
Returning objects are useful properties such as pageNumber, pageSize, totalElements, totalPages that can be encapsulated
{
"errorCode": "00",
"errorMessage": "success",
"pageNumber": "1",
"pageSize": "2",
"returnObject": [
{
"eventTitle": "1111",
"id": 3,
"registerTime": 1528702813000,
"status": "0"
},
{
"eventTitle": " Xiao Ming is missing ",
"id": 2,
"registerTime": 1526268436000,
"status": "0"
}
],
"totalElements": "5",
"totalPages": "3"
}You can inquire. There is also very little information about this on the Internet. I hope I can help everyone.
Possible errors encountered
Unable to locate Attribute with the the given name [event] on this ManagedType [org.microservice.tcbj.yytsg.checkcentersys.entity.Event]
In this case, 1 is generally because there is no such attribute in the entity class. For example, if my Event is eventTitle and I write event, I will report an error.
JpaSpecificationExecutor interface
20200114 Supplement
JPA provides dynamic interface JpaSpecificationExecutor, using type checking, using Specification to query complex conditions, which is more convenient and safe than writing SQL by yourself.
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 is the query parameter we passed in. It is an interface and only has one method
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
}
A method that is clear with one eye
The second realization idea: I heard that this method is outdated, but in fact this method is the best understanding. Attached here as a reference for ideas.
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
Idea 3: Use CriteriaBuilder + CriteriaQuery + Predicate + TypedQuery related to EntityManager to query.
@PersistenceContext
private EntityManager em;
/**
* CriteriaBuilder Secure query creation factory , Create CriteriaQuery, Create query specific conditions Predicate
* @author zhengkai.blog.csdn.net
*/
@Override
public List<Even> list(Even even) {
// Query factory
CriteriaBuilder cb = em.getCriteriaBuilder();
// Query class
CriteriaQuery<Even> query = cb.createQuery(Even.class);
// Query criteria
List<Predicate> predicates = new LinkedList<>();
// Query Criteria Setting
predicates.add(cb.equal("id", even.getId()));
predicates.add(cb.like("eventTitle", even.getEventTitle()));
// Splice where Query
query.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));
// Use JPA 2.0 Adj. TypedQuery Make a query
TypedQuery<Even> typedQuery = em.createQuery(query);
return typedQuery.getResultList();
}
Application of JPA and Specification in Development Process
Specification is JPA more flexible query specification, convenient to achieve complex query.
Why do you need Specification
Spring-Data JPA itself supports a relatively simple query mode, that is, according to the attribute name and combining with some specifications, the query method is written. For example, if an Customer object has name attribute, if you want to query according to name, you only need to add a method findByName (String name) in the interface file.
public interface CustomerRepository extends JpaRepository<Customer, Long> {
Customer findByName(String name);
Customer findByEmailAddress(String emailAddress);
List<Customer> findByLastname(String lastname, Sort sort);
Page<Customer> findByFirstname(String firstname, Pageable pageable);
}However, in many cases, there will be more complex queries, so at this time through the automatic generation of query methods is no longer feasible.
Application scenario
In order to realize complex query, JPA provides Criteria interface, which is a set of standard interfaces. Let's look at an example. In a platform, when an old customer (two years since registration) has a birthday, the system wants to send a coupon to the user, so JPA 2.0 Criteria API is traditionally used to realize it:
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
The main problem here is that the code scalability is poor, because CriteriaBuilder, CriteriaQuery and Root need to be set, and the readability of this part of the code is poor.
JPA Specification Implementation of Complex Queries
Specification in order to achieve reusable assertion, JPA introduced an Specification interface, interface encapsulation is very simple, as follows
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
In Java8, we can very easily achieve the effect achieved with Criteria above
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
Thus, the implementation of repository corresponding to JPA can be as follows
customerRepository.findAll(hasBirthday());
customerRepository.findAll(isLongTermCustomer());What Specification does for us is to prepare CriteriaQuery, Root, CriteriaBuilder, and with these reusable assertions, we can combine them to implement more complex queries
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
JPA multi-condition, multi-table query
If you need to use Specification, the corresponding Repository needs to implement the interface JpaSpecificationExecutor
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
Single table multi-condition query
After combining Spring, Boot and JPA, we can consider using Predicate assertion for line 4 multi-condition query and sorting and paging. For example, for User, we want to make fuzzy query according to different attributes of users. At the same time, if the attribute value is empty or empty string, we skip the attribute and do not serve as a query condition. At the same time, it belongs to single-table multi-condition query, then
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
Multi-table and multi-condition query
In many cases, we will face multi-table and multi-condition queries, and the implementation examples are as follows
@GetMapping("/event/list")
public ApiReturnObject findAllEvent(String eventTitle,Timestamp registerTime,Integer pageNumber,Integer pageSize) {
if(pageNumber==null) pageNumber=1;
if(pageSize==null) pageNumber=10;
// Paging
//Pageable Is an interface, PageRequest Is an interface implementation, new PageRequest() It's the old method, PageRequest.of() It's a new method
//PageRequest.of There are multiple object constructors for, page Is the number of pages, and the initial value is 0 , size Is the number of query results, and the last two parameters refer to Sort Construction method of object
Pageable pageable = PageRequest.of(pageNumber,pageSize,Sort.Direction.DESC,"id");
//Specification Query constructor
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)); // You can set any query criteria here
return null; // Use this way JPA Adj. API Query criteria are set, so there is no need to return query criteria Predicate To Spring Data Jpa , so in the end return null
}
};
Page<Event> list=eventRepository.findAll(specification, pageable);
return ApiReturnUtil.page(list);
}
Spring Data Jpa Simple Fuzzy Query
Under some simple query conditions, you must use Specification interface, such as
@Repository
public interface UserRepository extends CrudRepository<User, Integer> {
/**
* username Fuzzy queries are not supported, deviceNames Support fuzzy query
* @param deviceNames Fuzzy query deviceNames
* @param username User name
* @return {@link List<User>}
*/
List<User> findAllByDeviceNamesContainingAndUsername(String deviceNames,String username);
/**
* Among them username Fuzzy queries are not supported, deviceNames Support fuzzy query
* Incoming deviceNames Need to be added before and after % Otherwise, the result that may be returned is the result of an exact query
* @param deviceNames Fuzzy query deviceNames
* @param username User name
* @return {@link List<User>}
*/
List<User> findAllByDeviceNamesLikeAndUsername(String deviceNames,String username);
}