postgresql - 我如何使用 JPA native 查询更新点类型数据？

Question

我编写了要更新的查询，但其中出现错误。

@Modifying
@Transactional
@Query(value = "UPDATE lat_lang SET geom=ST_transform(ST_SetSRID(ST_MakePoint(:lat,:lang),4326),3857),latitude=:lat,longitude=:lang WHERE uid=1;", nativeQuery = true)

int updateGeom(@Param("lat") Double lat, @Param("lang") Double lang);

我遇到了这个错误

org.postgresql.util.PSQLException: ERROR: function st_makepoint(bytea, bytea) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 50
    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.executeUpdate(PgPreparedStatement.java:120) ~[postgresql-42.2.5.jar:42.2.5]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:107) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1593) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:292) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1584) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:256) ~[spring-data-jpa-2.1.8.RELEASE.jar:2.1.8.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.1.8.RELEASE.jar:2.1.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.1.8.RELEASE.jar:2.1.8.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.1.8.RELEASE.jar:2.1.8.RELEASE]

Answer 1

1.首先通过替换原始纬度和经度值进行检查:

@Query(value = "UPDATE lat_lang SET geom=ST_transform(ST_SetSRID(ST_MakePoint(28.7041,77.1025),4326),3857),latitude=28.7041,lon gitude=77.1025 WHERE uid=1;", nativeQuery = true);
int updateGeom();

如果它不起作用，则传递参数有问题。

2.

确保您在函数前没有缺少架构名称，即 schema.ST_MakePoint，因为它是 native 查询。

1. 检查这两个常见错误:1和 2