休眠异常.QuerySyntaxException:意外令牌:HAVING [英] Hibernate exception. QuerySyntaxException: unexpected token: HAVING

问题描述

我尝试使用此HQL查询:

I try use this HQL query:

   Result.find("SELECT c, ( 3959 * acos( cos( radians(?) ) * "+
    "cos( radians( c.latitude ) ) *"+
    "cos( radians( c.longitude ) - radians(?) ) +"+
    "sin( radians(?) ) * sin( radians( c.latitude ) ) ) ) " +
    "AS distance FROM City c HAVING distance < ? ORDER BY distance ASC",
    latitude, longitude, latitude, radius).fetch();

但结果是:

IllegalArgumentException occured : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: HAVING near line 1, column 204 [SELECT c, ( 3959 * acos( cos( radians(?) ) * cos( radians( c.latitude ) ) *cos( radians( c.longitude ) - radians(?) ) +sin( radians(?) ) * sin( radians( c.latitude ) ) ) ) AS distance FROM models.City c HAVING distance < ? ORDER BY distance ASC]

推荐答案

唯一方法是使用本机查询，因为:

The only way is to use a native query because :

• subselect是不可能的:请注意，HQL子查询只能在select或where子句中发生." http://docs.jboss.org/hibernate/core/4.3/manual/zh-CN/html_single/#queryhql-subqueries
• 没有分组依据就不可能
• 哪里都不可能

Java代码:

@Query(value = "SELECT s.*,\n" +
            "  (\n" +
            "    3959 * acos(\n" +
            "        cos(radians(s.lat))\n" +
            "        * cos(radians(:lat))\n" +
            "        * cos(radians(:lon) - radians(s.lon))\n" +
            "        + sin(radians(s.lat))\n" +
            "          * sin(radians(:lat))\n" +
            "    )\n" +
            "  ) AS distance\n" +
            "FROM Stop s\n" +
            "HAVING distance < 30\n" +
            "ORDER BY distance asc", nativeQuery = true)
    List<Stop> findClosestStops(@Param("lat") Double lat, @Param("lon") Double lon);

另一种方法是返回包含城市+距离的对象的列表，但是不可能在任何地方，但是您可以限制结果集

An alternative is to return a list of object containg the city + distance, but no where is possible, but you can limit the resultset

这篇关于休眠异常.QuerySyntaxException:意外令牌:HAVING的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！