Introduction

Before, single table query was often used in projects. When multiple entities are involved in query, cloud intelligence's comprehensive query library is often used directly. How to query is unknown;

When writing Alice's make-up test management, it needs to show that the total score is less than the pass score (score setting), and the comprehensive query database cannot be used, so we have a preliminary understanding of the query between multiple entities.

Summary

• Multi table query requires the jpaspecification executor interface provided by SpringData

• Entity warehouse needs to inherit jpaspecification executor

  public interface ScoreRepository extends CrudRepository<Score, Long>, JpaSpecificationExecutor<Score>     {}
  

• Implement the method of topredicte in the interface of jpaspecification executor

   public static Specification<Score> base(final Map<String, Object> map) {
      return new Specification<Score>() {
       @Override
          public Predicate toPredicate(Root<Score> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {}
      }

Build query criteria

logger.info("Construct query conditions, and the total score is lower than the pass score");
Predicate makeupScorePredicate = criteriaBuilder
    .lessThan(root.get("totalScore").as(Float.class), 
    root.join("courseArrangement")
        .join("course")
        .join("scoreSet")
        .get("passScore").as(Float.class));       

and, or connection query criteria

Here, an example of and is given:

 private Predicate predicate = null;
 private CriteriaBuilder criteriaBuilder;

 private void andPredicate(Predicate predicate) {
    // If the incoming query condition is not empty
     if (null != predicate) {
         if (null == this.predicate) {
             // If there is no query condition before the method, it is assigned directly
             this.predicate = predicate;
         } else {
             // Otherwise, use the and of criteriaBuilder to connect the existing and new query conditions with and
             this.predicate = this.criteriaBuilder.and(this.predicate, predicate);
         }
     }
 }

Improvement

Zhang xishuo, the group leader, said: it's not good to write like that before, and it's not easy to understand.

When building query conditions, we can also write as follows:

logger.info("Construct query conditions, and the total score is lower than the pass score");
Predicate makeupScorePredicate = root.get("totalScore").as(Float.class)
    .lessThan(root.join("courseArrangement")
                   .join("course")
                   .join("scoreSet")
                   .get("passScore").as(Float.class))

Is it easy to write like this! It is easy to see that the query condition is: the total score is lower than the passing score

summary

Usually, we use the cloud intelligence warehouse for comprehensive query. There is no feeling, but we can use it. The principle is not clear, and it is always fuzzy. Until we have to write it ourselves one day, we will understand it more deeply.

Thank you, group leader Zhang xishuo

Reference code

   /**
     * Return query results according to query criteria
     *
     * @param map query criteria
     */
    public static Specification<Score> base(final Map<String, Object> map) {
        return new Specification<Score>() {

            private Predicate predicate = null;
            private CriteriaBuilder criteriaBuilder;

             // Set the and predicate. Note that only the predicate of the and relationship can be set here. If the predicate is OR, you need to set it manually
            private void andPredicate(Predicate predicate) {
                if (null != predicate) {
                    if (null == this.predicate) {
                        this.predicate = predicate;
                    } else {
                        this.predicate = this.criteriaBuilder.and(this.predicate, predicate);
                    }
                }
            }

             @Override
            public Predicate toPredicate(Root<Score> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                 logger.info("Set private variable");
                this.criteriaBuilder = criteriaBuilder;

                 logger.info("Lower than passing grade in general evaluation");
                Predicate makeupScorePredicate = criteriaBuilder.lessThan(root.get("totalScore").as(Float.class), root.join("courseArrangement").join("course").join("scoreSet").get("passScore").as(Float.class));
                this.andPredicate(makeupScorePredicate);

                 if (null != map.get("semesterId")) {
                    logger.info("Semester information passed in");
                    Predicate semesterIdPredicate = criteriaBuilder.equal(root.join("courseArrangement").join("semester").get("id").as(Long.class), map.get("semesterId"));
                    this.andPredicate(semesterIdPredicate);
                }

                 if (null != map.get("gradeId")) {
                    logger.info("Incoming grade information");
                    Predicate gradeIdPredicate = criteriaBuilder.equal(root.join("student").join("klass").join("grade").get("id").as(Long.class), map.get("gradeId"));
                    this.andPredicate(gradeIdPredicate);
                }

                 if (null != map.get("majorId")) {
                    logger.info("Professional information passed in");
                    Predicate majorIdPredicate = criteriaBuilder.equal(root.join("courseArrangement").join("course").join("major").get("id").as(Long.class), map.get("majorId"));
                    this.andPredicate(majorIdPredicate);
                }

                 if (null != this.predicate) {
                    criteriaQuery.where(criteriaBuilder.and(this.predicate));
                }

                 return criteriaQuery.getRestriction();
            }
        };
    }