JPA/Hibernate本地查询无法识别参数。

Question

我正在使用Hibernate/JPA执行本地PostGIS查询。这些查询的问题是它们需要的参数不是经典的X = 'value'形式。

例如，以下行会崩溃

 String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(:lon :lat)'),4326), 0.1)";
  Query query = Cell.em().createNativeQuery(queryString, Cell.class);
  query.setParameter("lon", longitude);
  query.setParameter("lat", latitude);

play.exceptions.JavaExecutionException: org.hibernate.QueryParameterException: could not locate named parameter [lon]
 at play.mvc.ActionInvoker.invoke(ActionInvoker.java:259)
 at Invocation.HTTP Request(Play!)
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [lon]
 at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:358)

以下查询有效，但是：

String queryString = String.format("select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(%f %f)'),4326), 0.1)", longitude, latitude);
Query query = Cell.em().createNativeQuery(queryString, Cell.class);

（但这种方法容易受到SQL注入攻击...）

有人知道如何在这种情况下使用 setParameter() 吗？

- user99054

Answer 1

根据JPA规范（第3.6.3节 命名参数），原生查询不支持使用命名参数：

命名参数遵循4.4.1中定义的标识符规则。命名参数的使用仅适用于Java Persistence查询语言，并未在原生查询中定义。只有位置参数绑定可在原生查询中被移植使用。

因此，请尝试以下方法代替：

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(?1 ?2)'),4326), 0.1)";
Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter(1, longitude);
query.setParameter(2, latitude);

请注意，在JPA >= 2.0中，您可以在本地查询中使用命名参数。

- Pascal Thivent

---

+1 因引用规范并提醒我这不具备可移植性。实际上，在一个使用 Hibernate 的项目中，我曾经成功地在本地查询中使用过命名参数。在另一个基于 TopLink Essentials 的旧项目中，所有本地查询都使用位置参数，所以我先前一定知道这点，只是现在已经忘记了 :) - Jörn Horstmann

@samokk: 我想知道这里是否使用单引号括起来的 POINT 不是问题所在。 - Pascal Thivent

对于 JPA 位置参数，例如 "?1"，您必须调用 query.setParameter("1", longitude); 等等。 - Lord

@Lord 位置参数应该使用 Query.setParameter(int, Object)（而不是 setParameter(String, Object)）。 - Martin

@Martin 理论上是可以的，但实际上在 Hibernate 4.x 中（2013 年）只有 setParameter(String, Object) 对于位置编号参数才有效。对于非编号参数（"?, ?, ?"），setParameter(int, Object) 是可行的。我不知道在 Hibernate 5 中是否仍然有效。 - Lord

Answer 2

也许你可以替换

'POINT(:lon :lat)'

使用

'POINT(' || :lon || ' ' || :lat || ')'

这样做可以将参数放在常量字符串之外，并且应该被查询解析器识别。

- Jörn Horstmann

---

+1有趣的技巧，实际上证实了问题来自于引号，而不是命名参数（在使用JPA时应避免使用命名参数）。 - Pascal Thivent

使用NamedNativeQuery注释在Spring Data中工作，因此不需要创建Repository实现。 - lreeder

Answer 3

我之前也遇到过类似的问题，发现可以在本地查询语句中使用问号来设置参数。请尝试以下方法：

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(? ?)'),4326), 0.1)";

Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter(1, longitude);
query.setParameter(2, latitude);

- Ferenc

Answer 4

我曾经遇到过类似的问题。在存储库中，我使用了带有?1的本地查询。通过将参数用括号括起来解决了这个问题，如下所示。

SELECT * FROM XYZ WHERE ABC = (?1)

http://javageneralist.blogspot.com/2011/06/jpa-style-positional-param-was-not.html

- yousafsajjad

Answer 5

您还可以完全摆脱整个

ST_GeomFromEWKT('POINT(' || :lon || ' ' || :lat || ')')

将call替换为

ST_Point(:lon,:lat)

那么您就不必担心引号的问题了。

- MDAHatter

Answer 6

因此，我们的想法是使用Jörn Horstmann建议的连接技巧来强制Postgres识别参数。以下代码可行：

String queryString = "select * from Cell c where ST_DWithin(c.shape, SetSRID(ST_GeomFromEWKT('POINT(' || :lon || ' ' || :lat || ')'),4326), 0.2)";
Query query = Cell.em().createNativeQuery(queryString, Cell.class);
query.setParameter("lon", longitude);
query.setParameter("lat", latitude);

非常感谢您的回答！

- user99054

---

实际上，无法识别参数的不是Postgres，而是您的JPA提供程序（因为有单引号）。 - Pascal Thivent

Answer 7

Pascal的答案是正确的，但是...你的解决方案如何防止SQL注入？如果你在示例中使用String.format和参数类型％f，那么除了数字之外的任何内容都会抛出java.util.IllegalFormatConversionException异常。没有可能传递像“xxx' OR 1=1 -”这样的值。请注意，在String.format中使用％s会导致SQL注入问题。

- Boris Šuška