Hibernate criteria projection distinct

criteriahibernate

Hi i want to write a query using criteria :
The following query has to be created using criteria:

"Select Distinct(s2Taxper) from S2 where s2Tc='601' AND s2Txcd!=''"

thanks in advance

Best Answer

Criteria criteria = 
    session.createCriteria(S2.class)
           .add(Restrictions.eq("s2Tc","601"))
           .add(Restrictions.ne("s2Txcd",""))
           .setProjection(Projections.distinct(Projections.property("s2Taxper")));

Related Solutions

Java – JPA and Hibernate – Criteria vs. JPQL or HQL

I mostly prefer Criteria Queries for dynamic queries. For example it is much easier to add some ordering dynamically or leave some parts (e.g. restrictions) out depending on some parameter.

On the other hand I'm using HQL for static and complex queries, because it's much easier to understand/read HQL. Also, HQL is a bit more powerful, I think, e.g. for different join types.

Java – How to get distinct results in hibernate with joins and row-based limiting (paging)

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:

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

Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

Best Answer

Related Solutions

Java – JPA and Hibernate – Criteria vs. JPQL or HQL

Java – How to get distinct results in hibernate with joins and row-based limiting (paging)

Related Topic