Spring Boot集成Hibernate如何对JSON数组字段编写条件查询语句

你可以通过以下3种常用方式实现需求，按适用场景选择即可： #

方案1：直接执行原生SQL（适配所有Hibernate版本，最省事） #

你原本写的PostgreSQL语法是合法的，直接用Hibernate原生查询接口执行即可，示例代码如下：

// 带参数绑定的写法，避免SQL注入
String nativeSql = "SELECT id, elements ->> 'startTime' " +
                   "FROM work_shift, jsonb_array_elements(timings) elements " +
                   "WHERE elements ->> 'day' = :targetDay";
// 执行查询
List<Object[]> result = session.createNativeQuery(nativeSql)
        .setParameter("targetDay", "Monday")
        .getResultList();

如果需要映射成实体对象，可以加@SqlResultSetMapping注解配置映射规则。 #

方案2：Hibernate 6+ 版本用HQL实现 #

Hibernate 6开始内置了JSON/JSONB函数的HQL支持，首先要确保你的实体类中timings字段映射正确：

// 实体类字段映射示例
@Type(JsonBinaryType.class)
@Column(columnDefinition = "jsonb")
private List<WorkTime> timings;

对应的HQL写法如下：

SELECT ws.id, json_extract_text(elements, '$.startTime')
FROM WorkShift ws, json_array_elements(ws.timings) elements
WHERE json_extract_text(elements, '$.day') = :targetDay

方案3：Hibernate 5 版本自定义函数实现 #

如果使用Hibernate 5，需要先在自定义PostgreSQL方言中注册你用到的JSON函数，之后就可以在HQL中直接调用：

public class CustomPostgreSQLDialect extends PostgreSQL10Dialect {
    public CustomPostgreSQLDialect() {
        super();
        // 注册jsonb_array_elements函数
        registerFunction("jsonb_array_elements", 
            new StandardSQLFunction("jsonb_array_elements", StandardBasicTypes.OBJECT));
        // 注册->>操作符对应的函数
        registerFunction("jsonb_extract_text", 
            new SQLFunctionTemplate(StandardBasicTypes.STRING, "?1 ->> ?2"));
    }
}

配置自定义方言后，HQL写法如下：

SELECT ws.id, jsonb_extract_text(elements, 'startTime')
FROM WorkShift ws, jsonb_array_elements(ws.timings) elements
WHERE jsonb_extract_text(elements, 'day') = :targetDay

注意事项 #

• 所有场景都推荐用参数绑定的方式传入day的匹配值，不要直接拼接字符串到SQL中，避免SQL注入风险
• 如果只需要查询匹配条件的单条记录，可以加LIMIT 1减少查询开销

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