Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate's setFirstResult() issue with Oracle jdbc driver

I'm trying to do pagination with Hibernate using setFirstResult() and setMaxResults() but I'm not getting the expected results when setting the first result to 0.

When doing the following:

Query query = session.createQuery(queryString);  
query.setFirstResult(0);  
query.setMaxResults(30);  
List list = query.list();  //list.size() returns 10  

but if I set the first result to 1 (or anything different than 0 for that matter):

query.setFirstResult(1);  
query.setMaxResults(30);  
List list = query.list();  //list.size() returns 30  

I read this is a known bug in the jdbc driver, but I searched for a solution and I can't seem to find it. Has anyone run across anything similar and found a fix for it?

like image 792
Bernardo Avatar asked Feb 06 '09 16:02

Bernardo


2 Answers

Apparently adding setFetchSize() does the trick. So something like this now works perfectly:

query.setFirstResult(0);  
query.setMaxResults(30);  
query.setFetchSize(30);  
List list = query.list(); //list.size() now returns... wait for it... 30
like image 151
Bernardo Avatar answered Oct 21 '22 05:10

Bernardo


Another Solution is to implement your own Oracle Dialect:

public class Oracle10gDialectLimitBugfix extends Oracle10gDialect {
    @Override
    public boolean forceLimitUsage() {
        return true;
    }
}

See https://forum.hibernate.org/viewtopic.php?p=2379096

UPDATE: It seems to be fixed in Oracle 11.2.0.1.0

like image 31
Harald Brabenetz Avatar answered Oct 21 '22 07:10

Harald Brabenetz