Sorting with Hibernate – 用Hibernate进行排序

最后修改: 2014年 5月 5日

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

1. Overview

1.概述

This article illustrates how to Sort with Hibernate, using both the Hibernate Query Language (HQL) and the Criteria API.

本文说明了如何用Hibernate进行排序，同时使用Hibernate查询语言（HQL）和标准API。

2. Sorting With HQL

2.用HQL进行排序

Sorting with Hibernate’s HQL is as simple as adding the Order By clause to the HQL query string:

使用Hibernate的HQL进行排序，就像在HQL查询字符串中添加 Order By子句一样简单。

String hql = "FROM Foo f ORDER BY f.name";
Query query = sess.createQuery(hql);

After this code is executed, Hibernate will generate the following SQL query:

执行此代码后，Hibernate将生成以下SQL查询。

Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_ from 
    FOO foo0_ order by foo0_.NAME

The default sort order direction is ascending. This is why the order condition, asc, is not included in the generated SQL query.

默认的排序方向是升序。这就是为什么顺序条件asc不包括在生成的SQL查询中。

2.1. Using an Explicit Sorting Order

2.1.使用明确的排序顺序

To specify the sorting order manually – you’ll need to include the order direction in the HQL query string:

要手动指定排序顺序–你需要在 HQL查询字符串中包括排序方向。

String hql = "FROM Foo f ORDER BY f.name ASC";
Query query = sess.createQuery(hql);

In this example, setting the asc clause in the HQL was included in the generated SQL query:

在这个例子中，在HQL中设置asc子句被包含在生成的SQL查询中。

Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_ 
    from FOO foo0_ order by foo0_.NAME ASC

2.2. Sorting by More Than One Attribute

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

Multiple attributes, together with an optional sorting order, can be added to the Order By clause in the HQL query string:

多个属性，加上一个可选的排序顺序，可以被添加到HQL查询字符串中的Order By子句。

String hql = "FROM Foo f ORDER BY f.name DESC, f.id ASC";
Query query = sess.createQuery(hql);

The generated SQL query will change accordingly:

生成的SQL查询将相应改变。

Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_ 
    from FOO foo0_ order by foo0_.NAME DESC, foo0_.ID ASC

2.3. Setting Sorting Precedence of Null Values

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

By default, when the attribute to sort by has null values, it is up to the RDMS to decide the precedence. This default treatment can be overridden by placing a NULLS FIRST or NULLS LAST clause in the HQL query string.

默认情况下，当要排序的属性有null值时，由RDMS来决定优先级。这种默认处理方法可以通过在HQL查询字符串中放置一个NULLS FIRST或NULLS LAST子句而被覆盖。

This simple example places any nulls at the end of the result list:

这个简单的例子将任何空值放在结果列表的最后。

String hql = "FROM Foo f ORDER BY f.name NULLS LAST";
Query query = sess.createQuery(hql);

Lets see the is null then 1 else 0 clause in the generated SQL query:

让我们看看在生成的SQL查询中的 is null then 1 else 0子。

Hibernate: select foo0_.ID as ID1_1_, foo0_.NAME as NAME2_1_, 
foo0_.BAR_ID as BAR_ID3_1_, foo0_.idx as idx4_1_ from FOO foo0_ 
order by case when foo0_.NAME is null then 1 else 0 end, foo0_.NAME

2.4. Sorting One To Many Relations

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

Lets analyze a complex sorting case: sorting entities in a one to many relation – Bar containing a collection of Foo entities.

让我们分析一个复杂的排序案例。以一对多的关系对实体进行排序 – Bar包含一个Foo实体的集合。

We’ll do this by annotating the collection with the Hibernate @OrderBy annotation; we will specify the field by which the ordering is done, as well as the direction:

我们将通过使用Hibernate @OrderBy 注解对集合进行注解；我们将指定进行排序的字段以及方向。

@OrderBy(clause = "NAME DESC")
Set<Foo> fooList = new HashSet();

Notice that clause argument to the annotation. This is unique to Hibernate’s @OrderBy, as compared to similar @OrderBy JPA annotation. Another characteristic that differentiates this approach from its JPA equivalent is that the clause argument indicates that the sorting is done based on the NAME column of the FOO table, not on the name attribute of Foo.

注意注解的clause参数。与类似的@OrderBy JPA注解相比，这是Hibernate的@OrderBy独有的。另一个区别于JPA等价物的特征是，子句参数表明排序是基于FOO表的NAME列进行的，而不是基于Foo的 name属性。

Now let’s look at the actual sorting of Bars and Foos:

现在我们来看看Bars和Foos的实际排序。

String hql = "FROM Bar b ORDER BY b.id";
Query query = sess.createQuery(hql);

The resulting SQL statement shows that the sorted Foo’s are placed in a fooList:

结果SQL语句显示，排序后的Foo’s被放置在一个fooList:。

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_ID3_0_0_, 
    foolist0_.ID as ID1_1_0_, foolist0_.ID as ID1_1_1_, foolist0_.NAME as 
    NAME2_1_1_, foolist0_.BAR_ID as BAR_ID3_1_1_, foolist0_.idx as idx4_1_1_ 
    from FOO foolist0_ where foolist0_.BAR_ID=? order by foolist0_.NAME desc

One thing to keep in mind is that it is not possible to to sort Lists as was the case with JPA. Hibernate documentation states:

