CODEWITHSUNDEEP
sqlpostgresqlhibernatespring-bootjpa
Morteza Jalambadani
Morteza Jalambadani

Reputation: 2465

Postgres Sql `could not determine data type of parameter` by Hibernate

I have 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
);

error is :

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]

but when a change query to:

@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
);

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

note:

offLoadingDate in CardReaderEntity:

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

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

Upvotes: 53

Views: 92855

Answers (3)

Terry Windwalker
Terry Windwalker

Reputation: 1886

By my experience, you must assign a type to the outer value before you can ask PostgreSQL to check whether it is null or not. For example, this code won't work for PostgreSQL:

SELECT * FROM table WHERE $1 IS NULL OR column = $1;

And what would work is:

SELECT * FROM table WHERE $1::text IS NULL OR column = $1::text;

You can try applying the correct type to your variables to see if it works out. In your example, you should try

@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::timestamp is null or cr.offLoadingDate >= :fromDate::timestamp ) " +
            "AND  (:toDate::timestamp   is null or cr.offLoadingDate <= :toDate::timestamp   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)

In fact, PostgreSQL won't ask for types in most of cases, checking null value is one of the special cases where type becomes necessary.

Upvotes: 27

dev_2014
dev_2014

Reputation: 371

Add casting only when comparing to null and not when actual data comparison occurs.

    @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
);

Upvotes: 7

PowR
PowR

Reputation: 1140

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.

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 )

use

(cast(:fromDate as date) is null )

same for toDate

Upvotes: 70

Related Questions