Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: Sorting varchar numbers using criteria
PostPosted: Wed Oct 26, 2005 4:18 pm 
Newbie

Joined: Wed Oct 26, 2005 2:49 pm
Posts: 5
Hibernate version:3.0.5

Name and version of the database you are using:Oracle 9i

Here is my situation, I have a varchar column which for the most part are numbers but could have text. I need to be able to sort this column and currently I am using Criteria.addOrder(...), however, of course, the numbers are being sorted as strings and are not in a logic sort order for the user. What I want to happen is the numbers to be sorted as numerics and the text to sort after the numbers have been sorted.
Example (should sort):
"20"
"1025"
"1222"
"a789"
"b908"

Is there any way in hibernate to efficiently make this possible (using Criterias)? I know I can make a new column in the table and such but I would like to avoid doing that. Thank you.


Top
 Profile  
 
 Post subject: Re: Sorting varchar numbers using criteria
PostPosted: Wed Oct 26, 2005 4:22 pm 
Expert
Expert

Joined: Mon Feb 14, 2005 12:32 pm
Posts: 609
Location: Atlanta, GA - USA
Version4 wrote:
Hibernate version:3.0.5

Name and version of the database you are using:Oracle 9i

Here is my situation, I have a varchar column which for the most part are numbers but could have text. I need to be able to sort this column and currently I am using Criteria.addOrder(...), however, of course, the numbers are being sorted as strings and are not in a logic sort order for the user. What I want to happen is the numbers to be sorted as numerics and the text to sort after the numbers have been sorted.
Example (should sort):
"20"
"1025"
"1222"
"a789"
"b908"

Is there any way in hibernate to efficiently make this possible (using Criterias)? I know I can make a new column in the table and such but I would like to avoid doing that. Thank you.


Criteria.addOrder(...) merely adds an ORDER BY statement to the SQL retrieving the data from the database. For what you're talking about, I think you would need to create a Sorted Collection object and implement a custom Comparator to sort them.

_________________
Preston

Please don't forget to give credit if/when you get helpful information.


Top
 Profile  
 
 Post subject: Re: Sorting varchar numbers using criteria
PostPosted: Wed Oct 26, 2005 4:38 pm 
Newbie

Joined: Wed Oct 26, 2005 2:49 pm
Posts: 5
pksiv wrote:
Criteria.addOrder(...) merely adds an ORDER BY statement to the SQL retrieving the data from the database. For what you're talking about, I think you would need to create a Sorted Collection object and implement a custom Comparator to sort them.


Thank you, but I am not quite sure how to implement a sorted collection using Hibernate. Are you refering to the 'sort' property of a mapping and passing it a comparator? If so, is the comparator the same as a standard comparator in Java?


Top
 Profile  
 
 Post subject: Re: Sorting varchar numbers using criteria
PostPosted: Wed Oct 26, 2005 4:41 pm 
Expert
Expert

Joined: Mon Feb 14, 2005 12:32 pm
Posts: 609
Location: Atlanta, GA - USA
Version4 wrote:
pksiv wrote:
Criteria.addOrder(...) merely adds an ORDER BY statement to the SQL retrieving the data from the database. For what you're talking about, I think you would need to create a Sorted Collection object and implement a custom Comparator to sort them.


Thank you, but I am not quite sure how to implement a sorted collection using Hibernate. Are you refering to the 'sort' property of a mapping and passing it a comparator? If so, is the comparator the same as a standard comparator in Java?


The <set>, <map> etc... have a sort="" attribute which allows you to specify a custom Java Comparator class. See Section 7.3.1 in the reference docs.

_________________
Preston

Please don't forget to give credit if/when you get helpful information.


Top
 Profile  
 
 Post subject: Re: Sorting varchar numbers using criteria
PostPosted: Wed Oct 26, 2005 5:10 pm 
Newbie

Joined: Wed Oct 26, 2005 2:49 pm
Posts: 5
pksiv wrote:
Version4 wrote:
pksiv wrote:
Criteria.addOrder(...) merely adds an ORDER BY statement to the SQL retrieving the data from the database. For what you're talking about, I think you would need to create a Sorted Collection object and implement a custom Comparator to sort them.


Thank you, but I am not quite sure how to implement a sorted collection using Hibernate. Are you refering to the 'sort' property of a mapping and passing it a comparator? If so, is the comparator the same as a standard comparator in Java?


The <set>, <map> etc... have a sort="" attribute which allows you to specify a custom Java Comparator class. See Section 7.3.1 in the reference docs.


Thanks again, I looked into using a map or set but I don't think I can do this since I am gathering all my data directly from a View. My mapping file looks simliar to the following.

<class name="srViewDb" table="VIEW_SR">
<id name="id" type="java.lang.Long" column="id">
<generator class="assigned" />
</id>

<property name="IMId" column="IMID" type="java.lang.Long"/>
<property name="IMName" sort="" column="IMNAME" type="java.lang.String"/>
<property name="IMAccountNum" column="IMACCOUNTNUM" type="java.lang.String"/>
.......
</class>

The IMAccountNum is having the issue. Any other suggestions would be very helpful or a correction to the above mapping.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 26, 2005 5:28 pm 
Beginner
Beginner

Joined: Tue Apr 05, 2005 4:27 pm
Posts: 40
Location: canada
do the srViewDb objects belong to a collection in another class, or are you just performing a select * from the view?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 26, 2005 5:32 pm 
Newbie

Joined: Wed Oct 26, 2005 2:49 pm
Posts: 5
jaime wrote:
do the srViewDb objects belong to a collection in another class, or are you just performing a select * from the view?


It's basically just a select * from view. Each column from the view is going directly to a column in the gui.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 26, 2005 5:35 pm 
Beginner
Beginner

Joined: Tue Apr 05, 2005 4:27 pm
Posts: 40
Location: canada
you could just stick the list of those objects, or whatever you get back from hibernate into your own collection with a custom comparator... i'm not sure this kind of comparison can be done from the database...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 27, 2005 12:32 am 
Newbie

Joined: Wed Oct 26, 2005 2:49 pm
Posts: 5
Ok, so I found a solution in PL/SQL:

select * from table
order by lpad(columnName, 25);

(where columnName is varchar2(25))

Now, all I need is some help in trying to translate the above order by clause into Hibernate using Criterias, I'm just not sure how to do it. Any help is greatly appreciated.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 27, 2005 2:58 am 
Newbie

Joined: Mon Jan 05, 2004 11:04 am
Posts: 16
try "Named SQL queries" (Reference 17.3.) or
"Custom SQL for loading" (Reference 17.5.).

There you can put your PL/SQL .


Siegbert


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 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.