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.

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.

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.

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...

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.

try "Named SQL queries" (Reference 17.3.) or
"Custom SQL for loading" (Reference 17.5.).

There you can put your PL/SQL .


Siegbert