ORDER BY and named parameters

skipper · **Joined:** Mon Nov 27, 2006 7:38 am **Posts:** 7

I try to use the ORDER BY clause in a named query, like this:

Code:

"SELECT a.firstname, a.lastname FROM Contact a WHERE 1=1 ORDER BY :orderBy"

Then I try to run the following code:

Code:

results = em.createNamedQuery("MyNamedQueryAbove").setParameter("orderBy", "a.firstname").getResultList();

This fails and in the trace log I get

Quote:

DEBUG 16-02 13:33:53,953 (Log4JLogger.java:debug:84) -select contact0_.firstname as col_0_0_, contact0_.lastname as col_1_0_ from Contact contact0_ where 1=1 order by ?
DEBUG 16-02 13:33:53,968 (Log4JLogger.java:debug:84) -bindNamedParameters() a.firstname -> orderBy [1]
DEBUG 16-02 13:33:53,968 (Log4JLogger.java:debug:84) -about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG 16-02 13:33:53,968 (Log4JLogger.java:debug:84) -aggressively releasing JDBC connection
DEBUG 16-02 13:33:53,968 (Log4JLogger.java:debug:84) -releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
DEBUG 16-02 13:33:53,968 (Log4JLogger.java:debug:89) -could not execute query [select contact0_.firstname as col_0_0_, contact0_.lastname as col_1_0_ from Contact contact0_ where 1=1 order by ?]
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610

How do I use named parameters with the ORDER BY clause?

What I really wanted to do was pagination, so if I change the above code to:

Code:

results = em.createNamedQuery("Contact.getAllInShortForm").setFirstResult(0).setMaxResults(10).setParameter("orderBy", "a.firstname").getResultList();

It still fails but with a different reason. Here again is the trace log:

Quote:

DEBUG 16-02 13:53:29,687 (Log4JLogger.java:debug:84) -select * from ( select rownumber() over(order by ?) as rownumber_, contact0_.firstname as col_0_0_, contact0_.lastname as col_1_0_ from Contact contact0_ where 1=1 order by ? ) as temp_ where rownumber_ <= ?
DEBUG 16-02 13:53:29,687 (Log4JLogger.java:debug:84) -bindNamedParameters() a.firstname -> orderBy [1]
DEBUG 16-02 13:53:29,718 (Log4JLogger.java:debug:84) -about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG 16-02 13:53:29,718 (Log4JLogger.java:debug:84) -aggressively releasing JDBC connection
DEBUG 16-02 13:53:29,718 (Log4JLogger.java:debug:84) -releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
DEBUG 16-02 13:53:29,718 (Log4JLogger.java:debug:89) -could not execute query [select contact0_.firstname as col_0_0_, contact0_.lastname as col_1_0_ from Contact contact0_ where 1=1 order by ?]
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001

Any help will be appreciated! Thanks!

Hibernate version:
Hibernate Core 3.2
Annotations 3.2
Entity Manager 3.2

Name and version of the database you are using:
DB2 v8.2.3 on Windows XP

emmanuel · **Posted:** Tue Feb 20, 2007 7:17 pm

Seems like your driver or your DB does not support such queries.
I'm not even sure it's valid SQL to be honest

skipper · **Joined:** Mon Nov 27, 2006 7:38 am **Posts:** 7

Yes, you are right. I read the manual... You can not use parameters with the ORDER BY clause.

I could not find an easy solution, but I solved my problem with a native query. Not nice. Another solution was creating the sql statement at run-time. Still with native sql as it would be too slow to use jpql.

Regards,
Skipper

emmanuel · **Posted:** Tue May 22, 2007 6:24 pm

skipper wrote:

Still with native sql as it would be too slow to use jpql.

I don't see why.

skipper · **Joined:** Mon Nov 27, 2006 7:38 am **Posts:** 7

If I build the jpql string at runtime, then I would get the jpql to sql translation overhead for every execution. If I build the sql string instead then there is no translation overhead.

emmanuel · **Posted:** Thu Jun 14, 2007 10:22 am

skipper wrote:

If I build the jpql string at runtime, then I would get the jpql to sql translation overhead for every execution. If I build the sql string instead then there is no translation overhead.

Of course not, Hibernate is not that stupid :)