Dynamic query instances in Spring Data JPA

spring Data JPA greatly simplifies the development of our persistence layer, but in practice, we still need dynamic queries.

For example, the front end has multiple conditions, many of which are optional, so the back end SQL should be customizable. When using hibernate, you can splice SQL(HQL) by judging the conditions. Of course, Spring Data JPA simplifies our development, but it also provides support.

Implementing dynamic queries implemented by Criteria2 requires that our Repo interface inherit the JpaSpecificationExecutor interface, which is a generic interface.

Then when the query, pass in dynamic query parameters, paging parameters and so on.

It's easy to use, but to see why, let's look at Criteria API.

Criteria API

If the compiler can perform syntax correctness checks on the query, then the query is type-safe for Java objects. Java & # 8482; Version 2.0 of Persistence API (JPA) introduces Criteria API, which first introduced type-safe queries into Java applications and provides a mechanism for dynamically constructing queries at run time. This article shows how to write dynamic type-safe queries using Criteria API and its closely related Metamodel API.

When using Spring Data JPA, as long as our Repo layer inherits THE JpaSpecificationExecutor interface, we can use Specification for dynamic query. Let's take a look at the JpaSpecificationExecutor interface first:

public interface JpaSpecificationExecutor<T> { 
 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); 
} 

As you can see, five methods are provided, and the arguments and return values of the methods clearly express their intent. The parameters, Pageable and Sort, should be relatively simple, respectively are paging parameters and sorting parameters, and the focus is Specification parameters, let's take a look at the definition of this interface:

public interface Specification<T> { 
 Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb); 
} 

One of these methods returns a dynamically queried data structure.

javax.persistence.criteria.Predicate toPredicate(javax.persistence.criteria.Root<T> root,
javax.persistence.criteria.CriteriaQuery<?> query,
javax.persistence.criteria.CriteriaBuilder cb);

The specification in Java EE is used here, and the specific implementation is Hibernate. Of course, you can choose other data persistence layer frameworks that implement THE JPA specification.
Here we need to go back to Criteria API 1 again:

The Criteria query is based on the concept of a metamodel defined for the managed entities of a specific persistence unit, which can be entity classes, embedded classes, or mapped superclasses.

CriteriaQuery interface: Represents a top-level query object of specific, which contains various parts of the query, such as: select, from, where, group by, order by, etc. Note: The CriteriaQuery object only works for Criteria queries of entity type or embedded type

The Root interface: Represents the root object of the Criteria query. The root of the Criteria query defines the entity type to obtain the desired results for future navigation, which is similar to the FROM clause in the SQL query

1: The Root instance is typed and defines the types that can appear in the FROM clause of the query.

2: The query root instance can be obtained by passing in an entity type to the AbstractQuery.from method.

3: Criteria query, you can have multiple query roots.

4: AbstractQuery is the parent of the CriteriaQuery interface, which provides the method to get the query root. CriteriaBuilder interface: The builder object used to build CritiaQuery Predicate: a simple or complex predicate type that is essentially a condition or combination of conditions

The supported methods are very powerful. Here is an example for you to refer to. Similarly, you can write more complex queries based on the example:

Repo interface:

public interface DevHREmpConstrastDao 
 extends JpaRepository<DevHREmpConstrast, Long>,JpaSpecificationExecutor<DevHREmpConstrast> 

Query instance 1:

/** 
 *  Condition querying dynamically assembles conditions  
 */ 
