JPA CriteriaBuilder实现jsonb字段ilike模糊查询方法

你之前的无效写法存在两个明确问题：

• 错误将jsonb类型的pathVar字段本身传入criteriaBuilder.lower()，相当于尝试对整个JSONB结构做小写转换，数据库执行时会出现类型错误，正确做法是对jsonb_extract_path_text返回的字符串结果做小写转换
• 末尾调用的.toLowerCase()是Java String类的原生方法，作用在JPA的Predicate对象上不会生成任何SQL逻辑，属于API误用。

标准JPA兼容写法（无额外依赖） #

以下代码生成的SQL和你需要的ilike逻辑完全等价，不需要自定义方言，所有JPA实现均可正常运行：

// 提取jsonb字段中actor属性的值，和你等值查询的逻辑完全一致
Expression<String> extractedActor = criteriaBuilder.function(
    "jsonb_extract_path_text",
    String.class,
    pathVar,
    criteriaBuilder.literal("actor")
);

// 对提取出的值转小写后做like匹配，参数侧统一用小写即可实现大小写不敏感的模糊查询
Predicate p = criteriaBuilder.like(
    criteriaBuilder.lower(extractedActor),
    "%tom%"
);

该写法生成的SQL为select * from users where lower(jsonb_extract_path_text(properties, 'actor')) like ?，参数绑定为%tom%，在PostgreSQL默认排序规则下和原生->>'actor' ilike '%tom%'效果、执行性能完全一致。

Hibernate+PostgreSQL 简化写法 #

如果项目使用Hibernate作为JPA实现，可以直接用Hibernate扩展的大小写不敏感like重载，直接生成原生ilike语法：

Expression<String> extractedActor = criteriaBuilder.function(
    "jsonb_extract_path_text",
    String.class,
    pathVar,
    criteriaBuilder.literal("actor")
);
// 第三个参数指定匹配模式为任意位置包含，会自动拼接前后%，等价于'%tom%'
Predicate p = criteriaBuilder.like(extractedActor, "tom", MatchMode.ANYWHERE);

注意该写法是Hibernate扩展API，标准JPA接口不支持。

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