如何在JPA CriteriaBuilder中组合使用JSON_VALUE与TO_DATE函数

步骤1：转换前端传入的Instant到数据库兼容的日期类型 #

前端传入的Instant是UTC时间，需转换为Oracle能处理的日期类型，注意时区要匹配数据库中entry_date的时区：

// 前端传入的Instant
Instant inputInstant = ...;

// 转换为Timestamp（适合数据库存储UTC时间的场景）
Timestamp targetTimestamp = Timestamp.from(inputInstant);

// 若数据库entry_date是本地时区，转成对应时区的LocalDateTime再转Date
LocalDateTime targetLocalDateTime = LocalDateTime.ofInstant(inputInstant, ZoneId.of("Asia/Shanghai"));
Date targetDate = Date.from(targetLocalDateTime.atZone(ZoneId.of("Asia/Shanghai")).toInstant());

步骤2：构建JSON_VALUE表达式获取字符串日期 #

通过JSON_VALUE从CLOB类型的JSON字段中提取entry_date字符串：

// 假设Root<MYTABLE> root = query.from(MYTABLE.class);
CriteriaBuilder cb = entityManager.getCriteriaBuilder();

Expression<String> jsonEntryDateStr = cb.function(
    "JSON_VALUE",
    String.class,
    root.get("myclob_column"), // 目标CLOB列
    cb.literal("$.entry_date"), // JSON路径
    cb.literal("FORMAT JSON") // Oracle 12c+可选参数，确保JSON格式解析
);

步骤3：用TO_DATE将字符串转成SQL日期类型 #

调用Oracle的TO_DATE函数，将提取到的字符串日期转换为SQLDate类型，必须指定与entry_date完全匹配的日期格式：

// 示例：若entry_date格式为"2024-05-20 14:30:00"，则格式串为"yyyy-MM-dd HH24:mi:ss"
Expression<Date> entryDate = cb.function(
    "TO_DATE",
    Date.class,
    jsonEntryDateStr,
    cb.literal("yyyy-MM-dd HH24:mi:ss") // 匹配entry_date的字符串格式
);

步骤4：构建日期筛选条件 #

将转换后的日期字段与前端传入的日期做比较（如大于等于、小于等于）：

// 示例：筛选entry_date >= 目标日期的记录
Predicate dateFilter = cb.greaterThanOrEqualTo(entryDate, targetTimestamp);

// 组合到查询中
CriteriaQuery<MYTABLE> query = cb.createQuery(MYTABLE.class);
query.select(root).where(dateFilter);

// 执行查询
List<MYTABLE> result = entityManager.createQuery(query).getResultList();

关键注意事项 #

• 日期格式匹配：TO_DATE的格式串必须与entry_date的字符串格式完全一致，否则会抛出Oracle日期转换异常。
• 时区一致性：确保前端Instant转换后的时区与数据库中entry_date的时区一致，避免时间偏差。
• Oracle版本要求：JSON_VALUE仅在Oracle 12c及以上版本支持，若使用低版本需考虑其他JSON解析方案。

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