休眠限制/条件初学者问题 [英] Hibernate Restrictions / Criteria Beginner Question
问题描述
我一直在尝试找出最好的方法来完成此任务,但无济于事.
Hi I have been trying to work out the best way to do this today to no avail.
我理想地要做的是创建一个由下面的SQL公式计算出的别名距离(尽管我对其他计算距离的方法持开放态度,但这似乎是最简单的方法)
What I would ideally like to do is to create an alias distance calculated by the SQL formula below (although I am open to other ways of calculating the distance, this was just the way that seemed it should be easiest)
一旦有了该别名,我便希望能够以一种限制"方式使用它来查找在一定距离内的所有别名.
Once I have that alias I want to be able to use it in a Restrictions fashion to find all that are within a certain distance.
我还希望能够按距离排序.
I would also like to be able to sort via distance.
这是已建立的较大搜索条件的一部分,因此,理想情况下,我希望继续使用Criteria.(我已经限制了纬度和经度值的范围,以便在较少的字段上进行距离计算.
This is part of a bigger search criteria that is built up so I would ideally like to keep using Criteria. ( I already limit the range of Lat and Long values to make the distance calculation required on less fields.
Criteria criteria = session.createCriteria(Activity.class)
.createAlias("activityLocations", "actloc")
.createAlias("actloc.location", "location")
.createAlias("location.address", "addr");
criteria.add((Restrictions.and(Restrictions.between("addr.latitude", latmin,
latmax),Restrictions.between("addr.longitude", truelongmin, truelongmax))));
String sql = "SQRT( POW( 69.1 * ( addr3_.latitude - " + point[1]
+" ) , 2 ) + POW( 69.1 * ( "+point[0] +" - addr3_.longitude ) * COS( addr3_.latitude /"
+" 57.3 ) , 2 ) ) < "+distance;
criteria.add(Restrictions.sqlRestriction(sql));
当前我在sql查询中有addr3_,因为我正在查看详细的输出,这就是Hibernate生成查询的方式(此hack在我正在查看的一个实例中有效,但我害怕考虑更长的时间术语的含义,所以不想让它留在那里!)
Currently I have addr3_ in the sql query because I was looking at the verbose output and that is the way that Hibernate has generated the query (this hack worked in the one instance i was looking at but I dread to think about the longer term implications so would not want it to stay there!!)
推荐答案
在SQL限制中,您可以将条件根的别名称为 {alias}
.要在这种情况下使用它,您需要一个以 location.address
:
In SQL restrictions you can refer to the alias of the criteria root as {alias}
. To use it in this case, you need a "sub criteria" rooted at location.address
:
Criteria criteria = session.createCriteria(Activity.class)
.createAlias("activityLocations", "actloc")
.createAlias("actloc.location", "location");
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));
这篇关于休眠限制/条件初学者问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!