Spring Data Rest: "Date is null" query throws an postgres exception

Question

I use Spring Boot and Data Rest to create a simple microservice in Java8 and get a postgres exception.

My entity:

@Entity
public class ArchivedInvoice implements Serializable {
    ...
    @Column
    private String invoiceNumber;
    @Column
    private java.sql.Date invoiceDate;
    ...
}

My repository interface:

@RepositoryRestResource(collectionResourceRel = "archivedinvoices", path = "archivedinvoices")
public interface ArchivedInvoiceRepository extends PagingAndSortingRepository < ArchivedInvoice, Long > {
    ...
@RestResource(rel = "findByXYZ", path = "findByXYZ")
@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
        + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
        + "(:invoiceDate IS NULL OR ai.invoiceDate = :invoiceDate)" 
        )
public Page < ArchivedInvoice > findByXYZ(
        @Param("invoiceNumber") @Nullable String invoiceNumber,
        @Param("invoiceDate") @Nullable Date invoiceDate,
        Pageable p);
    ...
}

If I call ".../findByXYZ?invoiceDate=2016-02-22", I'll get the following error message:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...
Caused by: org.postgresql.util.PSQLException: FEHLER: could not determine data type of parameter

But it works, when I remove the ":invoiceDate IS NULL" part. How can I check, if the invoiceDate parameter is null?

Answer 1

I've spend some time looking at this because I really want to compare "date is null" in querys and that's the result:

When you use the "cast(foo.date as date) is null", it works OK if the field is not null. If the field is null this exception is throwed:

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to date

The solution is use coalesce:

coalesce(:date, null) is null

It works fine having or not data in the field tested.

My query example:

@Query("SELECT c "
        + " FROM Entity c "
        + " WHERE "
        + "       and ( (coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is null) "
        + "             or ((coalesce(c.date, null) is not null) "
        + "                 and ( "
        + "                        ((coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is not null) and c.date <= :dateUntil) "
        + "                     or ((coalesce(:dateUntil, null) is null and coalesce(:dateFrom, null) is not null) and c.date >= :dateFrom)"
        + "                     or (c.date between :dateFrom and :dateUntil)"
        + "                 )"
        + "             )"
        + "       ) "

Hope it works for you!

Answer 2

I believe @Bonifacio is correct in that Postgres is not able to determine the type of your @Param("invoiceDate") parameter when performing the IS NULL test. I have queries similar to yours that behave as expected with an in memory H2 database, but fail with the Postgres integration tests.

I was able to get around this by casting the parameter to a date like so:

@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "
    + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "
    + "(cast(:invoiceDate as date) IS NULL OR ai.invoiceDate = :invoiceDate)" 
    )

Answer 3

I have had similar problems with the field of type LocalDateTime and field Timestamp in postgres 9.5. I solved it converting it like this:

    @Query("SELECT c "
        + "FROM Contract c "
        + "WHERE "
        + "(:idContract IS NULL OR c.idContract = :idContract) AND "
        + "(CAST(:dtCancelInitial AS java.time.LocalDateTime) IS NULL OR (c.dtCancel >= :dtCancelInitial AND c.dtCancel < :dtCancelFinal)) "
        + "ORDER BY c.idContract")
List<Contract> findContratConsultaCancelamento(@Param("idContract") Long idContract, @Param("dtCancelInitial") LocalDateTime dtCancelInitial, @Param("dtCancelFinal") LocalDateTime dtCancelFinal);