Is there a better way than using DetachedCriteria for pagination?

Question

Sorting and paging with Hibernate's Criteria api comes with a big restriction, it needs to retrieve distinct results from the database. Tools provided by the api like DistinctRootTransformer won't work, because it is applied after retrieving the entities from db and thus breaks paging and sorting. The only way to get distinct results of a query with restrictions on an association is by limiting the resultset by a DetachedCriteria:

DetachedCriteria dc = DetachedCriteria.forClass(Household.class, "h")
                .createAlias("cats", "c", JoinType.LEFT_OUTER_JOIN)
                .add(Restrictions.or(
                        Restrictions.isEmpty("cats"),
                        Restrictions.ne("c.name", "Sylvester")
                ))
                .setProjection(Projections.distinct(Projections.property("h.id")));

Criteria criteria = session.createCriteria(Household.class)
                .add(Property.forName("id").in(dc));

...apply paging, sorting and filtering to criteria.

Does anybody know a better approach such as omitting subqueries and use joins without breaking pagination? My goal is to find a solution that is reusable, like passing only a criteria to another method that applies paging, sorting and filtering.

Update:

The following code does not work. Because of the join I have to use a Resulttransformer, to get distinct results. However, it is applied after sorting and paging.

Criteria criteria = session.createCriteria(Household.class)
   .createAlias("cats", "c", JoinType.LEFT_OUTER_JOIN)
   .add(Restrictions.ne("c.name","Sylvester"))
   .setFirstResult((page - 1) * pagesize)
   .setMaxResults(pagesize)
   .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

E.g. debugging the sql the database would return something like that:

household_id=1,...cat_ids={1,2};household_id=1,...cat_ids={1,2};household_id=2,...cat_ids={1};

In this example, setting pagesize to 1 and viewing page 2 should return the uid 2, because there are only two distinct users. But as you can see in the database output, it returns the wrong uid 1, because Resulttransformers kicks in afterwards.

How do you apply sorting to your criteria? To ensure no repeats across pages I believe you must impose a total ordering. The detached criteria could be sorted, but you project it down to just an ID, so you can only sort by ID. Consider example: get page 1 size 50 blue shirts of 150 blue shirts in the catalog sorted by price ascending; when the user requests page 2, all should be unseen and more expensive, right? How to guarantee? (Assume some one-to-many relationships on shirts like fabric, collection, etc. that require joins.) — chrisinmtown, Apr 02 '18 at 20:51

unit · Answer 1 · 2017-04-07T09:58:11.220

If you create DetachedCriteria in some method and then work with it, you can try to convert DetachedCriteria to Criteria using something like:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Your.class);
// then add all your restrictions and convert
Criteria criteria = detachedCriteria.getExecutableCriteria(session);

And then apply you pagination

criteria.setFirstResult(pageable.getPage() * pageable.getSize());
criteria.setMaxResults(pageable.getSize());

And then execute criteria. For example:

List<T> result = criteria.list();

score 1 · Answer 2 · answered Nov 12 '15 at 11:17

1

When I need to paginate, I never use DetachedCriteria, but instead normal Criteria, controlling first and max results.

From view I determine which page I need to show and once I have prepared the critera I configure this way:

criteria.setMaxResults(lazyQuery.getPageSize());
criteria.setFirstResult(layQuery.getStart());

lazyQuery is an object of my own model, used for view and business logic. This works perfect.

answered Nov 12 '15 at 11:17

malaguna

4,183
1
17
33

2

But how to you make sure that the criteria has unique results? If this criteria has any joins to another entity, pagination and sorting will be broken, because maxResults,firstResult and sorting are applied to redundant data at database level. This can easily happen if you apply an alias to the main criteria instead to the detached one, which I showed in my code snippet above. – Journeycorner Nov 12 '15 at 14:05
I have been using them in very complex criterias with several joins, using aliases and sorting results, and I have never run in the problems you described. – malaguna Nov 12 '15 at 15:06
I updated my post to show a case, where it would not work to apply setMaxResults to arbitrary queries. Point is: sometimes you either have to use DetachedCriterias/subqueries or joins. – Journeycorner Nov 12 '15 at 18:18
@Journeycorner now I have just understand your problem. The conflict is with `DISTINCT_ROOT_ENTITY` resultTransformer. Sorry, I can't help you, but I found this [thread](http://stackoverflow.com/questions/18276230/hibernate-criteria-distinct-entities-and-then-limit) where similar query is solved with `criteria.setFetchMode("xxxxxxx", FetchMode.SELECT)` – malaguna Nov 12 '15 at 18:32
Looks good, but they did not apply any restrictions on the joined association. I need to figure out if FetchMode.SELECT can subsitute a real join here. – Journeycorner Nov 12 '15 at 20:40

Is there a better way than using DetachedCriteria for pagination?

2 Answers2