Sorting with JPA – 用JPA进行排序

最后修改: 2014年 4月 29日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

This article illustrates the various way JPA can be used for sorting.

本文说明了JPA可用于排序的各种方式。

2. Sorting With JPA / JQL API

2.用JPA/JQL API进行排序

Using JQL to sort is done with the help of the Order By clause:

使用JQL进行排序是在Order By子句的帮助下完成的。

String jql ="Select f from Foo as f order by f.id";
Query query = entityManager.createQuery (jql);

Based on this query, JPA generates the following straighforward SQL statement:

基于这个查询，JPA生成了以下直截了当的SQL语句。

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_ 
    from Foo foo0_ order by foo0_.id

Note that the SQL keywords in the JQL string are not case sensitive, but the names of the entities and their attributes are.

注意，JQL字符串中的SQL关键字不区分大小写，但实体的名称和它们的属性是区分大小写的。

2.1. Setting the Sorting Order

2.1.设置排序顺序

By default the sorting order is ascending, but it can be explicitly set in the JQL string. Just as in pure SQL the ordering options are asc and desc:

默认情况下，排序顺序是升序，但可以在JQL字符串中明确设置。就像在纯SQL中，排序选项是 asc和desc。

String jql = "Select f from Foo as f order by f.id desc";
Query sortQuery = entityManager.createQuery(jql);

The generated SQL query will then include the order direction:

然后，生成的SQL查询将包括订单方向。

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_ 
    from Foo foo0_ order by foo0_.id desc

2.2. Sorting by More Than One Attribute

2.2.按一个以上的属性排序

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

要按多个属性排序，这些属性被添加到JQL字符串的order by子句中。

String jql ="Select f from Foo as f order by f.name asc, f.id desc";
Query sortQuery = entityManager.createQuery(jql);

Both sorting conditions will appear in the generated SQL query statement:

这两个排序条件将出现在生成的SQL查询语句中。

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_ 
    from Foo foo0_ order by foo0_.name asc, foo0_.id desc

2.3. Setting Sorting Precedence of Null Values

2.3.设置空值的排序优先级

The default precedence of nulls is database specific, but this is customizable through the NULLS FIRST or NULLS LAST clause in the HQL query string.

空值的默认优先级是数据库特定的，但这可以通过HQL查询字符串中的NULLS FIRST或NULLS LAST子句来定制。

Here is a simple example – ordering by name of Foo in descending order and placing Nulls at the end:

下面是一个简单的例子–按Foo的name降序排序，并将Nulls放在最后。

Query sortQuery = entityManager.createQuery
    ("Select f from Foo as f order by f.name desc NULLS LAST");

The SQL query that is generated includes the is null the 1 else 0 end clause (3rd line):

产生的SQL查询包括is null the 1 else 0 end子句（第三行）。

Hibernate: select foo0_.id as id1_4_, foo0_.BAR_ID as BAR_ID2_4_, 
    foo0_.bar_Id as bar_Id2_4_, foo0_.name as name3_4_,from Foo foo0_ order 
    by case when foo0_.name is null then 1 else 0 end, foo0_.name desc

2.4. Sorting One To Many Relations

2.4.对一对多关系进行排序

Moving past the basic examples, let’s now look at a use case involving sorting entities in a one to many relation – Bar containing a collection of Foo entities.

抛开基本的例子，现在让我们看看涉及以一对多关系排序实体的用例—Bar包含Foo实体的集合。

We want to sort the Bar entities and also their collection of Foo entities – JPA is especially simple for this task:

我们想对Bar实体以及它们的Foo实体集合进行排序–JPA对于这项任务来说特别简单。

  1. Sorting the Collection: Add an OrderBy annotation preceding the Foo collection in the Bar entity: 
    @OrderBy("name ASC")
List <Foo> fooList;
  2. Sorting the entity containing the collection: 
    String jql = "Select b from Bar as b order by b.id";
Query barQuery = entityManager.createQuery(jql);
List<Bar> barList = barQuery.getResultList();

