CODEWITHSUNDEEP
javahibernatehqldistinct
Kirill Reznikov
Kirill Reznikov

Reputation: 2357

Hibernate SELECT DISTINCT behavior

I hope that this question is not duplicate, because in another case I use google badly (shame on me) :-)

So, I can't find exact and clear answer for my question: Is "SELECT DISTINCT ..." in hql applied before setFirstResult() and setMaxResults()? I want to be calm that I will not have problems with pagination in my app.

Upvotes: 2

Views: 15104

Answers (2)

JamesENL
JamesENL

Reputation: 6540

To answer the actual question, yes the distinct result set would be determined before the limit would be applied. The setFirstResult() and setMaxResults() methods essentially are equivalent to a LIMIT clause on the query.

If you use both you are requesting that the distinct result set be limited between the indexes given to your two method calls above.

An example:

You have a result set with 100 records. There are 20 distinct records found for column X. You set up the following criteria:

Criteria critiera = getCurrentSession().createCritiera(Foo.class);
criteria.add(Projections.distinct(Projections.property("X")));
criteria.setFirstResult(0);
criteria.setMaxResults(10);
List<Foo> fooList = critiera.list();

fooList would contain 10 results, from the start of the distinct result set of 20 results.

If you changed the above to

criteria.setFirstResult(4);
criteria.setMaxResults(20);

fooList would contain 15 results omitting the first 5 results.

It could be ambiguous how the result set would be ordered, so you may not get the same 10 results every time.

Upvotes: 2

chenthil
chenthil

Reputation: 51

SELECT DISTINCT

When the keyword DISTINCT is used after SELECT, it works as a shortcut replacement for a simple GROUP BY clause. The expressions in the SELECT list are used directly as the . The following examples of SELECT DISTINCT and SELECT with GROUP BY are equivalent. From reference documentation

SELECT DISTINCT d, e + f FROM atable WHERE a + b = c
 SELECT d, e + f FROM atable WHERE a + b = c GROUP BY d, e + f`

Upvotes: 0

Related Questions