Spring Data jPA: complex dynamic query and paging, sorting

The last chapter introduced the basic construction and integration of Spring Data JPA in Spring Boot, as well as relatively simple queries, this chapter describes how to achieve more complex queries and paging, sorting in Spring Data JPA.

1 Use Specification to implement complex queries

1.1 What is Specification

Specification is an interface in Spring Datejpa that extends some basic CRUD operations for JPA, and can be thought of as a complex query interface for Spring JPA. Second, we need to understand the Criteria queries, which are type safe and more object-oriented. The Spring Data JPA supports JPA2.0 Criteria queries, with the corresponding interface being JpaSpecificationExecutor. The JpaSpecificationExecutor interface is defined around the Specification interface, which defines only one method:

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

Criteria queries are based on the concept of a metamodel defined for the managed entities of a concrete persistence unit, which can be an entity class, an embedded class, or the parent of a map.

The CriteriaQuery interface represents a specific top-level query object that contains the various parts of the query, such as SELECT, FROM, WHERE, Group by, Order BY, etc. Note: CriteriaQuery objects work only for Criteria queries of entity types or embedded types. Root: Represents the Root object of the Criteria query, which defines the entity type for future navigation to obtain the desired result, similar to the FROM clause in SQL queries. The Root instance is typed and defines the types that can appear in the FROM clause of the query. Root represents the entity class for the query, from which Query gets the root object, tells JPA which entity class to query, adds query conditions, and combines EntityManager objects to get the TypedQuery object for the final query. The CriteriaBuilder interface is used to build the CritiaQuery builder object Predicate: a simple or complex Predicate type that is essentially a condition or a combination of conditions. Through the EntityManager. GetCriteriaBuilder.

2. Use Specification for complex dynamic query

Maven’s dependencies continue with those of the previous chapter, with modifications to the entity class and Controller layer. Request entity class:

@data public class AccountRequest {private Integer page; Private Integer Specifies the number of queries per pagelimit;

    private String id;

    private String name;

    private String pwd;

    private String email;

    private Integer[] types;

}
Copy the code

Entity class:

@Data
@Entity
@Table(name = "account")
@ToString
@EntityListeners(AuditingEntityListener.class)
public class Account {

    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    private String id;

    @Column(name = "username", unique = true, nullable = false, length = 64)
    private String username;

    @Column(name = "password", nullable = false, length = 64)
    private String password;

    @Column(name = "email", length = 64)
    private String email;

    @Column(name = "type")
    private Short type;

    @CreatedDate
    @Column(name = "create_time", nullable = false)
    private LocalDateTime createTime;

}
Copy the code

The Repository layer:

public interface AccountRepository extends JpaRepository<Account,String>, JpaSpecificationExecutor<Account> {}
Copy the code

Controller layer (again, skip the Service layer)

@Autowired
    private AccountRepository repository;


    @PostMapping("/get") public List<Account> get(@RequestBody AccountRequest request){ Specification<Account> specification = new Specification<Account>() { @Override public Predicate toPredicate(Root<Account> root, CriteriaQuery<? CriteriaQuery, CriteriaBuilder Builder) {// All predicates and conditions List<Predicate> predicates = new ArrayList<>(); // Exactly match the IDpwd
                if(request.getId() ! = null) { predicates.add(builder.equal(root.get("id"), request.getId()));
                }
                if(request.getPwd() ! = null) { predicates.add(builder.equal(root.get("password"), request.getPwd())); } // Fuzzy search for nameif(request.getName() ! = null && ! request.getName().equals("")) {
                    predicates.add(builder.like(root.get("username"), "%" + request.getName() + "%"));
                }
                if(request.getEmail() ! = null && ! request.getEmail().equals("")) {
                    predicates.add(builder.like(root.get("email"), "%" + request.getEmail() + "%")); } / /inRange queriesif(request.getTypes() ! = null) { CriteriaBuilder.In<Object> types = builder.in(root.get("type"));
                    for (Integer type : request.getTypes()) {
                        types = types.value(type);
                    }
                    predicates.add(types);
                }
                returnbuilder.and(predicates.toArray(new Predicate[predicates.size()])); }}; List<Account> accounts = repository.findAll(specification);return accounts;
    }
Copy the code

By rewriting the Specification’s toPredicate approach, such a complex dynamic SQL query is completed and can be invoked directly with a POST request.

3 paging and sorting

@PostMapping("/page") public List<Account> getPage(@RequestBody AccountRequest request){ Specification<Account> specification = new Specification<Account>() { @Override public Predicate toPredicate(Root<Account> root, CriteriaQuery<? > criteriaQuery, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); //do anything
                returncriteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); }}; PageRequest page = new PageRequest(request.getPage() -1, request.getLimit(), Sort.Direction.ASC,"createTime");
        Page<Account> pageInfo = repository.findAll(specification, page);

        return pageInfo.getContent();
    }
Copy the code

The above code builds paging sorting rules through PageRequest after a complex query and paging and sorting. You pass in the start page and the number of pages per page, as well as the rules for sorting and by which attribute. Jpa starts with page 0, so be careful when passing the parameter! Of course, if you don’t need to perform complex queries, you can also paginate and sort the data. Modify the repository, make its PagingAndSortingRepository inheritance.

@Repository
public interface AccountRepository extends JpaRepository<Account,String>, JpaSpecificationExecutor<Account> , PagingAndSortingRepository<Account,String> {
    Page<Account> findByAge(int age, Pageable pageable);
}
Copy the code

To use it, create the pageable parameter and pass it in.

PageRequest pr = new PageRequest(1,3); / / according to age and query Page < Account > stus = accountPageRepository. FindByAge (22, pr);Copy the code

Sorting is the same, creating methods in repository

List<Account> findByPwd(String pwd, Sort sort);
Copy the code

Call to pass in the sort object

/ / set the sort mode for the username descending List < Account > accs. = accountPageRepository findByAge ("123456",new Sort(Sort.Direction.DESC,"username")); // Set the sort to username andtypeFor ascending acc = accountPageRepository. FindByAge ("123456",new Sort(Sort.Direction.ASC,"username"."type")); Sort Sort = new Sort(new sort.order (sort.direction.asc,"name"),new Sort.Order(Sort.Direction.DESC,"type"));
accs = accountPageRepository.findByAge("123456",sort);
Copy the code