java Hibernate Criteria -- 返回列不同的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11943633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Criteria -- return records where column is distinct
提问by KyleM
Sample database table:
示例数据库表：
- ID = 1, msgFrom = 'Hello', foobar = 'meh'
- ID = 2, msgFrom = 'Goodbye', foobar = 'comments'
- ID = 3, msgFrom = 'Hello', foobar = 'response'
- ID = 1, msgFrom = '你好', foobar = 'meh'
- ID = 2, msgFrom = '再见', foobar = '评论'
- ID = 3, msgFrom = '你好', foobar = '响应'
Sample desired output (generated by hibernate query):
示例所需的输出（由休眠查询生成）：
- ID = 1, msgFrom = 'Hello', foobar = 'meh'
- ID = 2, msgFrom = 'Goodbye', foobar = 'comments'
- ID = 1, msgFrom = '你好', foobar = 'meh'
- ID = 2, msgFrom = '再见', foobar = '评论'
In the above example, the third record would be excluded from the results since the msgFrom column is the same. Let's say the Java/Hibernate class is called Message. I would like the results to be returned as a list of Message objects (or Objects that can be cast to Message, anyway). I want to use the Criteria API if possible. I saw this example on SO and it seems similar but I cannot implement it correctly as of yet.
在上面的示例中，由于 msgFrom 列相同，因此第三条记录将从结果中排除。假设 Java/Hibernate 类称为 Message。我希望将结果作为 Message 对象（或可以转换为 Message 的对象，无论如何）列表返回。如果可能，我想使用 Criteria API。我在 SO 上看到了这个例子，它看起来很相似，但到目前为止我还不能正确实现它。
select e from Message e
where e.msgFrom IN (select distinct m.msgFrom
from Message m
WHERE m.msgTo = ?
AND m.msgCheck = 0");
The reason I am doing this is to have the filtering of distinct records done on the database, so I am not interested in answers where I have to filter anything on the application server.
我这样做的原因是在数据库上完成不同记录的过滤，所以我对必须在应用程序服务器上过滤任何内容的答案不感兴趣。
edit: Article showing basically what I want to do. http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-column-only/
编辑：文章基本上显示了我想做的事情。http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-column-only/
回答by Java P
Please try this and let me know
请试试这个，让我知道
DetachedCriteria msgFromCriteria = DetachedCriteria.forClass(Message.class);
ProjectionList properties = Projections.projectionList();
properties.add(Projections.groupProperty("messageFrom"));
properties.add(Projections.min("id"),"id");
msgFromCriteria.setProjection(properties);
Criteria criteria = s.createCriteria(Message.class);
criteria.add(Subqueries.propertiesIn(new String[]{"messageFrom","id"},
msgFromCriteria));
List<Message> list = criteria.list();
for(Message message:list){
System.out.println(message.getId()
+"-------"
+message.getMessageFrom()
+"-----"
+message.getFoobar());
}
回答by James Scriven
The difficulty with this query is not so much with Hibernate, per se, but with the relational model in general. In the example, you say you expect rows 1 and 2, but why wouldn't you just as easily expect rows 2 and 3? It would be an arbitrary decision whether to return row 1 or row 3 since they both have the same value in the msgFrom field. Databases won't make arbitrary decisions like this. That's why distinctmust be applied to the entire list of select columns, not a subset. There are database-specific ways of grabbing the first matching rows. For example, have a look at
这个查询的困难不在于 Hibernate 本身，而在于一般的关系模型。在示例中，您说您期望第 1 行和第 2 行，但为什么您不能同样轻松地期望第 2 行和第 3 行？是否返回第 1 行或第 3 行将是一个任意决定，因为它们在 msgFrom 字段中具有相同的值。数据库不会做出这样的任意决定。这就是为什么distinct必须应用于整个选择列列表，而不是子集的原因。有一些特定于数据库的方法可以获取第一个匹配的行。例如，看看
Sometimes there will be a date column that you can use to decide which of the matching rows to return, but again the queries get somewhat complex:
有时会有一个日期列，您可以使用它来决定要返回哪些匹配的行，但查询再次变得有些复杂：
How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?
如何通过 SQL 中的另一列选择 MAX（列值）、DISTINCT 的行？
Fetch the row which has the Max value for a column
If you don't care about any of the other columns, you can just use a simple distinct, combined with Hibernate's constructor syntax (not tested):
如果您不关心任何其他列，您可以只使用一个简单的distinct，结合 Hibernate 的构造函数语法（未测试）：
select new Message(msgFrom) from (select distinct msgFrom from Message)
but you have to accept throwing away all the other columns.
但你必须接受扔掉所有其他列。
In the end, I often end up just doing this in code as a post query filter. Another option is to create a another table, say CurrentMessage, that includes msgFrom as part of the key. There will be more work in keeping this table up to date (you need to update a row everytime you add a row to the Message table) but querying will be much easier.
最后，我经常在代码中将其作为后查询过滤器执行。另一种选择是创建另一个表，比如 CurrentMessage，其中包括 msgFrom 作为键的一部分。将有更多的工作来保持此表的最新状态（每次向 Message 表添加一行时都需要更新一行），但查询会容易得多。
回答by JamesB
DetachedCriteria msgFromCriteria = DetachedCriteria.forClass(Message.class);
msgFromCriteria.setProjection(Projections.distinct(Projections.property("msgFrom")));
....
Criteria criteria = getSession().createCriteria(Message.class);
criteria.add(Subqueries.propertyIn("msgFrom", msgFromCriteria));
criteria.list();
