Spring Data JPA two ways to implement dynamic queries

preface

In the process of writing business interfaces, it is likely that interfaces that can dynamically combine various query criteria will be implemented. If we write by combining 1 method for 1 query condition, there will be a large number of methods that are cumbersome and difficult to maintain. To implement dynamic queries, you need to implement splicing SQL statements. No matter how complex the implementation, it is basically a condition that includes select fields, from or join tables, where or having. In Spring Data JPA, there are two methods to realize dynamic query of query conditions, both of which use Criteria API.

Criteria API

The API suite can be used to build queries against databases.

Type safety. By defining the metadata model, types can be checked at program compilation stage, unlike SQL, which needs to interact with Mysql to discover type problems.

The following is the metadata model. Create a metamodel class with the last character of the class name underlined, and the internal member variable corresponding to the attribute value of the entity class UserInfo.class.

@StaticMetamodel(UserInfo.class)
public class UserInfo_ {
  public static volatile SingularAttribute<UserInfo, Integer> userId;
  public static volatile SingularAttribute<UserInfo, String> name;
  public static volatile SingularAttribute<UserInfo, Integer> age;
  public static volatile SingularAttribute<UserInfo, Long> high;
}

Portable. API does not depend on a specific database and can generate SQL of the corresponding database type depending on the database type, so it is portable.

Object oriented. Criteria API USES a variety of classes and objects such as CriteriaQuery, Predicate and so on to build queries and is object-oriented. If you write SQL directly, you're dealing with a string.

Type 1: Criteria API via JPA

EntityManager get CriteriaBuilder CriteriaBuilder create CriteriaQuery CriteriaQuery specifies the table to query, resulting in Root < UserInfo > , Root represents the table to query CriteriaBuilder creates the condition Predicate, Predicate can operate with, or with multiple Predicate relative to where condition of SQL. Create TypedQuery with EntityManager TypedQuery executes the query and returns the results

public class UserInfoExtendDao {

 @PersistenceContext(unitName = "springJpa")
 EntityManager em;

 public List<UserInfo> getUserInfo(String name,int age,int high) {
   CriteriaBuilder cb = em.getCriteriaBuilder();
   CriteriaQuery<UserInfo> query = cb.createQuery(UserInfo.class);

   //from
   Root<UserInfo> root = query.from(UserInfo.class);

   //where
   Predicate p1 = null;
   if(name!=null) {
     Predicate p2 = cb.equal(root.get(UserInfo_.name),name);
     if(p1 != null) {
       p1 = cb.and(p1,p2);
     } else {
       p1 = p2;
     }
   }

   if(age!=0) {
     Predicate p2 = cb.equal(root.get(UserInfo_.age), age);
     if(p1 != null) {
       p1 = cb.and(p1,p2);
     } else {
       p1 = p2;
     }
   }

   if(high!=0) {
     Predicate p2 = cb.equal(root.get(UserInfo_.high), high);
     if(p1 != null) {
       p1 = cb.and(p1,p2);
     } else {
       p1 = p2;
     }
   }
   query.where(p1);

   List<UserInfo> userInfos = em.createQuery(query).getResultList();
   return userInfos;
 }
}

Type 2: The DAO layer interface implements JpaSpecificationExecutor < T > interface

JpaSpecificationExecutor is shown below. The method parameter Specification interface has one method toPredicate, and the return value is exactly Predicate in Criteria API, and Predicate is relative to where's where condition. Compared with the previous method, this method does not need to specify which table the query is, and does not need to achieve sorting and paging through Criteria API, just need to create Pageable, Sort objects and pass the reference to findAll method, it is simpler and 1.

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);
}

UserInfoDao implementation JpaSpecificationExecutor

public interface UserInfoDao 
  extends PagingAndSortingRepository<UserInfo, String>, JpaSpecificationExecutor<UserInfo> {}

Implement Specification

public static Specification<UserInfo> getSpec(final String name,final int age,final int high) {
   return new Specification<UserInfo>() {
     @Override
     public Predicate toPredicate(Root<UserInfo> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
       Predicate p1 = null;
       if(name!=null) {
         Predicate p2 = cb.equal(root.get(UserInfo_.name),name);
         if(p1 != null) {
           p1 = cb.and(p1,p2);
         } else {
           p1 = p2;
         }
       }

       if(age!=0) {
         Predicate p2 = cb.equal(root.get(UserInfo_.age), age);
         if(p1 != null) {
           p1 = cb.and(p1,p2);
         } else {
           p1 = p2;
         }
       }

       if(high!=0) {
         Predicate p2 = cb.equal(root.get(UserInfo_.high), high);
         if(p1 != null) {
           p1 = cb.and(p1,p2);
         } else {
           p1 = p2;
         }
       }

       return p1;
     }
   };
 }

Project code: springdatajpademo_jb51.rar