Hibernate Bind Variable in OrderBy Clause
posted by Jake on
I have spent mucho time trying to get some named parameters working in the "order by" clause of a dynamic sql query that I'm building. The query isn't even HQL/JPQL. It's native. And yet, it turns out that you cannot use bind variables, named or ordered, in an order by clause.
I was trying to do something like this:
public class Service { @PersistenceContext private EntityManager em; public List<object> search(String sortProperty) { Query q = em.createNativeQuery("select col from table order by :sortProperty"); q.setParameter("sortProperty", sortProperty); return q.getResultList(); } } </object>
This will yield something awesome, like this:
java.sql.SQLException: ORA-01745: invalid host/bind variable name
Seriously, the only way I've found around this is append, similar to this:
/* ... */ Query q = em.createNativeQuery("select col from table order by " + sortProperty); /* ... */
Just make sure you've got something scrubbing the data coming in.
Any better suggestions?