Hibernate - Injecting MySql Functions

Is is possible using Hibernate (prefferably the Criteria API) to use a mySql function to Order By.

The function I am looking to use is :-

SQRT( POW( 69.1 * ( {alias}.latitude -  51.3814282 ) , 2 ) + POW( 69.1 * (   -2.3574537 - {alias}.longitude ) * COS( {alias}.latitude / 57.3 ) , 2 ) ) 

where the {alias} is rooted at

criteria.createCriteria("location.address"); 

All I am looking to do is have the results ordered by 开发者_JS百科the above distance equation, but thus far I havent found a way using the Projections options or the Order. options and am running out of ideas.

Cheers, R

This doesn't exactly answer your question but what you get out of the Criteria API is a bunch of nice Objects which adhere to their interfaces. Have you thought of lifting the ordering out of the query and instead sorting them in memory? It should be easy to express that ordering function in terms of a Comparator and then you can just sort the Collection.

I don't know how to express it in the .addOrder function of the Criteria API. You can add arbitrary SQL to Restrictions, but it looks like you can't order by arbitrary SQL.

Good luck.