Using SpecificationBuilder to simplify the dynamic query of Spring Data Jpa

SpecificationBuilder

See source code Github

Existing problems

Suppose there is a domain with the following fields:
* Long id
* String name
* Integer age
* String school
* LocalDateTime birthday

If we want to make a dynamic query that supports matching by logo (accurate), age (accurate), school (batch), birthday (interval), and name (fuzzy), we need to build a condition that contains the following fields

  • Long id
  • String name
  • Integer age
  • List schools
  • LocalDateTime birthdayStart
  • LocalDateTime birthdayEnd

Then we need to write a long code to build the Specification object

Specification specification = new Specification<User>() {
        @Nullable
        @Override
        public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicates = new ArrayList<>();
            if (userCondition.getId() != null) {
                predicates.add(criteriaBuilder.equal(root.get("id"), userCondition.getId()));
            }
            if (userCondition.getAge() != null) {
                predicates.add(criteriaBuilder.equal(root.get("age"), userCondition.getAge()));
            }
            if (userCondition.getName() != null) {
                predicates.add(criteriaBuilder.like(root.get("name"), "%" + userCondition.getName() + "%"));
            }
            if (userCondition.getBirthdayStart() != null) {
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("birthday"), userCondition.getBirthdayStart()));
            }
            if (userCondition.getBirthdayEnd() != null) {
                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("birthday"), userCondition.getBirthdayEnd()));
            }
            if (CollectionUtils.isNotEmpty(userCondition.getSchools())) {
                predicates.add(root.get("school").in(userCondition.getSchools()));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
        };

For dynamic SQL with only five fields, 26 lines of code are written, most of which are boilerplate codes. In practice, one condition is often larger than ten fields

Using SpecificationBuilder

Using SpecificationBuilder to build the Specification of the query requires only one line

Specification buildSpecification = SpecificationBuilder.buildSpecification(condition);

Of course, you need to add comments to the condition object for some necessary description. The condition code is as follows

  //equal for singular fields
  private Long id;
  //Fuzzy match with @ Like annotation
  @Like  
  private String name;
  private Integer age;
  //When the field Name in the condition is inconsistent with the field in the domain, use @ Name to fix it. The set does the in operation
  @Name("school") 
  private List<String> schools;
  //@Greater than equal is equal to greater than or equal to
  @Name("birthday")
  @GreaterThanEqual 
  private LocalDateTime birthdayStart;
  //@Lesstthanequal is equal to or less than
  @Name("birthday")
  @LessThanEqual  
  private LocalDateTime birthdayEnd;

Use guide

Use the annotation to add the necessary description to the condition field
*Among them, the set type defaults to the in operation, and the odd number defaults to the equal operation
*@ Like indicates fuzzy query. By default, a percentage sign is added around the variable. It supports custom query by passing in the parameters Like.left and Like.right
*@ GreaterThan equal indicates greater than or equal to, greater than using @ GreaterThan
*@ LessThanEqual indicates less than or equal to, less than @ LessThan
*@ Page indicates the fields related to paging, and does not participate in the construction of Specification
*@ Ignore is marked as an ignorable field

Tags: less github SQL

Posted on Thu, 30 Apr 2020 05:19:03 -0700 by why2k