CODEWITHSUNDEEP
hibernatedetachedcriteria
NinjaBoy
NinjaBoy

Reputation: 3755

hibernate - How to set max result in DetachedCriteria?

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?

Upvotes: 4

Views: 10235

Answers (3)

Bruno Queiroz
Bruno Queiroz

Reputation: 377

The solution posted by @Joost den Boer was not working for me, but I found a workaround using a similar approach:

public class PageRestrictions {

    public static class Page implements Criterion {

        private Integer limit;

        private Integer offset;

        private Order order;

        public Page offset(Integer offset) {
            this.offset = offset;
            return this;
        }

        public Page limit(Integer limit) {
            this.limit = limit;
            return this;
        }

        public Page by(Order order) {
            this.order = order;
            return this;
        }

        @Override
        public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {

            StringBuilder builder = new StringBuilder("1=1");

            if (this.order != null) {
                builder.append(" order by ").append(this.order.toSqlString(criteria, criteriaQuery));
            }

            if (this.offset != null) {
                builder.append(" offset ").append(this.offset);
            }

            if (this.limit != null) {
                builder.append(" limit ").append(this.limit);
            }

            return builder.toString();
        }

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

    public static Page offset(Integer offset) {
        Page page = new Page();
        page.offset(offset);
        return page;
    }

    public static Page limit(Integer limit) {
        Page page = new Page();
        page.limit(limit);
        return page;
    }

    public static Page by(Order order) {
        Page page = new Page();
        page.by(order);
        return page;
    }
}

Upvotes: 0

Mitul
Mitul

Reputation: 131

WITH CRITERIA API YOU CAN USE 

detachedcrit.getExecutableCriteria(session).SetMaxResults(1)

Upvotes: 0

Joost den Boer
Joost den Boer

Reputation: 5017

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.

Upvotes: 2

Related Questions