How to retrieve all entity with different column value?

Question

In the following criteria query

Criteria criteria = createLogRecordCriteria(maxId, playerId, playerStatus, userCategory, from, to);
criteria.setFirstResult(offset);
criteria.setMaxResults(limit);
criteria.setProjection(Projections.distinct(Projections.property("player")));
List lst = criteria.list(); 
return lst;

I retrieve only the set of different players , but I need to retireve all entities with different player value. How can it be done through criteria?

score 1 · Accepted Answer · edited May 23 '17 at 10:33

Believe this is the query that you are searching for.

For the below query:

select t from Table t where t.player IN (select distinct t.player from Table t );

if DetachedCriteria is a feasible option, the subCriteria can be passed to the mainCriteria written as below (provided that subCriteria should be defined as a DetachedCriteria):

Criteria subCriteria = Criteria.forClass(Table.class);
        subCriteria.setProjection(Projections.distinct(Projections.property("player")));
           
    
        Criteria mainCriteria = createLogRecordCriteria(maxId, playerId, playerStatus, userCategory, from, to);
        mainCriteria.add(Property.forName("t.player").in(subCriteria));
        
    //adding the extra restrictions provided in the Question.
    mainCriteria.setFirstResult(offset);
        mainCriteria.setMaxResults(limit);
    
        List lst = mainCriteria.list(); 
        return lst;

If not, you have to get the result of 'sub-criteria' and then pass the same (in the form of Object[] or Collection) as the parameter to

> mainCriteria.add(Property.forName("t.player").in(Result_Of_subQuery));

Hope this helps.

How to retrieve all entity with different column value?

1 Answers1