如何用Java Criteria API构建PostgreSQL tsrange重叠查询Predicate

需要创建等效于以下PostgreSQL 15原生SQL的Criteria API Predicate：

SELECT time_range_column FROM public.address_table
WHERE  time_range_column && '[2014-09-23, 2015-09-24]'::tsrange;

实体中字段声明如下：

@Type(PostgreSQLRangeType.class)
@Column(name = "time_range_column")
private Range<LocalDateTime> timeRangeColumn;

尝试的错误实现（无返回结果）：

String dateTimeString1 = "2014-09-15 00:00:00";
String dateTimeString2 = "2015-09-15 00:00:00";
String dateTimeFormat = "yyyy-MM-dd HH:mm:ss";

DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat);
LocalDateTime d1 = LocalDateTime.parse(dateTimeString1, formatter);
LocalDateTime d2 = LocalDateTime.parse(dateTimeString2, formatter);

String rangeLiteral = "[" + d1 + "," + d2 + ")";

Predicate dateRangePredicate = criteriaBuilder.and(criteriaBuilder.literal(rangeLiteral)
.in(root.<LocalDateTime>get("time_range_column")));

错误分析 #

1. 误用in操作符：in用于检查值是否在集合中，和PostgreSQL的&&范围重叠逻辑完全不符
2. 类型错误：将Range<LocalDateTime>类型字段当作LocalDateTime处理，导致类型不匹配
3. 手动拼接字符串：容易引发日期格式错误，且无法利用Hibernate对PostgreSQL Range类型的自动转换

正确实现 #

步骤1：构造目标Range对象

直接使用RangeAPI创建符合需求的范围对象，避免字符串拼接：

String dateTimeString1 = "2014-09-15 00:00:00";
String dateTimeString2 = "2015-09-15 00:00:00";
String dateTimeFormat = "yyyy-MM-dd HH:mm:ss";

DateTimeFormatter formatter = DateTimeFormatter.ofPattern(dateTimeFormat);
LocalDateTime d1 = LocalDateTime.parse(dateTimeString1, formatter);
LocalDateTime d2 = LocalDateTime.parse(dateTimeString2, formatter);

// 构造左闭右开的Range对象，对应SQL中的[start, end)格式
Range<LocalDateTime> targetRange = Range.closedOpen(d1, d2);

步骤2：创建重叠查询Predicate

利用CriteriaBuilder.function直接调用PostgreSQL的&&运算符，实现和原生SQL一致的逻辑：

// 获取实体中Range类型的字段路径
Path<Range<LocalDateTime>> timeRangePath = root.get("timeRangeColumn");

// 调用PostgreSQL的&&运算符判断范围重叠
Predicate dateRangePredicate = criteriaBuilder.isTrue(
    criteriaBuilder.function(
        "&&",          // PostgreSQL范围重叠运算符
        Boolean.class, // 返回值类型
        timeRangePath, // 数据库表中的range字段
        criteriaBuilder.literal(targetRange) // 目标查询范围
    )
);

替代方案（Hibernate 6+）

如果使用Hibernate 6及以上版本，可以使用更简洁的RangePredicate：

Predicate dateRangePredicate = new RangePredicate(
    criteriaBuilder,
    timeRangePath,
    RangeOperator.OVERLAPS,
    targetRange
);

内容的提问来源于stack exchange，提问作者Gabriela SO