hibernate - Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct

Question

I am pretty new to Hibernate. I found out that we can get distinct result using following two different ways. Could any one tell me what is the difference between them? When to use one over other?

Projections.distinct(Projections.property("id"));

vs

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

question from:https://stackoverflow.com/questions/25536868/criteria-distinct-root-entity-vs-projections-distinct

Answer 1

While similar names, the usage is different.

I. Projections.distinct(Projections.property("id"));

this statement would be translated into SQL Statement. It will be passed to DB Engine and executed as a SQL DISTINCT. See:

• 17.9. Projections, aggregation and grouping

so e.g. this example:

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

would seems like:

SELECT DISTINCT(cat_id) FROM cat_table

II. criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

This statement is executed ex-post. Once the SQL query from DB engine is returned and Hibernate iterates the result set to convert that into list of our entities.

But is it needed always? NO, mostly this is not needed.

The only case, when we MUST to use that, if there is an association in the query - JOINING the one-to-many end.

Because if we do have one cat and its two kittens, this would return two rows, while cat is only one:

SELECT cat.*, kitten.*
FROM cat_table as cat 
  INNER JOIN kitten_table kitten ON kitten.cat_id = cat.cat_id

So, the statement at the end of the criteriaQuery:

... // criteriaQuery joining root and some one-to-many
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)

would result in a list with only one cat.