Hibernate: Ordering with SQL function

If you need to do ordering by an SQL function, eg:

ORDER BY YOUR_SQL_FUNCTION(your_table.your_column) ASC

This is what you need to do so that you can use Hibernate for this kind of ordering:

1. In your Hibernate mapping xml, add the following:

<class
name=”…”
table=”…”
>

<subselect>
<![CDATA[SELECT your_table.*, YOUR_SQL_FUNCTION(your_table.your_column) AS temp_column_name FROM YOUR_TABLE your_table]]>
</subselect>

<property
name=”javaFieldName”
type=”java.lang.String”
column=”temp_column_name”
update=”false”
insert=”false”
>
</property>

</class>

2. In your persistent Java class, add getter and setter for javaFieldName.

private String javaFieldName;

public String getJavaFieldName() {
return javaFieldName;
}

public void setJavaFieldName(String javaFieldName) {
this.javaFieldName = javaFieldName;
}

3. In your DAO class:

criteria.addOrder(Order.asc(”javaFieldName”));

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>