Pagination with HibernateTemplate's findByNamedParam function

I've seen lots of examples of how to create pagination with some really simple queries. But I don't see any using HibernateTemplate's findByNamedParam method.

How can I set a que开发者_Go百科ry's firstResult and maxResult parameters while also using the findByNamedParam method?

Basically, I'm trying to add pagination to an hql query I'm creating via HibernateTemplate's findByNamedParam method.

Ok after a lot of research, I finally got what I wanted.

First, need to create a HibernateCallback implementation:

HibernateCallbackImpl.java:

import java.sql.SQLException;
import java.util.List;

import org.apache.poi.hssf.record.formula.functions.T;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class HibernateCallbackImpl 
    implements HibernateCallback<List<T>> {

    private String queryString;
    private String[] paramNames;
    private Object[] values;

    private int firstResult;
    private int maxResults;

    /**
     * Fetches a {@link List} of entities from the database using pagination.
     * Execute HQL query, binding a number of values to ":" named parameters in the query string.
     * 
     * @param queryString a query expressed in Hibernate's query language
     * @param paramNames the names of the parameters
     * @param values the values of the parameters 
     * @param firstResult a row number, numbered from 0
     * @param maxResults the maximum number of rows
     */
    public HibernateCallbackImpl(
            String queryString, 
            String[] paramNames, 
            Object[] values,
            int firstResult,
            int maxResults) {
        this.queryString = queryString;
        this.paramNames = paramNames;
        this.values = values;

        this.firstResult = firstResult;
        this.maxResults = maxResults;
    }

    @Override
    public List<T> doInHibernate(Session session) throws HibernateException,
            SQLException {
        Query query = session.createQuery(queryString);
        query.setFirstResult(firstResult);
        query.setMaxResults(maxResults);

        // TODO: throw proper exception when paramNames.length != values.length

        for (int c=0; c<paramNames.length; c++) {
            query.setParameter(paramNames[c], values[c]);
        }

        @SuppressWarnings("unchecked")
        List<T> result = query.list();

        return result;
    }

}

Then, I can just instantiate the new object and it will return what I want:

Example:

@SuppressWarnings("unchecked")
List<TitleProductAccountApproval> tpaas = 
    getHibernateTemplate().executeFind(
        new HibernateCallbackImpl(
            hql.toString(), 
            paramNames.toArray(new String[paramNames.size()]), 
            values.toArray(),
            firstResult,
            maxResult
        )
    );

The solution by @Corey works great but it includes a problem inside the for-loop where query.setParameter(...) is called.

The problem is that it doesn't account for parameters which are either a collection or an array and this will result in weired ClassCastExceptions because Hibernate tries to determine the ID by calling getId() on the collection or array (which is wrong). This happens e.g. if you are using an IN-clause (e.g. ...WHERE department IN (:departments) ...) where 'departments' is an array or collection of Department entities.

This is because collections or arrays need to use 'query.setParameterList(paramName, (Object[]) value)' or 'query.setParameterList(paramName, (Collection) value)'

Long story short:

I modified the version by @Corey by adding an 'applyNamedParameterToQuery()' method which I borrowed from org.springframework.orm.hibernate3.HibernateTemplate.applyNamedParameterToQuery(Query, String, Object):

import java.sql.SQLException;
import java.util.List;

import org.apache.poi.hssf.record.formula.functions.T;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class HibernateCallbackImpl 
    implements HibernateCallback<List<T>> {

    private String queryString;
    private String[] paramNames;
    private Object[] values;

    private int firstResult;
    private int maxResults;

    /**
     * Fetches a {@link List} of entities from the database using pagination.
     * Execute HQL query, binding a number of values to ":" named parameters in the query string.
     * 
     * @param queryString a query expressed in Hibernate's query language
     * @param paramNames the names of the parameters
     * @param values the values of the parameters 
     * @param firstResult a row number, numbered from 0
     * @param maxResults the maximum number of rows
     */
    public HibernateCallbackImpl(
            String queryString, 
            String[] paramNames, 
            Object[] values,
            int firstResult,
            int maxResults) {
        this.queryString = queryString;
        this.paramNames = paramNames;
        this.values = values;

        this.firstResult = firstResult;
        this.maxResults = maxResults;
    }

    @Override
    public List<T> doInHibernate(Session session) throws HibernateException,
            SQLException {
        Query query = session.createQuery(queryString);
        query.setFirstResult(firstResult);
        query.setMaxResults(maxResults);

        // TODO: throw proper exception when paramNames.length != values.length

        for (int c=0; c<paramNames.length; c++) {
            applyNamedParameterToQuery(query, paramNames[c], values[c]);
        }

        @SuppressWarnings("unchecked")
        List<T> result = query.list();

        return result;
    }


     /**
     * Code borrowed from org.springframework.orm.hibernate3.HibernateTemplate.applyNamedParameterToQuery(Query, String, Object)
     * 
     * Apply the given name parameter to the given Query object.
     * @param queryObject the Query object
     * @param paramName the name of the parameter
     * @param value the value of the parameter
     * @throws HibernateException if thrown by the Query object
     */
    protected void applyNamedParameterToQuery(Query queryObject, String paramName, Object value)
            throws HibernateException {

        if (value instanceof Collection) {
            queryObject.setParameterList(paramName, (Collection) value);
        }
        else if (value instanceof Object[]) {
            queryObject.setParameterList(paramName, (Object[]) value);
        }
        else {
            queryObject.setParameter(paramName, value);
        }
    }

}