• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate sorting issue

 
Rick Prows
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 384
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 49472
64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic