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);

2 comments:

  1. While parameters seems to be a convenience in reality they are an optimization artifact.

    I have seen them first implemented in Oracle. For some reason when Oracle is looking for a plan in a query plan cache it just does a literal comparison of the query strings. It means that if you change a string or a number literal in your query Oracle will not be able to find a prepared plan for the query and will build a new one. Parameters allows one to use the same plan for queries that are different in literals only.

    While parameters also provide additional security - you can pass "unsafe" values directly as argument and do not worry about creating a hole, there are other ways to pass arbitrary data into a DB. Up to 8.0 PostgreSQL solved this issue by providing a general purpose escaping function. IIRC, MySQL also provided similar function some time ago.

    This is the history that explains what parameters were created for. They used to replace literal constants in queries. As "order by" expects attribute names parameters does not work there.

    Adding an "order by" clause may also change the plan generated for the query.

    Now the practical part :)

    You can probably do something like this:

    Session s = HibernateSupport.currentSession();
    try {
    Query q = s.createQuery(
    s.getNamedQuery("query.without.order.by")
    .getQueryString()
    + " order by " + sortByColumnName
    );

    ...

    }
    catch ...

    Unless your sortByColumnName comes from an untrusted source. In this case you have to do proper validation first.

    ReplyDelete
  2. Certainly a solution to the problem!
    It has one light defect though (comparing to solution of simply externalizing query strings to some app-specific config or constants): query initialization code is called twice in Hibernate.
    But probably this defect is negligible.
    Thank you!

    ReplyDelete