postgresql - Spring JPA Hibernate 在 postgres 中给出错误更新时间戳 wo 时区

Question

我一直在尝试使用更新 stmt 来更新时间戳字段。我尝试了 java.sql.timestamp、java.util.date、Calendar、LocalDateTime、ZonedDateTime 和其他一些 java 日期实用程序包。他们似乎都没有工作。

列:commit_ts(Postgresql 中没有时区的时间戳)在我们的 JPA/Hibernate 中定义为

@Column(name = "COMMIT_TS")
@Temporal(TemporalType.TIMESTAMP)
private Timestamp commitTs;

这是查询

@Timed(name = "updateWorkAllocationStatus")
@Transactional
@Modifying(clearAutomatically = true)
@Query(
      nativeQuery = true,
      value = "UPDATE wlm_work_allocation SET commit_ts=:ts " + 
              "WHERE allocation_id = :allocationId " + 
              "and status = :status " + 
              "and commit_ts == null"
)
int updateWorkAllocationStatus(
    @Param("timestamp") Timestamp ts,
    @Param("allocationId")Long allocationId,
    @Param("status")String status
);

我也试过 NativeQueries

@NamedNativeQuery(name = "WorkAllocationEntity.updateCommitTs",
        query="UPDATE wlm_work_allocation SET commit_ts= TIMESTAMP WHERE allocation_id=:allocationId and status=:status and commit_ts==null")

注意:根据此链接，SQL 标准要求仅写入时间戳等同于不带时区的时间戳，PostgreSQL 尊重该行为。 https://www.postgresql.org/docs/9.1/static/datatype-datetime.html

Springboot版本:1.5.7.RELEASE

Postgres JDBC 驱动程序:9.0-801.jdbc4

PostgresSQL 数据库:9.6.5

Error: 
org.postgresql.util.PSQLException: ERROR: operator does not 
exist: timestamp without time zone == unknown
Hint: No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

有人能帮忙吗？

Answer 1

JPQL和SQL中的比较运算符是=not ==

commit_ts==null 

应该是

commit_ts is null