toPredicate Method for jpa Multiple Conditional Query Rewriting Specification

Directory Criteria Query Basic Concepts Criteria Query Basic Object Construction Let's use two sample codes to get a deeper understanding

Spring Data JPA supports Criteria queries for JPA 2.0, and the corresponding interface is JpaSpecificationExecutor.

Criteria Query: Is a type-safe and more object-oriented query.

This interface is basically defined around the Specification interface, and only the following one method is defined in the Specification interface:

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

To understand this method and use it correctly, you need to be familiar with and understand the Criteria query of JPA 2.0, because the parameters and return values of this method are objects defined in JPA standard.

Basic Concepts of Criteria Query

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

CriteriaQuery Interface: Represents a top-level query object of specific, which contains all parts of the query, such as select, from, where, group, by, order by, etc. Note: CriteriaQuery object only works for entity type or embedded type Criteria query Root Interface: Represents the root object of an Criteria query, which defines the entity type for future navigation and is similar to the FROM clause in an SQL query

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

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

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

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

Construction of Criteria Query Basic Object

1: The CriteriaBuilder object can be obtained by getCriteriaBuilder of EntityManager or getCriteriaBuilder of EntityManagerFactory

2: An instance of CriteriaQuery can be obtained by calling the createQuery or createTupleQuery methods of CriteriaBuilder

3: The Root instance filter condition can be obtained by calling the from method of CriteriaQuery

A: The filter is applied to the FROM clause of the SQL statement. In an criteria query, query criteria are applied to an CriteriaQuery object through an Predicate or Expression instance. B: These conditions are applied to an CriteriaQuery object using the CriteriaQuery. where method C: CriteriaBuilder also serves as a factory for Predicate instances, creating Predicate objects by calling the conditional parties of CriteriaBuilder (equalnotEqual, gt, ge, lt, le, between, like, etc.). D: Compound Predicate statements can be constructed using the and, or andnot methods of CriteriaBuilder.

Build a simple Predicate example:

Predicate p1=cb.like(root.get( " name " ).as(String.class),  " % " +uqm.getName()+ " % " );
Predicate p2=cb.equal(root.get("uuid").as(Integer.class), uqm.getUuid());
Predicate p3=cb.gt(root.get("age").as(Integer.class), uqm.getAge());

Build a composite Predicate example:

Predicate p = cb.and(p3,cb.or(p1,p2));

Let's take a closer look at two sample codes

Multi-table query with complex conditions

// Objects to query 
public class Qfjbxxdz {
    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid.hex")
    private String id;
    @OneToOne
    @JoinColumn(name = "qfjbxx")
    private Qfjbxx qfjbxx; // Association table 
    private String fzcc;
    private String fzccName;
    @ManyToOne
    @JoinColumn(name = "criminalInfo")
    private CriminalInfo criminalInfo;// Association table 
    @Column(length=800)
    private String bz;
    //get/set......
}
// Create a construct Specification Method of 
// Here, I pass in two condition parameters because they are related to the previous frame. When you write, you can decide your own business. 
private Specification<Qfjbxxdz> getWhereClause(final JSONArray condetion,final JSONArray search) {
        return new Specification<Qfjbxxdz>() {
            @Override
            public Predicate toPredicate(Root<Qfjbxxdz> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicate = new ArrayList<>();
                Iterator<JSONObject> iterator = condetion.iterator();
                Predicate preP = null;
                while(iterator.hasNext()){
                    JSONObject jsonObject = iterator.next();
                    // Note: The root.join  Because we're gonna use qfjbxx This must be done as a condition for the fields in the object join Method has many overloads, so you can make decisions according to your own business when using it 
                    Predicate p1 = cb.equal(root.join("qfjbxx").get("id").as(String.class),jsonObject.get("fzId").toString());
                    Predicate p2 = cb.equal(root.get("fzcc").as(String.class),jsonObject.get("ccId").toString());
                    if(preP!=null){
                        preP = cb.or(preP,cb.and(p1,p2));
                    }else{
                        preP = cb.and(p1,p2);
                    }
                }
                JSONObject jsonSearch=(JSONObject) search.get(0);
                Predicate p3=null;
                if(null!=jsonSearch.get("xm")&&jsonSearch.get("xm").toString().length()>0){
                   p3=cb.like(root.join("criminalInfo").get("xm").as(String.class),"%"+jsonSearch.get("xm").toString()+"%");
                }
                Predicate p4=null;
                if(null!=jsonSearch.get("fzmc")&&jsonSearch.get("fzmc").toString().length()>0){
                    p4=cb.like(root.join("qfjbxx").get("fzmc").as(String.class),"%"+jsonSearch.get("fzmc").toString()+"%");
                }
                Predicate preA;
                if(null!=p3&&null!=p4){
                    Predicate  preS =cb.and(p3,p4);
                    preA =cb.and(preP,preS);
                }else if(null==p3&&null!=p4){
                    preA=cb.and(preP,p4);
                }else if(null!=p3&&null==p4){
                    preA=cb.and(preP,p3);
                }else{
                    preA=preP;
                }
                predicate.add(preA);
                Predicate[] pre = new Predicate[predicate.size()];
                query.where(predicate.toArray(pre));
                return query.getRestriction();
            }

Writing an DAO class or interface

dao class/interface inherits

public interface JpaSpecificationExecutor<T> 

Interface;

If you need paging, you can also inherit

public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> 

The JpaSpecificationExecutor interface has methods

    Page<T> findAll(Specification<T> spec, Pageable pageable);  // Paged query by condition   
    List<T> findAll(Specification<T> spec);    // Query by condition without pagination  

Method. We can call these two methods at the Service layer.

Both methods have the Specification spec parameter, which sets the query criteria.

Service Paging + Multiple Criteria Query Invocation Example:

studentInfoDao.findAll(new Specification<StudentInfo> () { 
   public Predicate toPredicate(Root<StudentInfo> root,  
     CriteriaQuery<?> query, CriteriaBuilder cb) {  
    Path<String> namePath = root.get("name");  
    Path<String> nicknamePath = root.get("nickname");  
    /** 
         *  Join query criteria ,  Indefinite parameters, can be connected 0..N Query criteria  
         */  
    query.where(cb.like(namePath, "% Li %"), cb.like(nicknamePath, "% Wang %")); // You can set any query criteria here  
    return null;  
   } 
  }, page); 
 } 

Here, two query criteria are created through the like method of CriteriaBuilder, where the name (name) field must contain "Li" and the nickname (nickname) field must contain "Wang".

Then you can join multiple query criteria. In this way, API of JPA is used to set the query condition, so there is no need to return the query condition Predicate to Spring Data Jpa, so return null; That's enough.