wood burning stoves 2.0*
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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark " Watch " New topic
Author

'order by' clause in named query

Bridget Kennedy
Ranch Hand

Joined: Nov 30, 2004
Posts: 84
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">
<![CDATA[
from Message as msg
where msg.conversation = :conversationId
order by :sortField
]]>
</query>
...
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: 84
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:

<property
column="offset_time"
length="20"
name="offsetTime"
not-null="true"
type="java.lang.Long"
/>

doesn't work. I tried seting the order by criteria to 'offset_time' and 'offsetTime'. The results are the same, and un-ordered.
 
 
subject: 'order by' clause in named query