如何在Jooq中执行大小写不敏感的IN查询？

在Java中使用jOOQ框架时，需要对某列执行大小写不敏感的查询，同时匹配给定List<String>中的所有结果。此前尝试过equalIgnoreCase方法，但担心该方式会生成冗余SQL，希望找到更高效的实现方案，避免给数据库带来不必要的负载。当前代码中的inIgnoreCase是核心问题点：

Integer warehouseId = 123;
ArrayList<String> productNames = new ArrayList<String>();

productNames.add("Lamp");
productNames.add("Laptop");

return getDslContext(instanceId)
                .select(TABLE_A.fields()) // 选择TABLE_A的所有字段
                .from(TABLE_A)
                .leftJoin(TABLE_B) // 关联TABLE_B
                .on(TABLE_A.WAREHOUSE_ID.eq(TABLE_B.WAREHOUSE_ID)
                        .and(TABLE_A.SERIAL_NUMBER.eq(TABLE_B.SERIAL_NUMBER))
                        .and(TABLE_B.DELETE_DATE.isNull())) // 关联条件：外键匹配且TABLE_B记录未删除

                .where(TABLE_A.PRODUCT_NAME.inIgnoreCase(productNames) // <-- 问题所在
                .or(TABLE_B.PRODUCT_NAME.inIgnoreCase(productNames))) 

                .and(TABLE_A.WAREHOUSE_ID.eq(warehouseId))
                .and(TABLE_A.DELETE_DATE.isNull()) // 确保TABLE_A记录未删除
                .fetchInto(TableARecord.class); // 转换结果为TableARecord

方案1：统一大小写+普通IN子句（推荐） #

将查询列和目标值统一转换为大写（或小写），使用标准IN子句替代inIgnoreCase，生成的SQL更简洁，数据库优化器处理效率更高，同时保证大小写不敏感匹配。

修改后代码示例 #

Integer warehouseId = 123;
List<String> productNames = Arrays.asList("Lamp", "Laptop");
// 将目标值统一转换为大写（与SQL侧的转换函数保持一致）
List<String> upperCaseNames = productNames.stream()
                                         .map(String::toUpperCase)
                                         .collect(Collectors.toList());

return getDslContext(instanceId)
                .select(TABLE_A.fields())
                .from(TABLE_A)
                .leftJoin(TABLE_B)
                .on(TABLE_A.WAREHOUSE_ID.eq(TABLE_B.WAREHOUSE_ID)
                        .and(TABLE_A.SERIAL_NUMBER.eq(TABLE_B.SERIAL_NUMBER))
                        .and(TABLE_B.DELETE_DATE.isNull()))
                .where(
                    // 对列调用UPPER函数，与目标值的大写形式匹配
                    upper(TABLE_A.PRODUCT_NAME).in(upperCaseNames)
                    .or(upper(TABLE_B.PRODUCT_NAME).in(upperCaseNames))
                )
                .and(TABLE_A.WAREHOUSE_ID.eq(warehouseId))
                .and(TABLE_A.DELETE_DATE.isNull())
                .fetchInto(TableARecord.class);

说明 #

• 主流数据库（MySQL、PostgreSQL、Oracle等）均支持UPPER()/LOWER()函数，兼容性强；
• 若列上存在普通索引，直接使用函数会导致索引失效，可提前创建函数索引（如CREATE INDEX idx_product_name_upper ON table_a(UPPER(product_name))），让查询命中索引。

方案2：利用数据库大小写不敏感特性 #

如果数据库表/列使用了大小写不敏感的排序规则（比如MySQL的utf8_general_ci、SQL Server的CI类排序规则），直接使用普通IN子句就能实现大小写不敏感匹配，无需额外转换操作，性能最优。

修改后代码示例 #

只需将inIgnoreCase替换为普通in：

.where(TABLE_A.PRODUCT_NAME.in(productNames)
       .or(TABLE_B.PRODUCT_NAME.in(productNames)))

说明 #

• 完全依赖数据库排序规则配置，代码无额外开销；
• 能直接利用列上的普通索引，查询性能达到最优。

方案3：批量构建equalIgnoreCase条件（备选） #

如果上述方案无法适用，可手动批量构建equalIgnoreCase条件，jOOQ会自动合并为一条SQL中的OR条件（不会生成多次查询，之前的担心多余，inIgnoreCase本身也只会生成单条SQL）。

修改后代码示例 #

Condition aCondition = productNames.stream()
                                  .map(name -> TABLE_A.PRODUCT_NAME.equalIgnoreCase(name))
                                  .reduce(Condition::or)
                                  .orElse(noCondition());

Condition bCondition = productNames.stream()
                                  .map(name -> TABLE_B.PRODUCT_NAME.equalIgnoreCase(name))
                                  .reduce(Condition::or)
                                  .orElse(noCondition());

return getDslContext(instanceId)
                .select(TABLE_A.fields())
                .from(TABLE_A)
                .leftJoin(TABLE_B)
                .on(TABLE_A.WAREHOUSE_ID.eq(TABLE_B.WAREHOUSE_ID)
                        .and(TABLE_A.SERIAL_NUMBER.eq(TABLE_B.SERIAL_NUMBER))
                        .and(TABLE_B.DELETE_DATE.isNull()))
                .where(aCondition.or(bCondition))
                .and(TABLE_A.WAREHOUSE_ID.eq(warehouseId))
                .and(TABLE_A.DELETE_DATE.isNull())
                .fetchInto(TableARecord.class);

说明 #

• 生成的SQL与inIgnoreCase本质相同，但手动构建条件更灵活；
• 当目标值列表过大时，OR拼接的SQL可能触发数据库查询条件数量限制，此时方案1的IN子句更可靠。

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