Query all and sort using Spring Data Jpa

Directory Spring Data Jpa query all and sort 1, Repository layer only needs to simply extends JpaRepository2, Service layer as follows JPA sort question 1, overview 2, use JPA/JQL API sort 3, use JPA condition query object API to sort

Spring Data Jpa Query All and Sort

1. Repository layer only needs extends JpaRepository

Still don't have to write anything. Because it actually has an findAll (Sort sort) method, it can be used directly.

2. The Service layer is as follows

List<xxxDO> xxxDOS = xxxRepository.findAll(new Sort(Sort.Direction.ASC," Attribute name "));

Note, however, that the constructor of springboot 2.2. 1 and above, Sort, becomes private and needs to be written as follows:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

The Scheduling Problem of JPA

1. Overview

This article will explore various implementations of Java Persistence API (JPA) sorting for simple entities as well as entities in 1-to-many relationships. These methods delegate the burden of sorting to the database layer.

2. Sort using JPA/JQL API

JQL sorting by using Order By keywords:

String jql ="Select * from Student order by id";
Query query = entityManager.createQuery (jql);

Based on the above query, JPA generates the following simple SQL statement:

Hibernate: select * from Student order by id

Note: The SQL keyword in the JQL string is not case sensitive, but the name of the entity and its attributes are sensitive (entity: Student, attribute: id).

2.1. Set the sort order

By default, the sort order is ascending, but can be set explicitly in the JQL string. As with 1 in pure SQL, the sorting options are asc and desc:

String jql = "Select * from Student order by id desc";
Query sortQuery = entityManager.createQuery(jql);

The generated SQL query will include the ascending and descending directions of the sort:

Hibernate: select * from Student order by id desc

2.2. Sort by the number of two or more attributes

To sort by multiple attributes, all the attributes to be sorted are added to the order by clause of the JQL string:

String jql="Select * from Student Order by name asc,id desc";
Query sortQuery = entityManager.createQuery(jql);

The same sort condition will appear in the generated SQL query statement:

Hibernate: select * from Student order by name asc,id desc

Note: For multi-attribute sorting, priority is given to the first attribute, sorting by the second attribute when the first value is the same, and so on.

2.3. Set the sorting priority of null values

The default null priority is specific in the database, but this can be customized through the NULLS FIRST or NULLS LAST clause in the HQL query string.

Take a simple example-in descending order of the name attribute of Student, and place Nulls at the end:

Query sortQuery = entityManager.createQuery
  ("Select * from Student order by name desc NULLS LAST");

Then, the SQL query it generates will look like this:

Hibernate: select * from Student order by case when name is null then 1 else 0 end, desc

2.4, 1-to-many relational sorting

Beyond the basic example, let's look at a use case that involves sorting entities in a 1-to-many relationship-Bar contains a collection of Student entities. We want to sort Bar entities and their collection of Student entities-JPA is particularly simple for this task:

1. Sort collections: Add a @ OrderBy annotation to the Student collection of the Bar entity:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

0

2. Sort the entities that contain the collection:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

1

Note: The @ OrderBy annotation is used here because we want to sort the Student collection for each Bar.

Next, look at the SQL statement corresponding to the above JQL:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

2

The first query sorts the parent Bar entity. A second query is generated to sort the set of child Student entities belonging to Bar.

3. Use the JPA condition to query the object API for sorting

Using the JPA Criteria-orderBy method is a "one-stop" option for setting all sorting parameters: you can set the sorting direction and sorting basis. The following is the API of this method:

orderBy (CriteriaBuilder. asc): Ascending sort. orderBy (CriteriaBuilder. desc): Sort in descending order.

Each Order instance is created by the asc or desc method of the CriteriaBuilder object.

This is a simple example-sorting Student by name:

CriteriaQuery<Student> criteriaQuery = criteriaBuilder.createQuery(Student.class);
Root<Student> from = criteriaQuery.from(Student.class);
CriteriaQuery<Student> select = criteriaQuery.select(from);
criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name")));

The parameter of the get method is case sensitive because it needs to match the property name.

Contrary to the simple JQL, the JPA conditional query object API enforces the use of explicit sequential directions in the query. Notice that in the last line of this code snippet, the criteriaBuilder object specifies an ascending order by calling its asc method.

After executing the above code, JPA generates an SQL query as shown below. JPA Criteria Object generates an SQL statement with an explicit asc clause:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

4

3.1. Sort by the number of two or more attributes

To sort multiple attributes, simply pass the Order instance to the orderBy method to sort each attribute. This is a simple example-sorted in ascending and descending order by name and ID, respectively:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

5

The corresponding SQL query looks like this:

List<xxxDO> xxxDOS = xxxRepository.findAll(Sort.by(Sort.Direction.ASC," Attribute name "));

6