Hibernate ScrollableResults Do Not Return The Whole Set of Results

Some of the queries we run have 100'000+ results and it takes forever to load them and then send them to the client. So I'm using ScrollableResults to have a paged results feature. But we're topping at roughly 50k results (never exactly the same amount of results).

I'm on an Oracle9i database, using the Oracle 10 drivers and Hibernate is configured to use the Oracle9 dialect. I tried with the latest JDBC driver (ojdbc6.jar) and the problem was reproduced.

We also followed some advice and added an ordering clause, but the problem was reproduced.

Here is a code snippet that illustrates what we do:

final int pageSize = 50;
Criteria crit = sess.createCriteria(ABC.class);
crit.add(Restrictions.eq("property", value));
crit.setFetchSize(pageSize);
crit.addOrder(Order.asc("property"));
ScrollableResults sr = crit.scroll();
...
...
ArrayList page = new ArrayList(pageSize);
do{
  for (Object entry : page)
    sess.evict(entry); //to avoid having our memory just explode out of proportion
  page.clear();
  for (int i =0 ; i < pageSize && ! metLastRow;  i++){
    if (sr.next())
      page.add(sr.get(0));
    else 
      metLastRow = true;
  }
  metLastRow = metLastRow?me开发者_如何学CtLastRow:sr.isLast();

  sendToClient(page);
}while(!metLastRow);

So, why is it that I get the result set to tell me its at the end when it should be having so much more results?

Your code snippet is missing important pieces, like the definitions of resultSet and page. But I wonder anyway, shouldn't the line

if (resultSet.next())

be rather

if (sr.next())

?

As a side note, AFAIK cleaning up superfluous objects from the persistence context could be achieved simply by calling

session.flush();
session.clear();

instead of looping through the collection of object to evict each separately. (Of course, this requires that the query is executed in its own independent session.)

Update: OK, next round of guesses :-)

Can you actually check what rows are sent to the client and compare that against the result of the equivalent SQL query directly against the DB? It would be good to know whether this code retrieves (and sends to the client all rows up to a certain limit, or only some rows (like every 2nd) from the whole resultset, or ... that could shed some light on the root cause.

Another thing you could try is

crit.setFirstResults(0).setMaxResults(200000);

As I had the same issue with a large project code based on List<E> instances, I wrote a really limited List implementation with only iterator support to browse a ScrollableResults without refactoring all services implementations and method prototypes.

This implementation is available in my IterableListScrollableResults.java Gist

It also regularly flushes Hibernate entities from session. Here is a way to use it, for instance when exporting all non archived entities from DB as a text file with a for loop:

Criteria criteria = getCurrentSession().createCriteria(LargeVolumeEntity.class);
criteria.add(Restrictions.eq("archived", Boolean.FALSE));
criteria.setReadOnly(true);
criteria.setCacheable(false);
List<E> result = new IterableListScrollableResults<E>(getCurrentSession(),
        criteria.scroll(ScrollMode.FORWARD_ONLY));
for(E entity : result) {
    dumpEntity(file, entity);
}

With the hope it may help