File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes 'order by' clause in named query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark " Watch " New topic

'order by' clause in named query

Bridget Kennedy
Ranch Hand

Joined: Nov 30, 2004
Posts: 86
I'm having trouble attempting to include an 'order by' clause in a Hibernate named query. The named query definition in my hbm.xml looks like:
<query name="getMessages">
from Message as msg
where msg.conversation = :conversationId
order by :sortField
The java code that uses the query and sets the conversationId and sortField named parameters looks like:
Query query;
Session session;
List messages;

session = getSession(session);

try {
query = session.getNamedQuery( "getMessages" );
query.setInteger( "conversationId", 1 );
query.setString( "sortField", "someString" );
messages = query.list();

I get the list of messages for the given conversationId. So the 'where' clause and its associated named parameter substitution seems to work ok.

The results are not, however, sorted by the column specified by someString. I've tried using the hibernate property 'name' and 'column' values for the someString value. The results are the same: the list is returned in order by record number. The order by clause has no effect.

Has anyone else out there used an 'order by' clause in a named query? Is this a misuse of a named parameter?
miguel lisboa
Ranch Hand

Joined: Feb 08, 2004
Posts: 1281
just a guess:

try order by 'xpto' asc/desc

java amateur
Bridget Kennedy
Ranch Hand

Joined: Nov 30, 2004
Posts: 86
Originally posted by miguel lisboa:
just a guess:

try order by 'xpto' asc/desc

I tried this. It didn't make any difference.

I do have another clue. It seems that the sort works if the property specified in the order by clause has identical property name and column values. If the property has differing name and column values, it doesn't sort, e.g. a data column property defined like so:


doesn't work. I tried seting the order by criteria to 'offset_time' and 'offsetTime'. The results are the same, and un-ordered.
I agree. Here's the link:
subject: 'order by' clause in named query
jQuery in Action, 3rd edition