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 ?

Short

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.

Elutriate

+1 for citing the spec and reminding me that this is not portable. I have actually used named parameters in native queries in a hibernate project,which is working fine. In another older project, based on toplink essentials, all native queries use positional parameters, so I must have known this before and forgotten :) – Saval

Hi, thanks for your answer. However, it still seems to crash on setParameter(1, longitude) org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1 any idea? – Short

@samokk: I wonder if the single quotes around POINT are not the problem here. – Elutriate

for JPA-position parameters like "?1 " you have to call query.setParameter("1", longitude); etc/ – Sobranje

This answer should be accepted as the best one. The problem with setParameter(position, value) is sometimes we don't have enough parameter as theory. Using (?1) and (?2) help the people like to build the query on the fly. – Boardwalk

@Sobranje Positional parameters should use Query.setParameter(int, Object) (not setParameter(String, Object). – Vesper

@Vesper In theory - yes, in practice - at the time (2013) in Hibernate 4.x only setParameter(String, Object) was working for positional numbered parameters. For non-numbered parameters ("?, ?, ?") setParameter(int, Object) was OK. I don't know if it's still valid for Hibernate 5. – Sobranje

On JPA2.2, native query with named parameters is still optional. 3.10.16.4 Restrictions "The use of named parameters is not defined for native SQL queries. Only positional parameter binding for SQL queries may be used by portable applications." – Rutaceous

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.

Halo

+1 Interesting trick which actually confirms the problem comes from the quotes, not from named parameters (that one should avoid with JPA though). – Elutriate

Works in Spring Data with NamedNativeQuery annotation, so doesn't require creation of Repository implementation. – Wellfounded

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

Extrajudicial

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 !

Short

Actually, it's not postgres that wasn't recognizing the parameters, but your JPA provider (because of the single quotes). – Elutriate

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.

Chemosh

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.

Gerrald

Answer 7

I faced similar issue. I was using native query in the repository with ?1. It resolved it by surrounding the parameter around brackets like the following.

SELECT * FROM XYZ WHERE ABC = (?1)

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

Platinumblond