Hi,

I'm using a Named Query, but should add an "order by" clause to it. The orderBy column can be chosen by the user, so I should be able to add it programmatically.

In order to implement this, I was thinking on getting the Named Query and combining it with a DetachedCriteria. The criteria allows me to "dynamically" add the sortcolumn.

Is this possible and what is the impact on the "cachebility" of the query ?

Regards, Stefan Lecho.

Hibernate version:
3.2.5.ga

You could add a parameter to your query.

For example, your query is: "from MyEntity order by :orderColumn"

When using it: session.getNamedQuery("myQuery").setParameter("orderColumn", "someColumnName")

When using "order by :orderColumn", I'm getting the following error: "Incorrect syntax near '@P1'".

When using "order by :orderColumn :orderDirection", I'm getting the following error: "Incorrect syntax near '@P2'".

Based on this, I guess that parameters are only allowed in where clauses. Is this correct ?

Regards, Stefan Lecho.

It worked without exception in my Test.

Here is my query:
@NamedQuery(name="test", query="from Contact order by :param")

And my test:
session.getNamedQuery("test").setParameter("param", "someProperty").list()

This worked, but actually my database(MySQL) did not order, because hibernate put the column name into quotes. Maybe you can check if it works with you database.

My test workes fine, when I put the columnindex as parameter:
setParameter("param", 1)

But I guess, this is not working for you, because you do not know at which index a column is selected by hibernate.

When using "q.setParameter("orderColumn",1);", SQL Server 2005 executes "... order by @P1 ',1" and that's something he does not like o:(.

Could you verify which SQL statement is generated in your environment ?

It generated:
select contact0_.id as id0_, contact0_.customer_id as customer3_0_, contact0_.someProperty as someProp2_0_ from Contact contact0_ order by 1 desc

It could be interesting to check why SQLServer2005 generates "... order by @P1 ',1" instead of "order by 1". Any volunteers :o)

Hi Stefan,
can you post your code? and the generated Hibernate query?
Also have you tried taking the generated query and executing it directly on the SQL server 2005 database?

The reason why I'm asking you to execute it is because we do use SQL server 2005 and also we did use some hand written SQL(NOT HQL thou) with dynamic 'order by' clause and pass the query to hibernate to get results. So My guess is that it has to do with the way you are using HQL to build the query.

Hope this helps,
Srilatha.

The solution for this problem is pretty simple.

You build your query as follows

select c.col1, c.col2, c.col3
from Clasz c
order by
:sort1 * c.col1,
:sort2 * c.col2,
:sort3 * c.col3

if you have numeric values.

Setting sort1 to sort3 to 0 or 1 switches your ordering on/off

for alphanumeric values you need some more advanced sql functionality

e.g.
...
order by
if (:sort1 = 1, c.col1, 0),
if (:sort2 = 1, c.col2, 0),
if (:sort3 = 1, c.col3, 0)