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

本文介绍了Postgres Sql`无法确定Hibernate的parameter的数据类型的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有 JpaRepository :

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


}

当我在此仓库中执行与此相同的查询时:

when i execute query in this repo same as this:

@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

this work without error but a want run with fromDate and toDate

注意:

卸载日期:

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

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

all of Date import in my java files is java.util.Date.

推荐答案

PostgreSQL驱动程序试图找出参数的类型，以将这些参数直接告知PostgreSQL服务器.PostgreSQL服务器必须能够比较字段，这是必要的.如果使用java.sql.Timestamp，则PostgreSQL驱动程序将无法执行该操作，因为PostgreSQL有两个匹配的字段.一方面是时间戳，另一方面是带有时区信息的时间戳.结果是，PostgreSQL驱动程序将其与Oid.UNSPECIFIED匹配.在大多数情况下，此行为不是问题，因为PostgreSQL服务器能够检测到该类型.PostgreSQL驱动程序类PgPreparedStatement中对此问题进行了详细描述.

The PostgreSQL driver tries to figure out the type of the parameters to tell those parameters directly to the PostgreSQL Server. This is necessary that the PostgreSQL server is able to compare fields. In case of a java.sql.Timestamp the PostgreSQL driver is just not able to do it since there are two matching fields for PostgreSQL. On the one side, there is the timestamp and on the other side the timestamptz with timezone information. The result is, that the PostgreSQL driver will just match it to Oid.UNSPECIFIED. This behavior is most of the time not an issue since the PostgreSQL server is able to detect the type. There is a detailed description of this issue in the PostgreSQL driver class PgPreparedStatement.

因此，仅在Postgres无法检测到正确的类型时(当您检查null时)，才可以强制将其强制转换为时间戳/日期类型.因此，而不是

So what you can do is force cast to timestamp/date type only when Postgres is not able to detect proper type (when you are checking for null).So instead of

(:fromDate is null )

使用

(cast(:fromDate as date) is null )

与toDate相同

这篇关于Postgres Sql`无法确定Hibernate的parameter的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

With