Search code examples
hibernatedistincthibernate-criteria

Hibernate select distinct return only id

class Cat {
    long id;
    String name;
    int age
}

List<Cat> results = (List<Cat>) session.createCriteria(Cat.class)
    .setProjection( Projections.projectionList()
        .add( Projections.distinct(Projections.property("id")) )
    )
    .list();

It works perfectly without set projection, but has duplicates by id. When we add set projection, the query return only a list if ids(1, 2, 3).

How to change to return a list of cats and non duplicate id?

https://hibernate.atlassian.net/browse/HHH-12437

Solution

  • One option is to use setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) on the criteria object. But it is implemented not in a database side - it's programically filtered (after the DB query is fired, Hibernate filters for distinct rows).

    Another better option is to use setResultTransformer(new AliasToBeanResultTransformer(Cat.class)) and you need to use projection with distinct as above and also have projection on all the properties that you want to populate in the Cat entity. This uses distinct keyword in the SQL query and so filtering happens in the DB)

    You can go through this hibernate forum - select distinct entities using Criteria for an example and more information on the related discussion.