JPA/Hibernate 本机查询无法识别参数 [英] JPA/Hibernate Native Queries do not recognize Parameters

问题描述

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

I am using Hibernate/JPA to execute native PostGIS queries. The problem with these queries is that they need parameters that are not of the classical X = 'value' form.

例如，以下几行崩溃

 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 注入...)

(but it is SQL-injection-prone...)

有谁知道在这种情况下如何使用setParameter()?

Does anyone know how to use setParameter() in this case ?

推荐答案

未为本地查询定义命名参数的使用.来自 JPA 规范(3.6.3 命名参数部分):

The use of named parameters is not defined for native queries. From the JPA specification (section 3.6.3 Named Parameters):

命名参数遵循以下规则第 4.4.1 节中定义的标识符.命名参数的使用适用于Java 持久化查询语言，并且不是为本地查询定义的.只有位置参数绑定可以可移植地用于本机查询.

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

因此请尝试以下操作:

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 中，您可以在本机查询中使用命名参数.

这篇关于JPA/Hibernate 本机查询无法识别参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！