Question on Hibernate Pagination

mister_ian · **Joined:** Wed Jun 11, 2008 7:02 am **Posts:** 3

Hello,

I'm trying to create a paginated list, but every time I use the setMaxResults and setFirstResult methods, my code breaks.

Here is the code I'm using. I set the setFirstResult to 0 temporarily since I couldn't get it to work anyway:

Code:

public List<Employee> getEmployeesByCriteria(Map<String, Object> criteriaMap) {
      
      session = HibernateUtil.getSessionFactory().getCurrentSession();
      tx = session.beginTransaction();
      Criteria criteria = session.createCriteria(Employee.class);
      criteria.setFirstResult(0);
      criteria.setMaxResults(10);
      
      if (criteriaMap.get("hireDateFrom") != null && criteriaMap.get("hireDateTo") != null) {
         criteria.add(Restrictions.between("hireDate", criteriaMap.get("hireDateFrom"), criteriaMap.get("hireDateTo")));
      } else if (criteriaMap.get("hireDateFrom") != null) {
         criteria.add(Restrictions.ge("hireDate", criteriaMap.get("hireDateFrom")));
      } else if (criteriaMap.get("hireDateTo") != null) {
         criteria.add(Restrictions.le("hireDate", criteriaMap.get("hireDateTo")));
      }
      
      if (criteriaMap.get("salary") != null) {
         criteria.add(Restrictions.le("salary", criteriaMap.get("salary")));
      }
      
      if (criteriaMap.get("position") != null) {
         criteria.add(Restrictions.eq("jobId", criteriaMap.get("position")));
      }
      
      List<Employee> results = criteria.list();
      tx.commit();
      return results;
   }

Commenting out the two mentioned methods allows my code to work, but obviously without the pagination.

Here is the stack trace appearing in my logs:

Quote:

exception

javax.servlet.ServletException: could not execute query
org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:515)
org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:419)

root cause

org.hibernate.exception.SQLGrammarException: could not execute query
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
org.hibernate.loader.Loader.doList(Loader.java:2216)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
org.hibernate.loader.Loader.list(Loader.java:2099)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
oracle.dao.EmployeeHibernateDAO.getEmployees(EmployeeHibernateDAO.java:35)
oracle.service.EmployeeService.getEmployees(EmployeeService.java:12)
oracle.DisplayValuesAction.execute(DisplayValuesAction.java:42)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)

Am I using these methods incorrectly? I tried copying code from online tutorials, but they all end up with the same result. I'm using Hibernate 3 if it makes a difference.

Thanks in advance![/code]

Cameron McKenzie · **Posted:** Wed Jun 11, 2008 9:30 am

Which database are you using? It's not SqlServer is it?

Could you try a different starting point? Maybe a 1, although 0 should work.

Are you getting any SQL generated?

mister_ian · **Joined:** Wed Jun 11, 2008 7:02 am **Posts:** 3

I'm using the default tables from Oracle 10g. And yes, I'm getting generated SQL. I just didn't use the setMaxResults() method and used the subList() method of the List interface instead to make my pagination work.

Thanks a lot! :)

VoodooChile · **Joined:** Thu Nov 10, 2005 4:24 am **Posts:** 8

mister_ian wrote:

I'm using the default tables from Oracle 10g. And yes, I'm getting generated SQL. I just didn't use the setMaxResults() method and used the subList() method of the List interface instead to make my pagination work.

Thanks a lot! :)

can you post the generated SQL?

Are you using the correct oracle dialect?

If I'm not mistaken, oracle dialect will throw a query around the actual query that will filter the rownum s of the inner query.

SQLGrammarException means (I think) you're using a wrong dialect or your base query is incorrect (or something else causing bad SQL)

mister_ian · **Joined:** Wed Jun 11, 2008 7:02 am **Posts:** 3

VoodooChile wrote:

can you post the generated SQL?

Are you using the correct oracle dialect?

If I'm not mistaken, oracle dialect will throw a query around the actual query that will filter the rownum s of the inner query.

SQLGrammarException means (I think) you're using a wrong dialect or your base query is incorrect (or something else causing bad SQL)

Looks that way, although I still can't figure out why adding that one method makes everything fail, especially considering that I copy-pasted the code from an online tutorial.

I changed my code to:

Code:

public List<Employee> getEmployees(int currentPage) {
      session = HibernateUtil.getSessionFactory().getCurrentSession();
      tx = session.beginTransaction();
      
      Criteria criteria = session.createCriteria(Employee.class)
         .addOrder(Order.asc("employeeId"))
         .setFirstResult(1)
         .setMaxResults(10);
      
      List<Employee> results = criteria.list();
      tx.commit();
      return results;
   }

And here's what appears in the stack trace:

Quote:

java.sql.SQLException: ORA-00923: FROM keyword not found where expected
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745) oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

The generated SQL is as follows. It looks like the generated "limit" keyword is what's causing the error:

Quote:

select limit ? ? this_.EMPLOYEE_ID as EMPLOYEE1_0_0_,
this_.MANAGER_ID as MANAGER2_0_0_, this_.FIRST_NAME as FIRST3_0_0_, this_.LAST_NAME
as LAST4_0_0_, this_.EMAIL as EMAIL0_0_, this_.PHONE_NUMBER as PHONE6_0_0_,
this_.HIRE_DATE as HIRE7_0_0_, this_.JOB_ID as JOB8_0_0_, this_.SALARY as SALARY0_0_,
this_.COMMISSION_PCT as COMMISSION10_0_0_, this_.DEPARTMENT_ID as DEPARTMENT11_0_0_ from
EMPLOYEES this_ order by this_.EMPLOYEE_ID asc

The Oracle10g dialect is also correctly specified in my cfg.xml file:

Quote:

<session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.password">hr</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521/xe</property>
<property name="hibernate.connection.username">hr</property>
<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="show_sql">true</property>
<mapping resource="oracle/model/Employee.hbm.xml"/>
</session-factory>

VoodooChile · **Joined:** Thu Nov 10, 2005 4:24 am **Posts:** 8

please look more careful! I see:

<property name="dialect">org.hibernate.dialect.HSQLDialect</property>

in your config !!

So you're not using the 10gDialect!

See the difference (and the appearance in your generated queries) of
the Dialect.getLimitString between 10gdialect and hsqldialect...