Hibernate Bind Variable in OrderBy Clause

| Comments

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:

1
2
3
4
5
6
7
8
9
10
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();
   }
}

This will yield something awesome, like this:

1
java.sql.SQLException: ORA-01745: invalid host/bind variable name

Seriously, the only way I’ve found around this is append, similar to this:

1
2
3
/* ... */
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?

Comments