问Spring Data Rest："Date is null“查询抛出postgres异常
EN

Stack Overflow用户

提问于 2016-02-23 07:46:43

回答 4查看 12K关注 0票数 15

我使用Spring Boot和Data Rest在Java8中创建了一个简单的微服务，并获得了postgres异常。

我的实体：

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

我的仓库界面：

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

如果我调用".../findByXYZ?invoiceDate=2016-02-22"，我会得到以下错误信息：

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

但是，当我删除":invoiceDate IS NULL“部分时，它就可以工作了。如何检查invoiceDate参数是否为空？

postgresql

jpa

spring-data

spring-data-rest

hibernate

回答 4

Stack Overflow用户

发布于 2019-01-16 18:58:05

我花了一些时间来研究这一点，因为我真的想比较查询中的"date is null“，这就是结果：

当您使用"cast(foo.date as date) is null“时，如果字段不为空，则可以正常工作。如果字段为空，则抛出此异常：

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

解决方案是使用coalesce：

coalesce(:date, null) is null

无论有没有现场测试的数据，它都可以很好地工作。

我的查询示例：

@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)"
        + "                 )"
        + "             )"
        + "       ) "

希望它能为你工作！

票数 10

Stack Overflow用户

发布于 2016-08-31 16:57:03

我相信@Bonifacio是正确的，因为Postgres在执行IS NULL测试时无法确定@Param("invoiceDate")参数的类型。我有一些与您的查询类似的查询，它们在内存中的H2数据库中表现得与预期一样，但在Postgres集成测试中失败。

我可以通过将参数转换为如下所示的日期来解决此问题：

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

票数 7

Stack Overflow用户

发布于 2018-05-21 19:03:59

我在Postgres9.5中的LocalDateTime类型的字段和字段Timestamp中也遇到过类似的问题。我解决了它的转换，如下所示：

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