java - Las consultas nativas JPA/Hibernate no reconocen los parámetros

hibernate orm select

Question

Estoy usando Hibernate / JPA para ejecutar consultas nativas PostGIS. El problema con estas consultas es que necesitan parámetros que no son de la forma clásica X = ''valor''.

Por ejemplo, las siguientes líneas bloquean

 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)

La siguiente consulta funciona sin embargo:

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);

(pero es propenso a la inyección de SQL ...)

¿Alguien sabe cómo usar setParameter() en este caso?

Answer 1

El uso de parámetros con nombre no está definido para consultas nativas. De la especificación JPA (sección 3.6.3 Parámetros nombrados ):

Los parámetros con nombre siguen las reglas para los identificadores definidos en la Sección 4.4.1. El uso de parámetros con nombre se aplica al lenguaje de consulta de Java Persistence y no está definido para consultas nativas. Solo el enlace de parámetros posicionales se puede usar de forma portátil para consultas nativas .

Intente lo siguiente en su lugar:

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);

Tenga en cuenta que en JPA> = 2.0 puede usar parámetros con nombre en consultas nativas.

Answer 2

Enfrenté un problema similar. Estaba usando la consulta nativa en el repositorio con? 1. Lo resolvió rodeando el parámetro entre paréntesis como el siguiente.

SELECT * FROM XYZ WHERE ABC = (?1)

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

Answer 3

Entonces, la idea era usar el truco de concatenación sugerido por Jörn Horstmann para forzar a postgres a reconocer los parámetros. El siguiente código funciona:

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);

Muchas gracias por sus respuestas !

Answer 4

La respuesta de Pascal es correcta, pero ... ¿Cómo es propensa la inyección SQL de su solución? Si está utilizando String.format y parmater escriba %f en su ejemplo, todo lo que no sea number arrojará java.util.IllegalFormatConversionException. No hay ningún valor de pase posible como "xxx" O 1 = 1 - ".

Tenga cuidado, usando %s en String.format está listo para inyección de SQL.

Answer 5

Quizás puedas reemplazar

''POINT(:lon :lat)''

con

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

De esta manera, los parámetros están fuera de las cadenas constantes y deben ser reconocidos por el analizador de consultas.

Answer 6

También puedes deshacerte de todo

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

llamar y reemplazarlo con

ST_Point(:lon,:lat)

Entonces no tienes que preocuparte por las cotizaciones.

Answer 7

Tuve un problema similar y descubrí que los parámetros se pueden establecer con signos de interrogación en consultas nativas. Prueba esto:

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);