Postgres逗号分隔字段匹配字符串列表的SQL及JPA Criteria实现方法

一、Postgres原生SQL实现 #

你的匹配规则本质是：mycolumn字段拆分后的所有元素，都是传入的匹配列表的子集，Postgres可以通过string_to_array函数拆分逗号分隔字符串，再用数组子集运算符<@完成校验，示例如下：

-- 匹配列表为['A']的场景
SELECT id, mycolumn 
FROM 你的表名
WHERE string_to_array(mycolumn, ', ') <@ ARRAY['A'];

-- 匹配列表为['A','B']的场景
SELECT id, mycolumn 
FROM 你的表名
WHERE string_to_array(mycolumn, ', ') <@ ARRAY['A','B'];

-- 匹配列表为['A','B','C']的场景
SELECT id, mycolumn 
FROM 你的表名
WHERE string_to_array(mycolumn, ', ') <@ ARRAY['A','B','C'];

说明：Postgres中<@运算符含义为「左数组的所有元素都包含在右数组中」，和内置函数arraycontains(左数组, 右数组)效果完全一致。

二、JPA Criteria实现 #

你原有代码存在两个可修正的问题：

1. string_to_array函数返回的是字符串数组，之前指定返回类型为String.class是错误的，需要改为String[].class
2. arraycontains的两个参数顺序写反了，按照需求应该是判断「数据库字段拆分的数组」是「传入匹配列表拆分的数组」的子集，所以数据库数组要放在第一个参数位置。

修正后的String参数版本（适配你原有实现） #

public static void arrayContains(final CriteriaBuilder cb, final List<Predicate> where,
                                 final Expression<String> expression, final String values) {
    // 把传入的匹配字符串转成数组，分隔符和数据库存储的分隔符保持一致为", "
    Expression<String[]> inputArray = cb.function("string_to_array", String[].class, 
            cb.literal(values), cb.literal(", "));
    // 把数据库字段转成数组
    Expression<String[]> dbColumnArray = cb.function("string_to_array", String[].class, 
            expression, cb.literal(", "));
    // 判断数据库数组是输入数组的子集，参数顺序：db数组在前，输入数组在后
    where.add(cb.isTrue(cb.function("arraycontains", Boolean.class, dbColumnArray, inputArray)));
}

更安全的List参数版本（避免字符串拼接特殊字符问题） #

如果你的入参是List类型的匹配列表，不需要自己拼接成字符串，可以直接传入数组参数，避免分隔符冲突：

public static void arrayContains(final CriteriaBuilder cb, final List<Predicate> where,
                                 final Expression<String> expression, final List<String> matchList) {
    // 把数据库字段转成数组
    Expression<String[]> dbColumnArray = cb.function("string_to_array", String[].class,
            expression, cb.literal(", "));
    // 直接把List转成数组字面量传入
    Expression<String[]> inputArray = cb.literal(matchList.toArray(new String[0]));
    where.add(cb.isTrue(cb.function("arraycontains", Boolean.class, dbColumnArray, inputArray)));
}

内容的提问来源于stack exchange，提问作者Ann-Christin Lindras