What is the best way to limit results using a DetachedCriteria of Hibernate in Java?

Question

I'm using Hibernate 3.5.6-Final in Java. Since I don't have access to the Hibernate Session, I'm using a DetachedCriteria. So, I would like to know what is the best way to limit the results for a DetachedCriteria (in my case I would like to get only the first row).

Additional info:

The Criteria class has some methods to achieve this, like setMaxResults(int maxResults) or setFirstResult(int firstResult), but the DetachedCriteria doesn't have neither. Again, I can't use the Criteria because I don't have access to the Hibernate's Session.

score 10 · Accepted Answer · edited Oct 27 '12 at 07:42

10

This is how i am doing it, you have to wrap your result into execute block. EntityMAnager in the example below is org.springframework.orm.hibernate3.HibernateOperations object :

final DetachedCriteria criteria = DetachedCriteria.forClass(ActivePropertyView.class);
criteria.add(Restrictions.eq("featured", true));
List<ActivePropertyView> result = entityManager.execute(
     new HibernateCallback<List<ActivePropertyView>>() {
         @Override
         public List<ActivePropertyView> doInHibernate(Session session) 
                                         throws HibernateException,SQLException {
                 return criteria.getExecutableCriteria(session).setMaxResults(5).list();
         }
     });
return result;

edited Oct 27 '12 at 07:42

Grisha Weintraub

7,803
1
25
45

answered Oct 27 '12 at 06:54

iririr

116
1
3

It seems like this approach is the best because the `DetachedCriteria` will always arrive at the repository layer where the _Hibernate Session_ is. Thanks! – Rubens Mariuzzo Dec 10 '12 at 14:17
This one will work fine only when it has single object. What if this detached object is having association and that association is have multiple records. This MaxResults(1) is getting only associated object even it has more results – sats Sep 26 '15 at 02:19

score 6 · Answer 2 · answered Jul 17 '12 at 01:25

6

You can use Restrictions.sqlRestriction() add plain SQL expression to DetachedCriteria. Try this:

DetachedCriteria criteria = DetachedCriteria.forClass(Domain.class)
  .add(Restrictions.sqlRestriction("LIMIT 1"));

answered Jul 17 '12 at 01:25

yorkw

40,926
10
117
130

2

this is not working in mysql case. I observed that 'limit 1' shown as condition in WHERE clause which is not a valid sql statement. – Arun Kumar Mudraboyina Feb 04 '15 at 09:52
1

A workaround is to put `DetachedCriteria criteria = DetachedCriteria.forClass(Domain.class) .add(Restrictions.sqlRestriction("1=1 LIMIT 1"));` – Dany Y Aug 13 '15 at 17:35
1

At least PostgreSQL requires `LIMIT` statement to be physically behind `ORDER BY`. This approach insert it between `WHERE` and `ORDER BY`, if you specified one. But if you didn't order your results, how can you get consistent results? – Vlastimil Ovčáčík Apr 06 '16 at 13:22

score 4 · Answer 3 · answered Mar 23 '16 at 15:35

4

If you are using hibernate templates, there is a findByCriteria(criteria, firstResult, maxResults). Templates are discouraged, but for those with legacy code this is available.

answered Mar 23 '16 at 15:35

Nielsvh

1,151
1
18
31

best answer ever – Alper Fırat Kaya Sep 25 '18 at 08:15

score 2 · Answer 4 · answered Jul 16 '12 at 15:52

2

If I'm reading the javadoc right, you cannot limit until you finally convert the DetachedCriteria to a real Criteria (when you do have a Session)

answered Jul 16 '12 at 15:52

MJB

9,352
6
34
49

Thanks for amending my blocking point, but I still would like to know _how to do it_. Seems like the answer given by @iririr is the best approach by the moment to handle this scenario. – Rubens Mariuzzo Dec 10 '12 at 14:19
Why not use your own ExtendedDetachedCriteriaClass that extends Criteria and adds limit and offset. Then set those when you actually have the session. – MJB Mar 22 '13 at 00:20

score 1 · Answer 5 · answered Apr 23 '15 at 22:28

1

Expanding on yorkw's answer, the Oracle equivalent is:

DetachedCriteria criteria = DetachedCriteria.forClass(Domain.class)
  .add(Restrictions.sqlRestriction("rownum < 1"));

It will add the row limit to the "where" clause in the subquery.

answered Apr 23 '15 at 22:28

user2812481

124
5

score -1 · Answer 6 · answered Jul 29 '17 at 15:57

-1

detachedCriteria.getExecutableCriteria(getSession()).setMaxResults(1);

answered Jul 29 '17 at 15:57

mjs

21,431
31
118
200

What is the best way to limit results using a DetachedCriteria of Hibernate in Java?

6 Answers6

Linked