postgresql - 错误：运算符不存在：没有时区的时间戳 >= boolean 提示：没有运算符与给定的名称和参数类型匹配

问题描述

我正在尝试使用 JPA 执行命名查询。查询必须根据查询的条件搜索和提取记录。标准是查询应该提取特定给定时间（提供的从和到日期之间的记录）和应用程序名称之间的记录。

在 Postgresql 中执行时，查询工作正常。但是，通过 JPA，它在执行查询时会出错

Here is my Query in PostgreSQL :
SELECT  auditLog.busn_sys_id            
        , sourceSystem.busn_sys_full_nm     
        , auditLog.purge_ts                 
        , auditLog.rec_purge_cnt            

FROM    rec_ret.rec_rtn_purge_adt                   auditLog
LEFT    JOIN    gbl_dm.gbl_busn_sys_dm              sourceSystem            on (auditLog.busn_sys_id = sourceSystem.busn_sys_id)

    WHERE   (auditLog.purge_ts BETWEEN '2019-08-19' AND '2019-08-25')
      and  sourceSystem.busn_sys_full_nm like 'PROFILE' 

    order by auditLog.busn_sys_id ;

这是我获取结果列表的 JPA 实现

try{

            Query query = entityManager.createNativeQuery("SELECT auditLog.busn_sys_id, sourceSystem.busn_sys_full_nm, auditLog.purge_ts, auditLog.rec_purge_cnt " +
                    "FROM rec_ret.rec_rtn_purge_adt auditLog " +
                    "LEFT JOIN gbl_dm.gbl_busn_sys_dm sourceSystem on (auditLog.busn_sys_id = sourceSystem.busn_sys_id) " +
                    "WHERE (auditLog.purge_ts BETWEEN auditLog.purge_ts = :requestedFrom AND auditLog.purge_ts = :requestedTo) " +
                    //"WHERE (auditLog.purge_ts BETWEEN requestedFrom = (?) AND requestedTo = (?)) " +
                    "and  sourceSystem.busn_sys_full_nm = :sourceSystem " +
                    "order by auditLog.busn_sys_id ");

            query.setParameter("requestedFrom",auditLogCriteria.getFromDate());
            query.setParameter("requestedTo",auditLogCriteria.getToDate());
            query.setParameter("sourceSystem",auditLogCriteria.getSourceSystem());;

            query.executeUpdate();
            return query.getResultList();


        }catch (Exception e){
            logger.error("Fetching of logs failed with message : " + e.getMessage());
            throw e;
        }

这是我的实体/模型类代码的实现

@NotNull (message = "Name of the Source System should be entered")
    private String sourceSystem;

    @NotNull (message = "Specify FromDate to filter records")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss[.SSS]")
    @Convert(converter = LocalDateTimeConverter.class)
    private LocalDateTime fromDate;

    @NotNull (message = "Specify ToDate to filter records")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss[.SSS]")
    @Convert(converter = LocalDateTimeConverter.class)
    private LocalDateTime toDate;
\\ Getters and Setters

这是错误：

Hibernate: SELECT auditLog.busn_sys_id, sourceSystem.busn_sys_full_nm, auditLog.purge_ts, auditLog.rec_purge_cnt FROM rec_ret.rec_rtn_purge_adt auditLog LEFT JOIN gbl_dm.gbl_busn_sys_dm sourceSystem on (auditLog.busn_sys_id = sourceSystem.busn_sys_id) WHERE (auditLog.purge_ts BETWEEN auditLog.purge_ts = ? AND auditLog.purge_ts = ?) and  sourceSystem.busn_sys_full_nm = ? order by auditLog.busn_sys_id 

[ERROR] 2019-08-29 10:02:00.558 [http-nio-8080-exec-2] SqlExceptionHelper - ERROR: operator does not exist: timestamp without time zone >= boolean
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 267
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1593)

因此，结果应该是所有符合条件的记录都必须作为对 JSON 请求的响应返回

标签： postgresqlspring-bootspring-data-jpa

postgresql - 错误：运算符不存在：没有时区的时间戳 >= boolean 提示：没有运算符与给定的名称和参数类型匹配

问题描述

解决方案

推荐阅读