需要记住的一点是，不可能像JPA那样对列表进行排序。Hibernate文档指出。

“Hibernate currently ignores @OrderBy on @ElementCollection on e.g. List<String>. The order of elements is as returned by the database, undefined.”

“Hibernate目前忽略了@ElementCollection上的@OrderBy，例如，List<String>。元素的顺序是由数据库返回的，没有定义。”

As a side-note, it would be is possible to work around this limitation by using legacy XML configuration for Hibernate, and replacing the <List..> element with a <Bag…> element.

作为旁注，通过使用Hibernate的传统XML配置，用<List…>元素替换<Bag…>元素，是可以绕过这个限制。

3. Sorting With Hibernate Criteria

3.用Hibernate的标准进行排序

The Criteria Object API provides the Order class as the main API to manage sorting.

Criteria Object API提供了Order类作为管理排序的主要API。

3.1. Setting the Sorting Order

3.1.设置排序顺序

The Order class has two methods to set the sorting order:

Order类有两个方法来设置排序顺序。

  • asc(String attribute) : Sorts the query by attribute in ascending order.
  • desc(String attribute) : Sorts the query by attribute in descending order.

Let’s start with a simple example – sorting by a single id attribute:

让我们从一个简单的例子开始–按单个id属性进行排序。

Criteria criteria = sess.createCriteria(Foo.class, "FOO");
criteria.addOrder(Order.asc("id"));

Note that the argument to the asc method is case sensitive and should match the name of the attribute to sort by.

注意，asc方法的参数是区分大小写的，应该与要排序的属性的name相匹配。

Hibernate Criteria’s Object API explicitly sets a sorting order direction and this is reflected in the SQL statement generated by the code:

Hibernate Criteria的对象API明确设置了一个排序方向，这反映在代码生成的SQL语句中。

Hibernate: select this_.ID as ID1_0_0_, this_.NAME as NAME2_0_0_ 
    from FOO this_ order by this_.ID sac

3.2. Sorting by More Than One Attribute

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

Sorting by multiple attributes only requires adding an Order object to the Criteria instance, as in the example below:

按多个属性排序只需要将一个Order对象添加到Criteria实例中，如下面的例子。

Criteria criteria = sess.createCriteria(Foo.class, "FOO");
criteria.addOrder(Order.asc("name"));
criteria.addOrder(Order.asc("id"));

The query that is generated in SQL is:

在SQL中生成的查询是。

Hibernate: select this_.ID as ID1_0_0_, this_.NAME as NAME2_0_0_ from 
    FOO this_ order by this_.NAME asc, this_.ID sac

3.3. Setting Sorting Precedence of Null Values

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

By default, when the attribute to sort by has null values, it is up to the RDMS to decide the precedence. Hibernate Criteria Object API makes it simple to change that default and place nulls at the end of an ascending ordered list:

默认情况下，当要排序的属性有null值时，由RDMS来决定优先级。Hibernate Criteria Object API使得改变默认值和将空值放在升序列表的最后变得简单。

Criteria criteria = sess.createCriteria(Foo.class, "FOO");
criteria.addOrder(Order.asc("name").nulls(NullPrecedence.LAST));

Here is the underlying SQL query – with the is null then 1 else 0 clause:

这里是基础的SQL查询–带有is null then 1 else 0子句。

Hibernate: select this_.ID as ID1_1_1_, this_.NAME as NAME2_1_1_, 
    this_.BAR_ID as BAR_ID3_1_1_, this_.idx as idx4_1_1_, bar2_.ID as
    ID1_0_0_, bar2_.NAME as NAME2_0_0_ from FOO order by case when 
    this_.NAME is null then 1 else 0 end, this_.NAME asc

Alternatively, we can also place the nulls at the beginning of a descending ordered list:

另外，我们也可以将空值放在降序列表的开头。

Criteria criteria = sess.createCriteria(Foo.class, "FOO");
criteria.addOrder(Order.desc("name").nulls(NullPrecedence.FIRST));

The corresponding SQL query follows – with the is null then 0 else 1 clause:

相应的SQL查询如下 – 带有is null then 0 else 1子。

Hibernate: select this_.ID as ID1_1_1_, this_.NAME as NAME2_1_1_, 
    this_.BAR_ID as BAR_ID3_1_1_, this_.idx as idx4_1_1_, bar2_.ID as 
    ID1_0_0_, bar2_.NAME as NAME2_0_0_ from FOO order by case when 
    this_.NAME is null then 0 else 1 end, this_.NAME desc

Note that, if the attribute to sort by is a primitive type like an int, a PresisitenceException will thrown.

请注意，如果要排序的属性是一个原始类型，如int，将抛出PresisitenceException。

For example, if the value of f.anIntVariable is null, then the execution of the query:

例如，如果 f.anIntVariable的值为空，那么执行查询。

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

will throw:

将投掷。

javax.persistence.PersistenceException: org.hibernate.PropertyAccessException:
Null value was assigned to a property of primitive type setter of 
com.cc.jpa.example.Foo.anIntVariable

4. Conclusion

4.结论

This article explores sorting with Hibernate – using the available APIs for simple entities as well as for entities in a one-to-many relation.

本文探讨了用Hibernate进行排序的问题–对简单实体以及一对多关系中的实体使用可用的API。

The implementation of this Hibernate Sorting Tutorial can be found in the github project – this is an Eclipse based project, so it should be easy to import and run as it is.

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