Showing posts with label named queries. Show all posts
Showing posts with label named queries. Show all posts

Tuesday, August 11, 2009

Order by in Hibernate named queries

Another annoying thing I discovered today with Hibernate is about putting dynamic Order By clause to named queries. You can not normally Order By column name because if you put into your query something like ' order by :columnName' and do something like setParameter(":columnName", "id") it won't work as id value will be put quoted into prepared statement. Even more the following query 'select * from Order o order by :column :direction' will result in error! Hibernate won't be able to parse this query.
On one of the forums I found 'nice' workaround suggesting put column numbers instead of column names, this seems like very inconvenient solution, also anyway this won't solve problem with sort direction.
If anyone reading this blog (is anyone here?! :)) ) knows better alternative - I'll be glad to hear. Currently marking named queries as 'useless' feature in our project.
Update: as suggested in comments there is a solution (quite a simple one I should admit) which is to wrap query string retrieved named query into another query call like that:

Query q = s.createQuery(s.getNamedQuery("query.without.order.by").
getQueryString()+ " order by " + sortByColumnName);