Postgres Exception ERROR: syntax error at or near ":" Position: 46

Question

I'm getting SQL exception while executing the below query.

@Query(value="SELECT * FROM lat_long WHERE ST_DWithin(geom :: geography,ST_SetSRID(ST_MakePoint(:lat, :lang),4326) :: geography,1000);",nativeQuery=true)

List<LatLong>find(@Param("lat")Double lat,@Param("lang")Double lang);

Hibernate:

SELECT
    * 
FROM
    lat_long 
WHERE
    ST_DWithin(geom : geography,ST_SetSRID(ST_MakePoint(?, ?),4326) : geography,1000);

2019-05-28 10:39:55.861  WARN 7374 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2019-05-28 10:39:55.861 ERROR 7374 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near ":"
  Position: 46
2019-05-28 10:39:55.885 ERROR 7374 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  Position: 46
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106) ~[postgresql

Answer 1

Hibernate treats the :as a parameter placeholder, messing up your cast.

The easiest way is to use a cast() operator instead:

SELECT * 
FROM lat_long 
WHERE ST_DWithin(cast(geom as geography), cast(ST_SetSRID(ST_MakePoint(:lat, :lang),4326) as  geography),1000)