I'm trying to implement pagination in several of my DAO classes. Up to somewhat recently simply fetching *ALL* results has been sufficient, but we're starting to see performance issues and are looking to implement pagination. The problem is that when we fetched *ALL* results, we could then perform very complex sorting. Now that we're only going to be receiving a portion of the overall results, performing sorting in this way is no longer an option - it *has* to be done as part of the query.

From my research one interesting option I saw was to create mappings for formula-calculated properties in my domain objects. For example, I could do something like:

...
<property name="complexProperty" type="string">
<property name="complexPropertySortValue" type="integer">
<formula>
case
when complexProperty is null then 0
when complexProperty is ... then 1
when complexProperty is ... then 2
else 0
end
</formula>
</property>
...

and then the order by clause in the appropriate query is pretty simple. In our case that will help in many, but not all of our situations. What other options do I have to perform complex sorting?