Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to use Spring Data Jpa to query all and sort. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Spring Data Jpa query all and sort 1. The Repository layer simply needs to extends JpaRepository

Still don't have to write anything. Because it actually has a findAll (Sort sort) method, just use it.

2. List xxxDOS = xxxRepository.findAll in the Service layer (new Sort (Sort.Direction.ASC, "attribute name"))

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

List xxxDOS = xxxRepository.findAll (Sort.by (Sort.Direction.ASC, "attribute name")); sorting problem of JPA 1. Overview

This article will explore various implementations for Java Persistence API (JPA) sorting, which are suitable for simple entities and entities in one-to-many relationships. These methods delegate the burden of sorting to the database layer.

2. Use JPA / JQL API to sort

JQL sorting is achieved by using the Order By keyword:

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 entity name and its attributes are case-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 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 direction 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 that need 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:

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

Note: for multi-attribute sorting, the first attribute is given priority, when the first value is sorted by the second attribute at the same time, and so on.

2.3. Set the sort 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-sort it in descending order by the name attribute of Student, and put 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. Sort one-to-many relationships

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

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

@ OrderBy ("name ASC") List studentList

2. Sort the entities that contain the collection:

String jql = "Select * from Bar as b order by b.id"; Query barQuery = entityManager.createQuery (jql); List barList = barQuery.getResultList ()

Note: the reason for using the @ OrderBy annotation here is that we want to sort the Student collection for each Bar.

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

Hibernate: select * from Bar b order by b.idHibernate: select * from Student slist where slist.bar_id=? Order by slist.name asc

The first query sorts the parent Bar entity. A second query is generated to sort the collection of child Student entities that belong to Bar.

3. Use JPA conditional query object API to sort.

Using the JPA Criteria-orderBy method is a "one-stop" choice for setting all sort parameters: you can set the sort direction and sort by. The following is the API of the method:

OrderBy (CriteriaBuilder.asc): sort in ascending order. OrderBy (CriteriaBuilder.desc): sort in descending order.

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

This is a simple example-sorting Student by name:

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery (Student.class); Root from = criteriaQuery.from (Student.class); CriteriaQuery 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.

In contrast to a simple JQL, the JPA conditional query object API enforces a clear sequential direction in the query. Notice that in the last line of this code snippet, the criteriaBuilder object specifies the ascending order by calling its asc method.

After executing the above code, JPA generates an SQL query like the following. JPA Criteria Object generates a SQL statement with an explicit asc clause:

Hibernate: select * from Student order by asc

3.1. Sort by the number of two or more attributes

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

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery (Student.class); Root from = criteriaQuery.from (Student.class); CriteriaQuery select = criteriaQuery.select (from); criteriaQuery.orderBy (criteriaBuilder.asc (from.get ("name")), criteriaBuilder.desc (from.get ("id")

The corresponding SQL query is as follows:

Hibernate: select * from Student order by name asc,id desc on "how to use Spring Data Jpa query all side by side" this article shares here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.