使用空日期参数的Hibernate本地查询

Question

我在一个看似简单的Hibernate用例中遇到了困难，无法弄清楚发生了什么：

这是我的实体：

@Entity
@Data
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Event {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(columnDefinition = "timestamp without time zone", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date date;

    @Column(columnDefinition = "text")
    private String status;

    public Event() {}
}

这是我的本地查询，用可能为NULL的“date”调用：

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = :date")
Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

传递到函数的日期参数已经被截断为日期格式（即没有小时、分钟等），但是数据库条目不是这样，因此我在比较的左侧使用了DATE() SQL函数。当运行一个空日期查询时，会出现以下错误：

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: date = bytea
  Indice : No operator matches the given name and argument type(s). You might need to add explicit type casts.

据我所知，SQL标准不会执行条件短路运算，因此第二个部分总是会被计算。另一方面，由于日期类型为null，Hibernate无法推断出“date”类型，因此将其作为二进制注入请求，导致错误。

我尝试了同样的变体，结果相同：

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = COALESCE(:date, '2000-01-01')")
Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

编辑： 我也尝试了这个：

@Query(value = "SELECT e FROM Event e WHERE :date is null or DATE(e.date) = CAST(:date AS date)")

还有另一个反映同样问题的错误：

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

编辑2:

为确保当参数为NULL时第二个条件部分不被评估，我尝试了这种方法，使用CASE WHEN .. THEN语法：

@Query(value = "SELECT e FROM Event e WHERE (case when :date is null then true else (DATE(e.date) = cast(:date AS date)) end)"

但是Hibernate不喜欢它，我不知道为什么...

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 1, column 30 [SELECT e FROM Event e WHERE (case when :date is null then true else (DATE(e.date) = cast(:date AS date)) end)]

- Neumann

---

你尝试过这个方法吗？Page<Event> findEvents(Pageable pageable, @Param("date") @Temporal java.util.Date date); 这可能会引起Spring将参数设置为 "Date null"。 - Tobias Liefke

是的！就是这样！它有效： SELECT e FROM Event e WHERE (DATE(e.date) = cast(:date AS date) or cast(:date AS date) is null)。同时在日期参数上使用@Temporal注释。你能把这个作为答案发布吗？这样我就可以给你奖励了。谢谢。 - Neumann

Answer 1

如果您将@Temporal添加到Date参数上，Spring Data就知道如何将该参数传递给Hibernate，即使它是null：

Page<Event> findEvents(Pageable pageable, @Param("date") @Temporal Date date);

（参数的类型必须是java.util.Date，而不是java.sql.Date。）

关于“为什么SQL/Hibernate不能进行短路”的另一个注释：对于大多数数据库来说，重新排序谓词是一项重要的性能特性，并且为了做到这一点，必须首先绑定参数。大多数数据库确实会对谓词进行短路处理，但不一定按照你编写查询的顺序。

- Tobias Liefke

---

这同样适用于查询注释，但是参数和实体属性的日期值必须转换为日期。 - Camilo Andrés Ospina

那对于我的命名查询有效，谢谢！ - Vnuuk

LocalDate怎么样？ - user666

我猜那是一个不同的问题，因为根据它的文档， @Temporal 仅适用于 java.util.Date 和 java.util.Calendar。 - Tobias Liefke

Answer 2

您无需检查参数是否为空，因为anything = null返回false。您只需要进行比较：e.date = :date or e.date = DATE('2000-01-01')。 第一个比较将在传入null时返回false，并默认为常量比较。

- eduyayo

---

您的解决方案触发了错误“operator does not exist: date = bytea”。 我提出问题的整个重点是：“当我的参数为NULL时，如何避免进行此比较，因为它不起作用”。 - Neumann

抱歉...您是否正在使用spring-data jpa2？如果是，请在参数上注释@Nullable，以便它不会失败。顺便说一下...它也应该适用于您的第一种方法，因为它不会失败，而且:param is null将返回true。有关参考，请阅读此处https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.nullability.annotations - eduyayo

是的，我是，但这并不改变任何事情。至于我的第一种方法，正如我在原帖中所说，它失败了，因为 SQL 条件中没有短路。因此，即使 NULL 检查起作用，第二部分也总是会被评估。 - Neumann

Spring EL 允许您检查参数是否存在 ... #{#date ? 'e.date = :date' : 'e.date = date(\'2000-01-01\')' } ... - eduyayo

我猜你指的是例子“findBusinessObjectsForCurrentUser”。在这个例子中，SpEL表达式用于计算LIKE操作的第二个操作数。SpEL仅适用于LIKE或=操作的操作数。 - Neumann

Answer 3

你可以使用类型转换来解决日期的问题。根据这个https://github.com/spring-projects/spring-data-jpa/issues/2491。提供的解决方法在我使用的Hibernate版本5.3.28.Final中有效。对于排序和实际条件，同时进行类型转换可以正常工作。

AND (cast(cast(:searchDateStart as text) as timestamp) is null or date_column >= cast(cast(:searchDateStart as text) as timestamp))
AND (cast(cast(:searchDateEnd as text) as timestamp) is null or date_column <= cast(cast(:searchDateEnd as text) as timestamp))

- Benyovszki Balázs

Answer 4

你在where子句的第一个条件中使用了日期参数而不是实际的表字段： 这样应该可以：

@Query(value = "SELECT e FROM Event e WHERE e.date is null or DATE(e.date) = :date")
Page<Event> findEvents(Pageable pageable, @Param("date") Date date);

- Malay Shah

---

您的请求语义上有所不同。 数据库中的日期字段不能为空，但存储库函数参数“date”可以为空。这就是为什么我要在后者上测试空值的原因。这个请求与其他请求一样会崩溃。 - Neumann

我的错.. 我认为你应该寻找创建动态查询的方法。可以参考这个链接: https://stackoverflow.com/questions/58398910/how-to-cast-a-null-date-in-an-hibernate-nativequery - Malay Shah