Hibernate - Injecting MySql Functions

Question

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

Tim Visher · Accepted Answer · 2010-11-18 17:13:53Z

1

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.

answered Nov 18, 2010 at 17:13

Tim Visher

12.9k16 gold badges61 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rob Over a year ago

Cheers Tim, it is not exactly what I was looking for but it does do the job. I am slightly concerned about performance as the dataset grows but for now it will do just fine to sort in memory :)

Collectives™ on Stack Overflow

Hibernate - Injecting MySql Functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related