Hibernate pagination

Pagination pattern is very popular used in application development. Its usage is to reduce memory footprint on the application server while retrieving huge data from database. In SQL, it could be done in following way:

select * from (
   select r, blah, blah, blah from (
      select rownum as row, tabA.* from tableA tabA
   )
   where row < pageNumber*rowPerPage
)
where row >= pageNumber-1*rowPerPage

Unfortunately, I wasn’t using Spring JdbcTemplate for the work. To recap, I have my jdbcTemplate being configured in such a way:

    <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:/comp/env/jdbc/MyApp"/>
        <property name="lookupOnStartup" value="false"/>
        <property name="cache" value="true"/>
        <property name="proxyInterface" value="javax.sql.DataSource"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

Instead, I was using Hibernate for the work. To recap, I have the Hibernate session being configured as following way:

    <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">

        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>

        <property name="hibernateProperties">
            …
        </property>

       <property name="annotatedClasses">
            …
        </property>
    </bean>

    <bean id="theDAO" class="org.huahsin.theDAOImpl">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

In order to achieve the same thing as in SQL, I have the Hibernate query done in the following way:

public List getFunction(int pageNumber, int rowPerPage) {
   Criteria criteria = theDAO.getCurrentSession().createCriteria(ModelA.class, "modA");
   criteria.setMaxResults(rowPerPage);
   criteria.setFirstResult((pageNumber - 1) * rowPerPage);
   return criteria.list();
}