Hibernate的Postgres Sql\`无法确定参数的数据类型\`

Question

我有 JpaRepository ：

 @Repository
public interface CardReaderRepository extends JpaRepository<CardReaderEntity, Integer > {

}

当我在此 repo 中执行与此相同的查询时：

 @Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (:fromDate is null or cr.offLoadingDate >= :fromDate ) " +
            "AND  (:toDate   is null or cr.offLoadingDate <= :toDate   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

错误是：

 org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ~[postgresql-42.2.2.jar:42.2.2]

但是当将查询更改为：

 @Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (:fromDate is null ) " +
            "AND  (:toDate   is null ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

这项工作没有错误，但希望使用 fromDate 和 toDate 运行

笔记：

CardReaderEntity 中的 offLoadingDate ：

 @Basic
@Temporal( TemporalType.TIMESTAMP )
public Date getOffLoadingDate() {
    return offLoadingDate;
}
public void setOffLoadingDate(Date offLoadingDate) {
    this.offLoadingDate = offLoadingDate;
}

我的 java 文件中的所有 Date 导入都是 java.util.Date 。

原文由 Morteza Jalambadani 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

仅在与 null 比较时添加转换，而不是在发生实际数据比较时添加。

     @Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (cast(:fromDate as timestamp) is null or cr.offLoadingDate >= :fromDate ) " +
            "AND  (cast(:toDate as timestamp)   is null or cr.offLoadingDate <= :toDate   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

原文由 dev_2014 发布，翻译遵循 CC BY-SA 4.0 许可协议