Hibernate 限制/标准初学者问题
嗨,我今天一直在尝试找出最好的方法,但没有成功。
理想情况下,我想做的是创建一个通过下面的 SQL 公式计算的别名距离(尽管我对计算距离的其他方法持开放态度,但这似乎是最简单的方法)
一旦我有了该别名,我希望能够以限制方式使用它来查找特定距离内的所有内容。
我还希望能够通过距离进行排序。
这是建立的更大搜索标准的一部分,因此我理想地希望继续使用 Criteria。 (我已经限制了 Lat 和 Long 值的范围,以便在更少的字段上进行距离计算。
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 在我正在查看的一个实例中起作用,但我害怕考虑长期影响,所以不希望它留在那里!!)
Hi I have been trying to work out the best way to do this today to no avail.
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.
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));
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!!)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
在 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 atlocation.address
: