CODEWITHSUNDEEP
javahibernateormjpapostgis
user99054
user99054

Reputation: 599

JPA/Hibernate Native Queries do not recognize Parameters

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 ?

Upvotes: 50

Views: 103517

Answers (7)

Pascal Thivent
Pascal Thivent

Reputation: 570295

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.

Upvotes: 84

yousafsajjad
yousafsajjad

Reputation: 973

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

Upvotes: 2

Ferenc
Ferenc

Reputation: 141

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

Upvotes: 4

Boris Šuška
Boris Šuška

Reputation: 1794

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.

Upvotes: 2

MDAHatter
MDAHatter

Reputation: 21

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.

Upvotes: 2

Jörn Horstmann
Jörn Horstmann

Reputation: 34014

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.

Upvotes: 19

user99054
user99054

Reputation: 599

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 !

Upvotes: 2

Related Questions