使用Hibernate的JPQL类型查询中的时间戳文本

Question

TypedQuery<TestEntity> query = entityManager
.createQuery("select t from TestEntity t where greatest(nvl(t.f1, :nullDate), nvl(t.f2, :nullDate)) between :date1 and :date2")
.setParameter("date1", date1)
.setParameter("date2", date2)
.setParameter("nulllDate", LocalDateTime.of(1970, 1, 1, 0, 0, 0, 1))

select t from TestEntity t where greatest(nvl(t.f1, {ts '1970-01-01 00:00:00:000000001'}), nvl(t.f2, {ts '1970-01-01 00:00:00:000000001'})) between :startChanges and :endChanges

org.hibernate.QueryException: unexpected char: '{' [select t from TestEntity t where greatest(nvl(t.f1, {ts '1970-01-01 00:00:00:000000001'}), nvl(t.f2, {ts '1970-01-01 00:00:00:000000001'})) between :startChanges and :endChanges]

UPD：也许它不在规范中，但是最大的/NVL函数可以通过上面的代码从JPQL正确地传递到SQL。我尝试在函数中包装master/NVL，但仍然不接受文字。

Answer 1

我在我的环境中准备了一个测试，这个测试返回结果：

Calendar calendar = Calendar.getInstance();
calendar.setTime(date1);
calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

date1 = calendar.getTime();

calendar.setTime(date2);
calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

date2 = calendar.getTime();

TypedQuery<TestEntity> query = entityManager
.createQuery("select t from TestEntity t where greatest(nvl(t.f1, TO_TIMESTAMP('1970-01-01 00:00:00,000000001', 'YYYY-MM-DD HH24:MI:SS,FF9')), nvl(t.f2, TO_TIMESTAMP('1970-01-01 00:00:00,000000001', 'YYYY-MM-DD HH24:MI:SS,FF9')) between :date1 and :date2")
.setParameter("date1", date1)
.setParameter("date2", date2);

我希望这就是你要找的。

编辑：修改查询以添加to_timestamp函数。经@dfche证实，它正在工作