I am using externalized named queries (named queries in my mapping document). After reading through the forums, is it safe to assume I can NOT use a named parameter for the ORDER BY clause?


..as setting the named parameter has no effect on the order of the results.

So, if this cannot be done... what is the best way to dynamically define the ORDER BY named parameter? I would like to use externalized named queries, if possible. Any suggestions? or do i have to revert back internalized named queries?

Thanks

Hibernate version: 3.1.3, 3.1.2CR2

Mapping documents:



Code between sessionFactory.openSession() and session.close():

Query q = sess.getNamedQuery("eg.DomesticCat.by.name.and.minimum.weight");
q.setString( "orderby", "cat.name" );
List cats = q.list();

Name and version of the database you are using:

MySQL 5.0.21

The generated SQL (show_sql=true):

.... ORDER BY ?

Hi,
i'm trying to achieve the same thing, i.e using a named parameter to dynamically create an order by clause. However i'm not using a named query but a standard HQL query built in the code.


The result does not take the ordering constraint into account.
I've turned on logging on the MySQL server and i see that the query is being run as the following:



I see that the sort column name is being included in quotes, which makes the order by ineffective. Quotes should not be added to the column name.
Has someone seen this behaviour ? I cannot find a way to get those quotes removed :(
I'm running Hibernate 3.2 with Hibernate Annotations and Hibernate EntityManager 3.2.0 CR2. My database is MYSQL 5.0.22.

Any help would be appreciated !
Thanks in advance.

I'm seeing the same issue with HB 3.1.2. If I use a dynamic sort with regular HQL, I see what you're seeing. If I hard code the sort value, it works fine. Looking into the Hibernate code and will get back later...

Lou

I think i figured it out. It's because Hibernate uses a PreparedStatement and the column name is a parameter to the PreparedStatement. This goes against the PreparedStatement spirit. I found out this thread http://forum.java.sun.com/thread.jspa?threadID=478053&messageID=2223214
That discusses the topic. I've changed my code so that the order by column name is not a Parameter to the HQL query anymore and everything is now fine.

I came to the same conclusion after switch my hard-coded value to a parametric value and building with a StringBuffer like this:



Best,
Lou

[EDIT] OOps double post :P

The problem is that applications become horrendously complicated without that feature.

A simple example is a table where the data can be sorted by each column, ascending and descending. There are 10 columns. This results in a whopping 20 queries. Than you can filter the table by 5 different criteria -> 100 queries.

You have either the option to code this as a criteria query, which is simply far to cumbersome for some queries, or build your HQL using String concatenation, which is not far better then creating plain SQL in your code.

Hi Charles,
Am facing the same problem, setting a dynamic order by clause for hibernate. I tried going to the link u have provided. but it does not seem to exist anymore. Can u please tell me how did u solve the issue as i cannot append order by string dynamically into the query as it would reflect as a SQL injection issue in the security scan.

If anyone else has a work around for this please do share your solution for this problem. Would appreciate it :)

If you are using JPA1 you can use below function or You can use unwrap method from JPA2


public static String unWrapNamedQuery(javax.persistence.Query query){

String sqlString="";
String queryObject=ToStringBuilder.reflectionToString(query, ToStringStyle.SIMPLE_STYLE);
String pattern = "(?i)(SELECT)(.+?)(ORDER)(\\s+)(BY)";
java.util.regex.Matcher matcher = Pattern.compile(pattern).matcher(queryObject);
if (matcher.find()) {
sqlString=matcher.group();
}

return sqlString;
}