代码之家 › 专栏 › 技术社区 › Kevin Pang

带距离计算存储过程的nhibernate查询

nhibernate sql

Kevin Pang · 技术社区 · 17 年前

我的数据库中有一个存储过程,它计算两个lat/长对之间的距离。此存储过程称为“DistanceBetween”。我有一个SQL语句,它允许用户搜索items表中按照与所提供的lat/long坐标的距离排序的所有项。SQL语句如下:

SELECT Items.*, dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude) AS Distance
FROM Items
ORDER BY Distance

如何在nhibernate中使用此查询?“我的域”中的项目类没有“距离”属性,因为“我的项目”表中没有“距离”列。只有当用户执行此搜索时,“距离”属性才真正起作用。

3 回复 | 直到 16 年前

Abel 16 年前

基本上可以使用三种方法,其中一些已经讨论过:

使用HQL查询或 CreateCriteria / ICriteria 查询; 缺点: 它不是实体/DAL的一部分; 有利因素: 它是灵活的;
将属性映射与 formula ; 缺点: 这并不总是可行或可能的,如果不小心性能会下降; 有利因素: 它是你的计算实体不可分割的一部分;
创建单独的XML HBM映射文件并映射到单独的(要创建的)实体; 缺点: 它不是基础实体的一部分; 有利因素: 只有在需要时才调用SP,完全控制映射/额外属性/扩展,可以使用部分或抽象类与现有实体组合。

我将在这里简要地展示选项2和3的一个例子,我相信在本文的前面,选项1已经被其他人充分地覆盖了。

选项2(如本例中所示)在可以将查询创建为select语句的子查询,以及在映射表中所有需要的参数都可用时特别有用。如果表不是可变的和/或缓存为只读的,这也会有所帮助,具体取决于存储过程有多重。

<class name="..." table="..." lazy="true" mutable="false>
  <cache usage="read-only" />

    <id name="Id" column="id" type="int">
      <generator class="native" />
    </id>
    <property name="Latitude" column="Latitude" type="double" not-null="true" />
    <property name="Longitude" column="Longitude" type="double" not-null="true" />

    <property name="PrijsInstelling"
        formula="(dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude))"
        type="double" />

    ... etc
</class>

如果由于映射中的限制、缓存问题或当前缓存设置逐个而不是按更大的数量检索而无法实现上述目标,则应考虑使用其他方法,例如使用参数对整个查询进行单独映射。这与上面的createSqlQuery方法非常接近,但强制结果集为特定类型(并且可以声明性地设置每个属性):

<sql-query flush-mode="never" name="select_Distances">
    <return
        class="ResultSetEntityClassHere,Your.Namespace"
        alias="items"
        lock-mode="read" >

        <return-property name="Id" column="items_Id" />
        <return-property name="Latitude" column="items_Latitude" />
        <return-property name="Longitude" column="items_Longitude" />
        <return-property name="Distance" column="items_Distance" />
    </return>

    SELECT 
        Items.*, 
        dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude) AS Distance
    FROM Items
    WHERE UserId = :userId

</sql-query>

您可以按如下方式调用此查询:

List<ResultSetEntityClassHere> distanceList = 
    yourNHibernateSession.GetNamedQuery("select_Distances") 
        .SetInt32("userId", currentUserId)  /* any params go this way */
        .SetCacheable(true)                 /* it's usually good to cache */
        .List<ResultSetEntityClassHere>();  /* must match the class of sql-query HBM */

根据您的需要,您可以选择一种方法。我个人使用以下经验法则来决定使用哪种方法:

计算很轻还是可以缓存? 使用 公式 途径;
是否需要将参数发送到SP/SQL? 使用 sql-query +绘图方法;
查询(非常)的结构是变量吗? 使用 伊克利特里亚 或者通过代码实现HQL方法。

关于数据的排序:选择“公式”或“SQL查询映射”方法时,检索数据时必须进行排序。这与通过当前映射检索数据没有什么不同。

更新: 在SQL查询XML中纠正了严重的编辑错误。

Sam 17 年前

你可以试试:

session.CreateSqlQuery(@"SELECT {item.*}, dbo.DistanceBetween(:lat1, :lat2, {item}.Latitude, {item}.Longitude) AS Distance
    FROM Items {item}
    ORDER BY Distance")
        .AddEntity("item", typeof(Item))
        .SetDecimal("lat1", lat1)
        .SetDecimal("lat2", lat2)
        .List<Item>()

nhibernate对查询中的表和列别名很挑剔,因此需要让它使用语法扩展它们。另外,使用hql命名参数语法(:lat1而不是@lat1),并将setdecimal()更改为正确的数据类型。

UpTheCreek 16 年前

Ayende在映射文件中解释了如何做到这一点,而不必诉诸于向会话传递SQL:

http://ayende.com/Blog/archive/2006/09/18/UsingNHibernateWithStoredProcedures.aspx

另请参阅有关此问题的Hibernate文档(也与nhibernate相关):

http://docs.jboss.org/hibernate/stable/core/reference/en/html/querysql.html