*
The moose likes Object Relational Mapping and the fly likes Hibernate sorting issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate sorting issue" Watch "Hibernate sorting issue" New topic
Author

Hibernate sorting issue

Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
my issue. My database column is of type char. However it houses all integers (store numbers).

when i do my crit.addOrder(Order.asc("storeNumber")); the order is really not in order.

it will come back as 0 1 10 11 12 13 14 15 16 17 18 19 2 20 etc....

is there a way to cast, convert, or format this to an integer before the sort?

Lalit Mehra
Ranch Hand

Joined: Jun 08, 2010
Posts: 384

yes you can use the Integer wrapper class to cast it back to an integer ...

try this ... java.lang.Integer

From the API:

The Integer class wraps a value of the primitive type int in an object. An object of type Integer contains a single field whose type is int.

In addition, this class provides several methods for converting an int to a String and a String to an int, as well as other constants and methods useful when dealing with an int.
[edit]
Have added quote tags, because most of this post was a direct quote. Please always cite the source for every quote.
CR
[/edit]


http://plainoldjavaobject.blogspot.in
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 37970
    
  22
Welcome to the Ranch

That is ascending order, just not numerical order. It looks like what is jocularly called asciibetical (pronounced aski----). It looks as if your numbers were being turned into Strings somewhere. Or maybe you are starting off with Strings.Does storeNumber represent the name of the column? Are you getting those Strings from XML or similar? You are doubtless familiar with the usual techniques for parsing Strings to ints and similar, but you can’t cast a String to an int.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2272
    
  28

In your Order by clause, you will need to put a call to a database function that converts the string to Integer. You can create your own Order class like this http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

Conversion functions are usually database specific. So, you might end up putting database specific code in here. I am not sure how to make a database agnostic custom Order class here. Might be possible but you will need to understand the API very well.

However, the query that it will generate will perform horribly. You are better of changing the datatype of the database column if possible. If it stores Integers make it Integer.
Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
Jayesh A Lalwani wrote:In your Order by clause, you will need to put a call to a database function that converts the string to Integer. You can create your own Order class like this http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

Conversion functions are usually database specific. So, you might end up putting database specific code in here. I am not sure how to make a database agnostic custom Order class here. Might be possible but you will need to understand the API very well.

However, the query that it will generate will perform horribly. You are better of changing the datatype of the database column if possible. If it stores Integers make it Integer.



It is company policy to not make a number an Integer unless it is a number that will change. Store numbers will not change, and therefore are defined as Strings.
Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
Lalit Mehra wrote:yes you can use the Integer wrapper class to cast it back to an integer ...

try this ... java.lang.Integer

From the API:

The Integer class wraps a value of the primitive type int in an object. An object of type Integer contains a single field whose type is int.

In addition, this class provides several methods for converting an int to a String and a String to an int, as well as other constants and methods useful when dealing with an int.
[edit]
Have added quote tags, because most of this post was a direct quote. Please always cite the source for every quote.
CR
[/edit]


I was hoping to not make the conversion in the action Bean unless there was no other way, doing it in the DAO makes sense and then it only needs to be sorted the one time. Obviously, if I have no other choice, I will see what I can do about doing it in the action bean.
Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
Campbell Ritchie wrote:Welcome to the Ranch

That is ascending order, just not numerical order. It looks like what is jocularly called asciibetical (pronounced aski----). It looks as if your numbers were being turned into Strings somewhere. Or maybe you are starting off with Strings.Does storeNumber represent the name of the column? Are you getting those Strings from XML or similar? You are doubtless familiar with the usual techniques for parsing Strings to ints and similar, but you can’t cast a String to an int.


It is a String on our SQL database. I supposed I could do this in my action bean, but since this is input to a form, I didn't want it to execute a 1000 times a day, I only wanted it to execute the one time in the DAO and not need to execute again until a new store is added.
Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
nevermind on this. I ended up writing a class that implements Comparator and then sorting the list using Collections.sort(List, new Comparator())

Thanks for your help though.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2272
    
  28

Whoa! You are sorting the result set in Java?! Unless you are working with very small data sets, it sounds like you are asking for trouble
Rick Prows
Greenhorn

Joined: Feb 16, 2012
Posts: 10
Jayesh A Lalwani wrote:Whoa! You are sorting the result set in Java?! Unless you are working with very small data sets, it sounds like you are asking for trouble


the result set isn't too large this time. But if you know a way to do in the DAO, I would much rather do there. I wasn't finding anything in searches that worked and I needed to move forward. I'd like my solution to be a stop-gap solution until a better one is found. But I do not know where else to look for a better option.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2272
    
  28

I already posted a solution for you. You can implement a custom Order class that tells the database to convert the column before ordering it. It's not an elegant solution but it;s better for large data sets. If your data set is guaranteed to be small, you might be better off doing sorting in Java.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate sorting issue
 
Similar Threads
Installing axis2 (1.5.1) in Websphere 7
Retereive data by ORDER BY
controlling jsp:forward
Session Management
distinctions between Stateless and Stateful bean