private Specification<DevHREmpConstrast> where( 
  final String corg,final String name,final String type,final String date,final String checker){ 
 return new Specification<DevHREmpConstrast>() { 
  @Override 
  public Predicate toPredicate(Root<DevHREmpConstrast> root, CriteriaQuery<?> query, CriteriaBuilder cb) { 
   List<Predicate> predicates = new ArrayList<Predicate>(); 
   // institutions  
   if(corg!=null&&!corg.equals("")){ 
    List<String> orgIds = organizationDao.findByName("%"+corg+"%"); 
    if(orgIds.size()>0&&orgIds.size()<1000) 
     predicates.add(root.<String>get("confirmOrgNo").in(orgIds));//confirmOrgNo 
   } 
   // The name  
   if(name!=null&&!name.equals("")){ 
    List<String> userIds = userDao.findByName(name); 
    if(userIds.size()>0&&userIds.size()<1000)// If there are too many, I don't care about this condition  
     predicates.add(root.<String>get("hrUserName").in(userIds)); 
   } 
   // type  
   if(type!=null&&!type.equals("")) 
    predicates.add(cb.equal(root.<String>get("hrUpdateType"),type)); 
   // The date of  
   if(date!=null&&!date.equals("")){ 
    // The processing time  
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); 
    Date startDate; 
    Date endDate; 
    try { 
     startDate = format.parse(date); 
    } catch (ParseException e) { 
     startDate = new Date(946656000000L);//2000 01 01 
    } 
    endDate = startDate; 
    Calendar calendar = Calendar.getInstance() ; 
    calendar.setTime(endDate); 
    calendar.add(Calendar.DATE, 1); 
    endDate = calendar.getTime(); 
    calendar = null; 
    predicates.add(cb.between(root.<Date>get("insDate"),startDate,endDate)); 
   } 
   // The reviewer  
   if(checker!=null&&!checker.equals("")){ 
    List<String> userIds = userDao.findByName(checker); 
    if(userIds.size()>0&&userIds.size()<1000)// If there are too many, I don't care about this condition  
     predicates.add(root.<String>get("confirmUserId").in(userIds)); 
   } 
   return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
  } 
 }; 
} 

Query instance 2:

/** 
 *  Condition querying dynamically assembles conditions  
 */ 
 private Specification<DevHREmpConstrast> where( 
   final String corg,final String name,final String type,final String date,final String checker){ 
  return new Specification<DevHREmpConstrast>() { 
   @Override 
   public Predicate toPredicate(Root<DevHREmpConstrast> root, CriteriaQuery<?> query, CriteriaBuilder cb) { 
    List<Predicate> predicates = new ArrayList<Predicate>(); 
    // institutions  
    if(corg!=null&&!corg.equals("")){ 
     List<String> orgIds = organizationDao.findByName("%"+corg+"%"); 
     if(orgIds.size()>0&&orgIds.size()<1000) 
      predicates.add(root.<String>get("confirmOrgNo").in(orgIds));//confirmOrgNo 
    } 
    // The name  
    if(name!=null&&!name.equals("")){ 
     List<String> userIds = userDao.findByName(name); 
     if(userIds.size()>0&&userIds.size()<1000)// If there are too many, I don't care about this condition  
      predicates.add(root.<String>get("hrUserName").in(userIds)); 
    } 
    // type  
    if(type!=null&&!type.equals("")) 
     predicates.add(cb.equal(root.<String>get("hrUpdateType"),type)); 
    // The date of  
    if(date!=null&&!date.equals("")){ 
     // The processing time  
     SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); 
     Date startDate; 
     Date endDate; 
     try { 
      startDate = format.parse(date); 
     } catch (ParseException e) { 
      startDate = new Date(946656000000L);//2000 01 01 
     } 
     endDate = startDate; 
     Calendar calendar = Calendar.getInstance() ; 
     calendar.setTime(endDate); 
     calendar.add(Calendar.DATE, 1); 
     endDate = calendar.getTime(); 
     calendar = null; 
     predicates.add(cb.between(root.<Date>get("insDate"),startDate,endDate)); 
    } 
    // The reviewer  
    if(checker!=null&&!checker.equals("")){ 
     List<String> userIds = userDao.findByName(checker); 
     if(userIds.size()>0&&userIds.size()<1000)// If there are too many, I don't care about this condition  
      predicates.add(root.<String>get("confirmUserId").in(userIds)); 
    } 
    return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
   } 
  }; 
 } 

You then call the dao layer method and pass in the parameters returned by the where() method.