gpt4 book ai didi

postgresql - Postgres 异常错误 : syntax error at or near ":" Position: 46

转载 作者:行者123 更新时间:2023-12-02 00:18:15 24 4
gpt4 key购买 nike

我在执行以下查询时遇到 SQL 异常。

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

休眠:

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

最佳答案

Hibernate 将 : 视为参数占位符,搞乱了您的转换。

最简单的方法是使用 cast() 运算符代替:

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

关于postgresql - Postgres 异常错误 : syntax error at or near ":" Position: 46,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56335419/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com