排序JPQL实体列表 [英] Sorting a list of JPQL Entities

问题描述

我一直在努力寻找答案，但是到目前为止我还没有运气.我正在尝试通过JPQL查询检索和排序对象列表，但是由于查询本身在不同表之间使用了大量联接，因此确实很困难.

I've searched long and hard for an answer, but I haven't had any luck so far. I'm trying to retrieve and sort a list of objects via a JPQL query, but since the query itself uses a lot of joins between different tables, it's really difficult.

基本上，我们有一个带有字段的实体人"

Basically, we have an entity "Person" with the fields

String name
List<Telephone> phones
List<Email> emails
List<Address> addresses

电话"，电子邮件"和地址"都是单独的实体，每个实体都包含自己的数据，例如字符串字段数字"或类似名称(Address.street，Address.state).因此，所有这四个对象都是数据库中的表.

"Telephone", "Email", and "Address" are all separate Entities, each containing its own data, like a String field "number" or something similar (Address.street, Address.state). So all four of these objects are tables in the database.

我希望用户能够按任何特定数据对人员"列表进行排序.现在，我想按以下任何一项进行排序:人员的姓名，电子邮件列表中的第一个电子邮件地址，人员的第一个地址的街道或所在州，等等.因此，如果以下人员列表:

I want the user to be able to sort a list of Persons by any particular data. Right now, I want to sort by any one of the following: the Person's name, the first email address in the emails list, by the street or state of the person's first address, and so on. So if a list of Persons is of the following:

Name     Phone Number        Street          State
Mack     555-1234            1 Main Street   WA
Andy     222-9999            2 Other Way     RI
Wendy    222-3333            3 Wrong Way     UT

并且我希望该表按州排序，列表应为:

and I want the table to be sorted by State, the list should be:

Name     Phone Number        Street          State
Andy     222-9999            2 Other Way     RI
Wendy    222-3333            3 Wrong Way     UT
Mack     555-1234            1 Main Street   WA

我希望使用JPQL查询来完成此操作，因此在将列表提供给Web服务器以优化性能时，已经对其进行了过滤和排序.我还必须补充一点，我正在实现一种搜索功能，该功能可以搜索所有这些列"中的特定术语.

I want this to be done using a JPQL query so a list is already filtered and sorted when it's given to the web server to optimize performance. I must also add that I am implementing a search feature that searches all these "columns" for a particular term.

长话短说，如何编写JPQL查询，以便a可以获取已针对这些列之一进行排序的Person对象的列表?我对"SELECT DISTINCT"的实现:

Long story short, how do I write a JPQL query such that a can obtain a list of Person objects that have been sorted for one of these columns? My implementation of "SELECT DISTINCT":

SELECT DISTINCT x FROM Person x, IN(x.phones) phones ORDER BY phones.number

不起作用，因为结果表需要对列进行排序(这是无效的，因为它导致列表不符合Person对象的列表的资格)，而且我似乎无法使用嵌套的SELECT语句来对列进行排序生成结果集，然后从中提取一个Person对象列表.有没有简单的方法可以做到这一点?

doesn't work since the result table requires to have the column being sorted (which is invalid because it causes the list to not qualify as a list of Person objects), and I can't seem to use nested SELECT statements to generate the result set and then pull a list of Person objects from that. Is there any easy way to do this?

推荐答案

对不起，我必须不同意.在JPQL中，您可以(！)对结果集进行排序，而不必在结果中包括已排序的列.

I am sorry but I must disagree. In JPQL you can (!) sort the result set without including the sorted column in the result.

查看任何Hibernate示例(作为JPQL的典型实现).

See any Hibernate example (as a promonent JPQL implementation).

如果我想念我，请告诉我

Please let me know if I miss understood

SELECT DISTINCT p FROM Person p 
                   JOIN p.addressList adr 
                   JOIN p.emailList eml 
                   JOIN p.phoneList phn
ORDER BY p.name, eml.emailAddress, phn.phoneNumber

这篇关于排序JPQL实体列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！