-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 
Author Message
 Post subject: How to combine NamedQuery and DetachedCriteria ?
PostPosted: Tue Jan 13, 2009 4:58 am 
Beginner
Beginner

Joined: Wed Aug 27, 2003 3:54 am
Posts: 28
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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 5:28 am 
Expert
Expert

Joined: Thu Jan 08, 2009 6:16 am
Posts: 661
Location: Germany
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")


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 6:02 am 
Beginner
Beginner

Joined: Wed Aug 27, 2003 3:54 am
Posts: 28
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 6:22 am 
Expert
Expert

Joined: Thu Jan 08, 2009 6:16 am
Posts: 661
Location: Germany
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 6:27 am 
Expert
Expert

Joined: Thu Jan 08, 2009 6:16 am
Posts: 661
Location: Germany
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.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 8:08 am 
Beginner
Beginner

Joined: Wed Aug 27, 2003 3:54 am
Posts: 28
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 ?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 12:20 pm 
Expert
Expert

Joined: Thu Jan 08, 2009 6:16 am
Posts: 661
Location: Germany
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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 13, 2009 4:37 pm 
Beginner
Beginner

Joined: Wed Aug 27, 2003 3:54 am
Posts: 28
It could be interesting to check why SQLServer2005 generates "... order by @P1 ',1" instead of "order by 1". Any volunteers :o)


Top
 Profile  
 
 Post subject: Can you post code?
PostPosted: Tue Jan 13, 2009 6:33 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 05, 2009 11:20 am 
Beginner
Beginner

Joined: Wed Apr 26, 2006 4:40 am
Posts: 24
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)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.