Alex Rivera | Logout

JPA/Hibernate Native Queries do not recognize Parameters

Asked 2010-06-29T19:55:44.947
50

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 ?

java hibernate orm jpa postgis
Edit
Report

2 Answers

2

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 !

answered 2010-06-30T12:50:26.880
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.

answered 2012-12-05T16:27:30.073

Your Answer