Criteria API returns a too small resultset

Question

How is this possible, I have to following criteria

Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();

The size of list is now 20. If I add a max results to the criteria,

Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(90);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();

.. now list's size is 18!

I don't understand how the resultsets size can be smaller after defining the max results, as the amount of rows is smaller than the defined max. This sure seems like a bug, or is there again some weird aspects of hibernate that I'm not aware of?

---

If you're looking for an answer to this question, make sure to read the accepted answer and its comments.

Toughminded

Could you add the hibernate mapping and the executed SQL query? – Cule

Thomas, unfortunately no, because it's quite a complex and deep data structure -- even for just the relevant POJOs. I'll try to isolate the problem to a smaller test case, but your response may very well be the key to my problem. – Toughminded

Yes, but what I wonder is why it worked with pure HQL-queries (we switched to use the Criteria API instead). I'm not very fond of the documentation stating that it may work with future versions of Hibernate, but there are no references to when that documentation was updated, so I have no idea if the case is still valid or not, since it worked with HQL. – Toughminded

As far as I know the problem is not fixed. – Cule

Great question. Just what I needed! – Whitethorn

Answer 1

What is happening here can be seen very clearly by turning on SQL debugging in Hibernate and comparing the generated queries.

Using a fairly simple Sale → Item one-to-many mapping (which is hopefully self-explanatory), a Criteria-based query like this:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Sale.class);
c.createAlias("items", "i");
c.add(Restrictions.eq("i.name", "doll"));
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.setMaxResults(2);

produces SQL like this:

select top ? this_.saleId as saleId1_1_, ... 
from Sale this_ 
inner join Sale_Item items3_ on this_.saleId=items3_.Sale_saleId 
inner join Item items1_ on items3_.items_id=items1_.id 
where items1_.name=?

whereas a Query like this:

Query q = sessionFactory.getCurrentSession().createQuery("select distinct s from Sale s join s.items as i where i.name=:name");
q.setParameter("name", "doll");
q.setMaxResults(2);

produces something like:

select top ? distinct hibernated0_.saleId as saleId1_ 
from Sale hibernated0_ 
inner join Sale_Item items1_ on hibernated0_.saleId=items1_.Sale_saleId 
inner join Item hibernated2_ on items1_.items_id=hibernated2_.id 
where hibernated2_.name=?

Note the difference in the very first line (DISTINCT). A ResultTransformer like DISTINCT_ROOT_ENTITY is a Java class, which processes the results of the SQL rows after the SQL is executed. Therefore, when you specify a maxResults, that will be applied as a row limit on the SQL; the SQL includes a join onto the elements in the Collection, so you're limiting your SQL result to 90 sub-elements. Once the DISTINCT_ROOT_ENTITY transformer is applied, that may result in less than 20 root elements, purely dependent on which root elements happen to come out first in the 90 joined results.

DISTINCT in HQL behaves very differently, in that that actually uses the SQL DISTINCT keyword, which is applied before the row limit. Therefore, this behaves as you expect, and explains the difference between the 2.

In theory you should be looking at setProjection to apply a projection at the SQL level -- something like c.setProjection(Projections.distinct(Projections.rootEntity())) -- but unfortunately Projections.rootEntity() doesn't exist, I just made it up. Perhaps it should!

Chrysalis

Is there any known (efficient) workaround for this problem? This clearly makes the Criteria API unusable for us :( – Toughminded

Not a good one, that I'm aware of. If you're not dealing with a large amount of raw data, you can always select them all, use DISTINCT_ROOT_ENTITY, and then take the first n items of the resluting List. No, not scalable, and yes, rather yuck. Unfortunately don't have any better suggestions -- unless anyone else knows any other possibilities, the Criteria API may not be for you in this particular case. – Chrysalis

That's the problem, queries with dynamically injected criterions, lots of data.. sigh – Toughminded

For anyone looking for an answer to this question, I made a workaround by using two queries. First I had my normal criteria with all the restrictions and limitations I wanted to have but with an added projection criteria.setProjection(Projections.distinct(Projections.id())); With this criteria I got a list of ids of the pojos I wanted to fetch. After this I created another criteria with the Restrictions.in("id",ids); The second criteria contained the DISTINCT_ROOT_ENTITY, but not the setMaxResult (as it was applied for the first query). – Toughminded

Nice. Using 2 queries is a bit icky but that's about as good as you're going to get. Good solution. – Chrysalis

The problem with projecting ids is that if you have different orders that could be applied to the criteria, this wouldn't work either. – Duluth

@Chrysalis So basically use HQL in these use cases? – Saimon

Answer 2

The setMaxResults does not work with outer join SQL queries. Maybe this is your problem: Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?.

Cule

Answer 3

Another solution is the following:

1. run your criteria.list() without setting any alias => the referenced sets/list of the root entity will be filled with proxies => here you set correctly the max results and such
2. run the alias criteria on its own in the same hibernate session => the above proxies will be initialized

Something like this:

Criteria criteria = this.getSession().createCriteria(User.class);
criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
criteria.setMaxResults(10);

// First get the results without joining with the other tables
List<User> results = criteria.list();

// at this point the set roles is filled with proxies
// we'll now create and execute the join so these proxies are filled since we're still in the same session
getSession().createCriteria(User.class, "u")
        .createAlias("u.roles", "r", CriteriaSpecification.LEFT_JOIN)
        .list();

return results;

Hope this can help,
Stijn

Sanasanabria

Answer 4

Hope this can be help

public List<Employee> getData(int to, int from) {

    Criteria hCriteria = null;
    List<Employee> viewDataList = null;
    List<Employee> exactDataList = null;
    try {

        hSession = HibernateSessionFactory.getSession();
        hTransaction = hSession.beginTransaction();
        hCriteria = hSession.createCriteria(Employee.class);

        /*
        hCriteria.setFirstResult(to);
        hCriteria.setFirstResult(from);
        */
        hCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        viewDataList = hCriteria.list();

        // for limit
        exactDataList=viewDataList.subList(from,to);

        hTransaction.commit();
    } catch (Exception e) {
        hTransaction.rollback();

    } finally {
        try {
            hSession.flush();
            HibernateSessionFactory.closeSession();
        } catch (Exception hExp) {
        }

}

    return exactDataList;
}

Ender

Answer 5

This is a known problem in hibernate. Look at @Cowan for a generated SQL and an explanation of the problem. There is an open bug request for this in their jira. Let's hope that someone comes along and fixes it :)

https://hibernate.atlassian.net/browse/HB-520

Daimon