Hibernate Criteria - 返回列不同的记录 [英] Hibernate Criteria -- return records where column is distinct
问题描述
示例数据库表:
Sample database table:
- ID = 1,msgFrom ='Hello',foobar ='meh'
- ID = 2,msgFrom ='Goodbye',foobar ='comment'
- ID = 3,msgFrom ='Hello',foobar ='response'
- ID = 1, msgFrom = 'Hello', foobar = 'meh'
- ID = 2, msgFrom = 'Goodbye', foobar = 'comments'
- ID = 3, msgFrom = 'Hello', foobar = 'response'
示例需要的输出(由hibernate查询生成):
Sample desired output (generated by hibernate query):
- ID = 1,msgFrom ='Hello',foobar ='meh'
- ID = 2,msgFrom ='Goodbye',foobar ='comments'
- ID = 1, msgFrom = 'Hello', foobar = 'meh'
- ID = 2, msgFrom = 'Goodbye', foobar = 'comments'
在上面的例子中,由于msgFrom列是相同的,所以第三条记录将被排除在结果之外。假设Java / Hibernate类被称为消息。我希望将结果作为Message对象列表(或可以转换为Message的对象)返回。如果可能,我想使用Criteria API。我在SO上看到了这个例子,它看起来很相似,但至今我仍然无法正确地实现它。
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.
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.
编辑:文章显示我基本上想要做什么。 http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-仅限列/
edit: Article showing basically what I want to do. http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-column-only/
推荐答案
请试试这个并让我知道
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());
}
这篇关于Hibernate Criteria - 返回列不同的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!