hibernate - How to set max result in DetachedCriteria?

Question

I am using DetachedCriteria and I just want the first result of the query so I want to do something like LIMIT 1 in DetachedCriteria. When I searched google I found setMaxResult but its Criteria.

How am I gonna do it in DetachedCriteria?

Answer 1

I was just looking into this too. I do not like the solution of having to use the criteria because this whole purpose of the DetachedCriteria is that it is defined when you do not have a Session yet. In the application I work on, this is also no an option because where the DetachedCriteria is created is nowhere near where it is actually executed.

Anyway, I found this neat trick to be able to define the limit when creating the DetachedCriteria. The DetachedCriteria takes Criterion instances, but the Criterion is called back with the actual Criteria (and CriteriaQuery) so you have the possibility to set the maxResults at that time.

The only catch is that Hibernate is already constructing the sql string and has already added an 'and ' to the query string because it is expecting a comparison statement. But we can fake this by just returning '1 = 1'.

See implementation of the LimitBy Criterion below.

public class Limit {
        private Limit() {}

        public static LimitBy by(int maxResults) {
            return new LimitBy(maxResults);
        }

        static class LimitBy implements Criterion {
            private int max;

            public LimitBy(int max) {
                this.max = max;
            }

            @Override
            public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
                criteria.setMaxResults(max);
                return "1 = 1";
            }

            @Override
            public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
                return new TypedValue[0];
            }
        }
    }

So with this LimitBy class, it is now possible to limit a DetachedCriteria using

DetachedCriteria.forClass(..)
  .add(Limit.by(1));

And now, this only returns the first result from the query.

This works on Postgres and is not tested on other db's but I expect it to work also for other db. Response when it does not work from some db.