JPA/Hibernate Native Queries do not recognize Parameters

Question

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.

For example, the following lines crash

 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)

The following query works however :

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

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

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

Answer 1

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

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.

So try the following instead:

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

---

Note that in JPA >= 2.0 you can use named parameters in native queries.

Answer 2

Maybe you can replace

'POINT(:lon :lat)'

with

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

This way the parameters are outside of constant strings and should be recognized by the query parser.

Answer 3

I had a similar problem and found that parameters can be set with question marks in native queries. Try this:

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

Answer 4

So, the idea was to use the concatenation trick suggested by Jörn Horstmann to force postgres to recognize the parameters. The following code works :

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

Thanks a lot for your answers !

Answer 5

You can also get rid of the whole

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

call and replace it with

ST_Point(:lon,:lat)

Then you don't have to worry about quotes.

Answer 6

Pascal's answer is correct, but... How is your solution SQL injection prone? If you're using String.format and parmater type %f in your example then anything else than number throws java.util.IllegalFormatConversionException. There is no possibililty pass value like "xxx' OR 1=1 --".

Be careful, using %s in String.format is SQL injection ready.