Detailed explanation of dynamic query code in JAVA Spring Data JPA

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

For example, if the front-end has multiple conditions, many of which are optional, then the back-end SQL should be customizable. When using hibernate, you can judge Conditions are used to splice SQL (HQL). Of course, Spring Data JPA not only simplifies our development, but also provides support.

By implementing the dynamic query implemented by Criteria 2, our Repo interface needs to inherit the JpaSpecificationExecutor interface, which is a generic interface.

Then when querying, just pass in dynamic query parameters, paging parameters, etc.

It is very simple to use, but in order to understand why, let’s first introduce the Criteria API.

Criteria API

A query is type-safe for Java objects if the compiler can perform syntax correctness checks on the query. Version 2.0 of the Java™ Persistence API (JPA) introduces the Criteria API, which brings type-safe queries to Java applications for the first time and provides a mechanism for dynamically constructing queries at runtime. This article describes how to write dynamic, type-safe queries using the Criteria API and the 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); 
}

You can see So far, 5 methods have been provided, and the parameters and return values ​​of the methods have clearly expressed their intentions. Among the parameters, Pageable and Sort should be relatively simple, they are paging parameters and sorting parameters respectively, and the focus is the Specification parameter. Let’s take a look at the definition of this interface first:

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

One of the methods returns Data structure for dynamic query.

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

The specifications used here are all Java EE specifications. For specific implementation, I use Hibernate. Of course, you can also choose other data persistence that implements JPA specifications. layer frame.
Here we need to go back and look at some things in the Criteria API:

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

CriteriaQuery interface: represents a specific top-level query object, which contains various parts of the query, such as: select, from, where, group by, order by, etc. Note: CriteriaQuery objects only work with entity types Or embedded type of Criteria query works

Root interface: represents the root object of the Criteria query. The query root of the Criteria query defines the entity type and can obtain the desired results for future navigation. It is related to The FROM clause in a SQL query is similar to

1: Root instances are typed and define 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 can have multiple query roots.

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

The supported methods are very powerful, as given below You can refer to an example. Similarly, you can write more complex queries based on the example:

Repo interface:

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

Query example 1:

/** 
 * 条件查询时动态组装条件 
 */ 
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>(); 
   //机构 
   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 
   } 
   //名字 
   if(name!=null&&!name.equals("")){ 
    List<String> userIds = userDao.findByName(name); 
    if(userIds.size()>0&&userIds.size()<1000)//如果太多就不管了这个条件了 
     predicates.add(root.<String>get("hrUserName").in(userIds)); 
   } 
   //类型 
   if(type!=null&&!type.equals("")) 
    predicates.add(cb.equal(root.<String>get("hrUpdateType"),type)); 
   //日期 
   if(date!=null&&!date.equals("")){ 
    //处理时间 
    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)); 
   } 
   //审核人 
   if(checker!=null&&!checker.equals("")){ 
    List<String> userIds = userDao.findByName(checker); 
    if(userIds.size()>0&&userIds.size()<1000)//如果太多就不管了这个条件了 
     predicates.add(root.<String>get("confirmUserId").in(userIds)); 
   } 
   return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
  } 
 }; 
}

Query example 2:

/** 
 * 条件查询时动态组装条件 
 */ 
 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>(); 
    //机构 
    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 
    } 
    //名字 
    if(name!=null&&!name.equals("")){ 
     List<String> userIds = userDao.findByName(name); 
     if(userIds.size()>0&&userIds.size()<1000)//如果太多就不管了这个条件了 
      predicates.add(root.<String>get("hrUserName").in(userIds)); 
    } 
    //类型 
    if(type!=null&&!type.equals("")) 
     predicates.add(cb.equal(root.<String>get("hrUpdateType"),type)); 
    //日期 
    if(date!=null&&!date.equals("")){ 
     //处理时间 
     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)); 
    } 
    //审核人 
    if(checker!=null&&!checker.equals("")){ 
     List<String> userIds = userDao.findByName(checker); 
     if(userIds.size()>0&&userIds.size()<1000)//如果太多就不管了这个条件了 
      predicates.add(root.<String>get("confirmUserId").in(userIds)); 
    } 
    return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
   } 
  }; 
 }

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

The above is the detailed content of Detailed explanation of dynamic query code in JAVA Spring Data JPA. For more information, please follow other related articles on the PHP Chinese website!