CODEWITHSUNDEEP
javaoracle-databasehibernatejdbc
Bernardo
Bernardo

Reputation: 1053

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?

Upvotes: 9

Views: 7934

Answers (2)

Harald Brabenetz
Harald Brabenetz

Reputation: 494

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

Upvotes: 1

Bernardo
Bernardo

Reputation: 1053

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

Upvotes: 14

Related Questions