Duplicate records with hibernate joins and pagination

Question

I'm using hibernate 3.6.3 Final version (I know it's pretty old, but that is like that for now on this project I'm currently working on).
I have a problem with doing joins and pagination in order that I am getting one record duplicated in results which is caused by hibernate.
This is the code I have:

public Page<T> findByCriteriaPaginated(PageParams params, Criteria countCriteria, Criteria listCriteria, String[] joins) {
    Page<T> page = new Page<T>(params);
    // count criteria
    countCriteria.setProjection(Projections.rowCount());
    page.setTotalCount(((Long) countCriteria.uniqueResult()).intValue());
    // fetch criteria
    listCriteria.setFirstResult(params.getFirstResultIdx());
    listCriteria.setMaxResults(params.getPageSize());
    if (params.getOrdering() != null && params.getOrdering().getSize() > 0) {
        for (Iterator<String> it = params.getOrdering().getKeyIterator(); it.hasNext();) {
            String key = it.next();
            if (params.getOrdering().isAscending(key)) {
                listCriteria.addOrder(Order.asc(key));
            } else {
                listCriteria.addOrder(Order.desc(key));
            }
        }
    }
    if (joins != null && joins.length > 0) {
        for (String s : joins) {
            listCriteria.setFetchMode(s, FetchMode.JOIN);
        }
    }
    page.setResults(listCriteria.list());
    return page;
}

When I hit the query that is generated and run it on DB server, then I don't have this duplicate record. But debuging my code, listCriteria.list() returns data set with duplicate. Also when I comment out these two lines listCriteria.setFirstResult(params.getFirstResultIdx()); listCriteria.setMaxResults(params.getPageSize());, then listCriteria.list() has no duplicate, it is fine.
So, this indicates to me that there is some problem with pagination and the rest of criteria I'm having (with joins and ordering).
Does anybody have idea how to fix this? Is this hibernate bug? Would increasing hibernate version to the latest (5.2.9.Final) help? Are there any potential problems with such large version upgrade?
Thank you for any kind of help.

mismas

score 2 · Accepted Answer · answered Mar 20 '17 at 19:53

2

Two things:

If you see duplicate rows in the same page, then you have issues with your joins. Try to log the SQL query, then execute it manually. The best way to solve this issue is with criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
For your pagination, if getOrdering() is empty, you do not add any order by. Although, to paginate correctly, you absolutely need an order by clause. Add listCriteria.addOrder(Order.asc("id")); to your code so the Id is a last resort ordering.

answered Mar 20 '17 at 19:53

Guillaume F.

5,905
2
31
59

1

Duplicate rows are on different pages. I've tried to execute SQL query manually and there is no duplicate records in results. Ordering in this case is also given. Do you think this is hibernate bug? – mismas Mar 20 '17 at 21:23
2

Thank you! The problem was with ordering after all. Not that it was missing but it was ambiguous. I had ordering by created date which was oracle date data type and data in db was with the same created date value. So ordering wasn't unambiguous. After adding ordering by id also (along with created date) everything worked. Thank you again! – mismas Mar 21 '17 at 10:58
Have similar query interface. @Guillaume's solutions of adding DISTINCT_ROOT_ENTITY and ensuring an unambigous order result in no dupes. But I see short paginated results; e.g., ask for 20 items, only get 19. I believe the SQL limit is sent as 20, then the transformer squeezes out 1 dupe yielding 19 unique rows/objects. Is there a workaround here? – chrisinmtown Mar 26 '18 at 15:58
@chrisinmtown ; Yes, you have to make sure that your query doesn't return multiple root elements. That occurs with `oneToMany join fetch` entities. You can try lazy joins if that helps. – Guillaume F. Mar 26 '18 at 22:51
Thanks. Works as expected after I set FETCH_MODE to SELECT on all the *-to-many fields as discussed in https://stackoverflow.com/questions/11038234/pagination-with-hibernate-criteria-and-distinct-root-entity – chrisinmtown Mar 27 '18 at 01:07

Duplicate records with hibernate joins and pagination

1 Answers1