Hibernate sorting issue

[Rick Prows]

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]

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]

[Campbell Ritchie]

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]

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]

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]

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]

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]

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]

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]

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]

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.