带有JPA / Hibernate和PostgreSQL 的Spring(Boot) - 使用重叠作为日期

Question

所以我有一个项目，我们使用springBoot和PostgreSQL 10与PostGis和hibernate.space进行空间查询。到目前为止一切正常。

新的要求是查找实体，这些实体的开始-结束日期以任何可能的方式与查询的开始-结束日期重叠(范围可能是封闭、开始重叠、中间、结束重叠(。

在PostgreSQL中，有重叠运算符，它似乎非常适合这项工作。

当尝试在我的 JPA 查询中使用它来查找像这样的实体"Sth"时。

select sth from Sth sth where 1=1 and (sth.start, sth.end) overlaps (:begin, :end)
// set begin and end params..

我得到一个..

antlr.NoViableAltException: unexpected token: overlaps
antlr.NoViableAltException: unexpected AST node: (
org.postgresql.util.PSQLException: FEHLER: rt_raster_from_wkb: wkb size (5)  < min size (61)

是否可以在不编写本机查询的情况下对具有 JPA 的日期使用重叠？

Answer 1

因此，似乎您需要做三件事才能使其正常工作。

1. 它可能不可能使用重叠作为运算符，但幸运的是，它似乎也可以用作函数：overlaps(start1, end1, start2, end2)

2. 重叠不是由任何休眠核心PostgreSQL[NN]方言映射的。但它是由冬眠空间PostgisPG[NN]方言映射的，它映射到空间重叠的st_overlaps函数。因此，您需要使用自己的自定义方言，该方言使用别名注册重叠函数，如下所示：

public class PostgisDialect extends org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect {
public PostgisDialect() {
super();
registerFunction("dateoverlaps", new StandardSQLFunction("overlaps", StandardBasicTypes.BOOLEAN));
}
}

并将其指定为spring.jpa.properties.hibernate.dialect(或spring.jpa.database-platform🤷 ♂️(。

3. 您的 JPA 查询必须包含如下= true：

select sth from Sth sth where 1=1 and dateoverlaps(sth.start, sth.end, :begin, :end) = true

您可以通过使用coalesce函数来处理空值来增强这一点，即

select sth from Sth sth where 1=1 and dateoverlaps(coalesce(sth.start, sth.plannedStart), coalesce(sth.end, '3999-01-01'), :begin, :end) = true

当开始为空时，它使用plannedStart，当结束为空/打开时，它使用未来长日期。