Order By

I have currently the below criteria :-

Criteria addr = criteria.createCriteria("location.address"); 
                addr.add((Restrictions.and(Restrictions.between("latitude", latmin,      
                       latmax), Restrictions.between("longitude", truelongmin, truelongmax)))); 

                String sql =  "SQRT( POW( 69.1 * ( {alias}.latitude - " + point[1]      
                       +" ) , 2 ) + POW( 69.1 * ( "+point[0] +" - {alias}.longitude ) * COS( {alias}.latitude /" 
                       +" 57.3 ) , 2 ) )  < "+distance;     
                addr.add(Restrictions.sqlRestriction(sql)); 

What I would like to do ideally is to be able to order by the distance. The mySQL equivalent being :-

SELECT * , (
SQRT( POW( 69.1 * ( latitude - 51.3814282 ) , 2 ) + POW( 69.1 * ( - 2.3574537 - longitude ) * COS( latitude / 57.3 ) , 2 ) )
) AS distance
FROM  `address` 
WHERE (
SQRT( POW( 69.1 * ( latitude - 51.3814282 ) , 2 ) + POW( 69.1 * ( - 2.3574537 - longitude ) * COS( latitude / 57.3 ) , 2 ) )
) < 10.0
ORDER BY distance DESC 
LIMIT 0 , 30

What is the best way to do this in Hibernate? I have tried createCriteria / createAlias ("distance","function")

Ideally I would like to do this post the restriction on Latmin and Latmax as that lowers the result set and thus the number of calculations (also the sql does the same calculation twice :S) however any suggestions would be most appreciated.

Cheers, Rob

---

Hibernate supports native queries just for situations like this :)

EDIT**

I haven't tested it, but something like the following should work:

StringBuilder sql = new StringBuilder();

sql.append("SELECT *");
sql.append(", ( SQRT( POW( 69.1 * ( latitude - 51.3814282 ) , 2 ) + POW( 69.1 * ( - 2.3574537 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) ) AS distance");
sql.append(" FROM  `address`");
sql.append(" WHERE ( SQRT( POW( 69.1 * ( latitude - 51.3814282 ) , 2 ) + POW( 69.1 * ( - 2.3574537 - longitude ) * COS( latitude / 57.3 ) , 2 ) ) ) < 10.0");
sql.append(" ORDER BY distance DESC");  
sql.append(" LIMIT 0 , 30");

SQLQuery query = session.createSQLQuery(sql.toString());
// call query.addScalar(..) for other fields you select
query.addScalar("distance", Hibernate.BIG_DECIMAL)

//Where AddressBean is the object that maps to a row of this resultset
query.setResultTransformer(Transformers.aliasToBean(AddressBean.class));

 List<AddressBean> list = new ArrayList<AddressBean>(100);
for (Object object : query.list()) {
    list.add((AddressBean)object);
}

链接地址: http://www.djcxy.com/p/36978.html

上一篇: JPA的hashCode（）/ equals（）两难

下一篇: 排序依据