Geek asks and answers

Hibernation criteria - returns records where the column is different

Example database table:

  • ID = 1, msgFrom = 'Hello', foobar = 'meh'
  • ID = 2, msgFrom = 'Goodbye', foobar = 'comments'
  • ID = 3, msgFrom = 'Hello', foobar = 'response'

An example of the desired output (generated by a sleep request):

  • ID = 1, msgFrom = 'Hello', foobar = 'meh'
  • ID = 2, msgFrom = 'Goodbye', foobar = 'comments'

In the above example, the third record will be excluded from the results, since the msgFrom column is the same. Let them say that 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 passed to Message, anyway). I want to use the criteria API, if possible. I saw this example on SO and it seems similar, but so far I can not implement it correctly.

 select e from Message e 
    where e.msgFrom IN (select distinct m.msgFrom 
                          from Message m
                          WHERE m.msgTo = ? 
                          AND m.msgCheck = 0");

, , - , , - .

edit: , , . http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-column-only/

+5
3

,

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());
}
+6

Hibernate, . , 1 2, 2 3? , 1 3, msgFrom. . distinct , . . ,

SELECT DISTINCT

, , , - :

MAX ( ), DISTINCT SQL?

- , distinct, Hibernate ( ):

select new Message(msgFrom) from (select distinct msgFrom from Message)

.

, , post-post. - , CurrentMessage, msgFrom . ( , ), .

+2
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();
0
source

More articles:


All Articles