Hibernate - setMaxResults() with DB2 issue

To fetch first 10 rows from the database using DB2, we can use a query as below

SELECT * FROM EMPLOYEE

FETCH FIRST 10 ROWS ONLY

If you wanna use Hibernate as your persistence framework, then the equivalent would be

Configuration cfg = new Configuration();

SessionFactory sf = cfg.configure().buildSessionFactory();

// open session

Session sess = sf.openSession();

Query query = sess.createQuery("from Employee");

query.setMaxResults(10);

List userList = query.list();

In an ideal world, thats supposed to work. But unfortunately it doesn''t and throws a runtime error due to the faulty SQL generated by hibernate. Thats weird but true. Fortunately, the solution is already available in the hibernate forum itself (http://opensource.atlassian.com/projects/hibernate/browse/HHH-2389).

The problem seems to be with the Db2Dialect class, that forms the FETCH FIRST clause of the SQL query for DB2 in the getLimitString() method. The fix listed in the same link can't be any simpler. Just add the following class to your project and you are all set.

/**  

* @author Nicolas Billard  *  

*Overrides DB2 dialect to limit number of results.  

*/

 public class DB2Dialect extends org.hibernate.dialect.DB2Dialect {   

public boolean supportsLimitOffset() {   

 return false; 

 }   

public boolean supportsVariableLimit() {   

 return false;  

}    

public String getLimitString(String query, int offset, int limit) {      

 if (offset > 0) {    

  return     "select * from (select rownumber() over () as rownumber, t.* from (" +     

    query +     " fetch first " + limit + " row only " +     

    ") as t) as t where rownumber > " + offset;   

 }      //   return query + " fetch first " + limit + " row only ";   

}   

I tested this fix with DB2 8.0 and Hibernate 3.5 and it works like a charm. Happy Hibernating.