spring-data-jpa PostgresSQL + Spring JPA:org.postgresql.util.PSQLException:错误:无法将类型bytea转换为没有时区的时间戳

wecizke3  于 2022-11-10  发布在  Spring
关注(0)|答案(3)|浏览(505)

当日期范围有时可能包含空值时,如何根据日期范围选择行(选择所有行)

我有一个名为Project(id,name,certification_date)的表; ID为int,名称为varchar(250),认证日期为时间戳。
插入项目时,只插入标识和名称,但certification_date为NULL。稍后用户可以证明该项目,然后该行将更新为certification_date,即用户证明该项目的日期。因此,在项目表中,我可以让某些行的certification_date为NULL,而某些行的certification_date为日期(例如,2020-06-18 00:12:07)
现在,我有一个名为的搜索查询,并有一个startDate和endDate过滤器来过滤certification_date。下面是带有完整签名的Repository查询。

@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as timestamp) is null or p.CERTIFICATION_DATE < :endDate ))",  nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

因此,当searchProjects()对所有值执行时,它会正确返回结果。但当我不需要日期过滤器时,即startDate和endDate为空时,它会失败。(这里,searchKey没有任何问题,因为如果searchKey为空,我传递“”,它会返回所有值)
例外情况为

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone

那么,我需要做些什么来使startDate和endDate成为可选的,以便在没有传递这些日期的情况下只返回所有行(无论certification_date是空值还是有一些值)呢?
我也尝试了许多解决方案从Stackoverflow,但他们不为我工作.

zkure5ic

zkure5ic1#

  1. JPA无法区分'null'的值,尤其是PostgreSQL中'date,timestamp,timestamptz'类型的Map。
    1.我的解决方案如下:
@Query(value = "select distinct p.id, p.name, p.certification_date from project p " +
            "where LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') " +
            "and (cast(cast(:startDate as text) as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) " +
            "and (cast(cast(:endDate as text) as timestamp) is null or p.CERTIFICATION_DATE < :endDate)",  nativeQuery = true)
    public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
mzillmmw

mzillmmw2#

class Employee {
 int id;
 Date birthDate;
}

List<Employee> findByBirthDateNotNullAndBirthDateLessThanEqualAndBirthDateGreaterThanEqual(Date startDate,Date endDate)
wxclj1h5

wxclj1h53#

仅在与null进行比较时添加强制转换,而不在进行实际数据比较时添加。

@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as date) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as date) is null or p.CERTIFICATION_DATE < :endDate ))",  nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

相关问题