Hi,
I would like to use some named parameters in the order by clause of a named query. For example I would like to dynamically sort on a given column either ascending or descending. Hibernate does not complain at all when I try doing this, but the results are not sorted correctly when using the bind parameters.
Code:
<query name="my.package.path.Application.getAll">
from my.package.path.Application as app
order by :sortItem :sortDir
</query>
I am wondering if this is a limitation of my JDBC driver/database, as the debug SQL shows the bind variables being issued in the order by, but there seems to be no effect?
My current work around is to leave the order by off in my named query and then add it on manually at runtime i.e.
Code:
<query name="my.package.path.Application.getAll">
from my.package.path.Application as app
</query>
Query namedQuery = this.getHibernateTemplate().getNamedQuery(
this.getSession(), ALL_APPS_QUERY);
StringBuffer orderedQuery = new StringBuffer( namedQuery.getQueryString());
orderedQuery.append(" order by ");
if (chunk.getSortItem() != null) {
orderedQuery.append("app.");
orderedQuery.append(chunk.getSortItem());
orderedQuery.append(' ');
orderedQuery.append((chunk.getSortDirection() ==
PageableChunk.SORT_DIR_ASC) ? "asc" : "desc");
} else {
orderedQuery.append("app.");
orderedQuery.append("submitDate desc");
}//else
Query query = this.getHibernateTemplate().createQuery(
this.getSession(), orderedQuery.toString());
query.setFirstResult(chunk.getOffset());
query.setMaxResults(chunk.getMaxSize());
Iterator applications = query.iterate();
This work around yields the correct results but is very ugly and basically removes the purpose of using a named query. So I am wondering if I am doing something wrong (from what I read hibernate does support bind variables in the order by clause)? I am not familiar with the hibernate source code, but if this is a driver issue can hibernate do the variable substitution before passing this on to jdbc to eliminate the problem?
I was considereing using the Criteria API instead but from looking at the API it appears that it always fetches the entire set (please correct me if I am wrong, I am just assuming this because there is no iterate() method) and that will be too large to pull everytime.
Thanks,
Ryan Rich
----------------------------------------------
Hibernate version: 2.1.6
Mapping documents:Code:
<query name="my.package.path.Application.getAll">
from my.package.path.Application as app
order by :sortItem :sortDir
</query>
Code between sessionFactory.openSession() and session.close():Code:
Query query =this.getHibernateTemplate().getNamedQuery( this.getSession(), ALL_APPS_QUERY);
query.setParameter("sortItem", "app.submitDate");
query.setParameter("sortDir", "desc");
query.setFirstResult(chunk.getOffset());
query.setMaxResults(chunk.getMaxSize());
Iterator applications = query.iterate();
Full stack trace of any exception that occurs: No Exceptions
Name and version of the database you are using: MySQL 4.0.18
Debug level Hibernate log excerpt: Code:
DEBUG net.sf.hibernate.SQL - select applicatio0_.id as x0_0_ from application applicatio0_ order by ? ? limit ?, ?
Login
FAQ
Search