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 ?

score 84 · Answer 1 · edited Aug 26 '15 at 09:46

84

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.

edited Aug 26 '15 at 09:46

Emil Sierżęga

1,785
2
31
38

answered Jun 29 '10 at 22:35

Pascal Thivent

562,542
136
1,062
1,124

4

+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 :) – Jörn Horstmann Jun 29 '10 at 23:53
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? – user99054 Jun 30 '10 at 12:32
1

@samokk: I wonder if the single quotes around POINT are not the problem here. – Pascal Thivent Jun 30 '10 at 13:16
2

for JPA-position parameters like "?1 " you have to call query.setParameter("1", longitude); etc/ – Lord Sep 24 '13 at 11:35
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. – emeraldhieu Jun 17 '15 at 04:17
1

@Lord Positional parameters should use [`Query.setParameter(int, Object)`](https://docs.oracle.com/javaee/7/api/javax/persistence/Query.html#setParameter-int-java.lang.Object-) (not `setParameter(String, Object)`. – Martin Aug 09 '18 at 11:05
1

@Martin 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. – Lord Aug 26 '18 at 14:36
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." – DEWA Kazuyuki - 出羽和之 Jan 29 '19 at 00:52

Jörn Horstmann · Answer 2 · 2012-08-15T15:29:39.507

19

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.

edited Aug 15 '12 at 15:29

answered Jun 29 '10 at 20:50

Jörn Horstmann

33,639
11
75
118

3

+1 Interesting trick which actually confirms the problem comes from the quotes, not from named parameters (that one should avoid with JPA though). – Pascal Thivent Jun 30 '10 at 14:46
Works in Spring Data with NamedNativeQuery annotation, so doesn't require creation of Repository implementation. – lreeder Oct 17 '15 at 21:38

score 4 · Answer 3 · answered Feb 26 '14 at 21:38

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

score 2 · Answer 4 · edited Dec 05 '12 at 16:51

2

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.

edited Dec 05 '12 at 16:51

Garrett Hyde

5,409
8
49
55

answered Dec 05 '12 at 16:27

MDAHatter

21
1

score 2 · Answer 5 · answered Feb 26 '13 at 17:47

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.

score 2 · Answer 6 · answered Mar 16 '15 at 20:44

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

score 2 · Answer 7 · answered Jun 30 '10 at 12:50

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 !

Actually, it's not postgres that wasn't recognizing the parameters, but your JPA provider (because of the single quotes). — Pascal Thivent, Jul 01 '10 at 17:14

JPA/Hibernate Native Queries do not recognize Parameters

7 Answers7

Linked

Related