Note that the @OrderBy annotation is optional, but we are using it in this case because we want to sort the Foo collection of each Bar.

请注意，@OrderBy注解是可选的，但我们在本例中使用它，因为我们想对每个Bar的Foo集合进行排序。

Lets take a look at the SQL query sent to the RDMS:

让我们看一下发送到RDMS的SQL查询。

Hibernate: select bar0_.id as id1_0_, bar0_.name as name2_0_ from Bar bar0_ order by bar0_.id

Hibernate: 
select foolist0_.BAR_ID as BAR_ID2_0_0_, foolist0_.id as id1_4_0_, 
foolist0_.id as id1_4_1_, foolist0_.BAR_ID as BAR_ID2_4_1_, 
foolist0_.bar_Id as bar_Id2_4_1_, foolist0_.name as name3_4_1_ 
from Foo foolist0_ 
where foolist0_.BAR_ID=? order by foolist0_.name asc

The first query sorts the parent Bar entity. The second query is generated to sort the collection of child Foo entities belonging to Bar.

第一个查询对父Bar实体进行排序。第二个查询是为了给属于Bar的子实体Foo的集合排序。

3. Sorting With JPA Criteria Query Object API

3.用JPA标准查询对象API进行排序

With JPA Criteria – the orderBy method is a “one stop” alternative to set all sorting parameters: both the order direction and the attributes to sort by can be set. Following is the method’s API:

在JPA Criteria中，orderBy方法是设置所有排序参数的 “一站式 “选择：order direction和attributes to sort都可以被设置。以下是该方法的API。

  • orderBy(CriteriaBuilder.asc): Sorts in ascending order.
  • orderBy(CriteriaBuilder.desc): Sorts in descending order.

Each Order instance is created with the CriteriaBuilder object through its asc or desc methods.

每个订单实例是通过CriteriaBuilder对象的 asc或desc方法创建的。

Here is a quick example – sorting Foos by their name:

这里有一个快速的例子–按Foos的名排序。

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

The argument to the get method is case sensitive, since it needs to match the name of the attribute.

get方法的参数是区分大小写的，因为它需要与属性的名称相匹配。

As opposed to simple JQL, the JPA Criteria Query Object API forces an explicit order direction in the query. Notice in the last line of this code snippet that the criteriaBuilder object specifies the sorting order to be ascending by calling its asc method.

与简单的JQL不同，JPA标准查询对象API在查询中强制要求明确的顺序方向。注意在这个代码片段的最后一行，criteriaBuilder对象通过调用其asc方法，指定排序顺序为升序。

When the above code is executed, JPA generates the SQL query shown below. JPA Criteria Object generates an SQL statement with an explicit asc clause:

当上述代码被执行时，JPA生成了如下所示的SQL查询。JPA Criteria Object生成了一个带有显式 asc子句的SQL语句。

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_
    from Foo foo0_ order by foo0_.name asc

3.1. Sorting by More Than One Attribute

3.1.按一个以上的属性排序

To sort by more than one attribute simply pass an Order instance to the orderBy method for each attribute to sort by.

要按一个以上的属性排序，只需为每个要排序的属性传递一个Order实例到orderBy方法。

Here is a quick example – sorting by name and id, in asc and desc order, respectively:

这里有一个快速的例子–按name和id排序，分别按asc和desc顺序排列。

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

The corresponding SQL query is shown below:

相应的SQL查询显示如下。

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_ 
    from Foo foo0_ order by foo0_.name asc, foo0_.id desc

4. Conclusion

4.结论

This article explores the sorting alternatives in the Java Persistence API, for simple entities as well as for a entities in a one-to-many relation. These approaches delegate the burden of the sorting work to the database layer.

本文探讨了Java Persistence API中对简单实体以及一对多关系中的实体的排序方法。这些方法将排序工作的负担委托给了数据库层。

The implementation of this JPA Sorting Tutorial can be found in the GitHub project – this is a Maven-based project, so it should be easy to import and run as it is.

本JPA排序教程的实现可以在GitHub项目中找到–这是一个基于Maven的项目，所以应该很容易导入并按原样运行。