如何用jOOQ生成AWS Athena/Presto的contains数组比较查询

你需要生成的目标SQL为：

select item_id, name, desc from item_table where contains(array[24805247, 2156654], item_id);

以下是两种可行的实现方案：

方案1：正确使用Plain SQL模板绑定数组参数 #

之前直接传入Integer[]时，jOOQ会将数组作为可变参数展开，导致仅绑定第一个元素。解决方法是用DSL.val()将整个数组包装为单个参数：

Integer[] targetItemIds = {24805247, 2156654};

ResultQuery<Record> query = dsl.select(ITEM_TABLE.ITEM_ID, ITEM_TABLE.NAME, ITEM_TABLE.DESC)
    .from(ITEM_TABLE)
    .where("contains(array[{0}], {1})", DSL.val(targetItemIds), ITEM_TABLE.ITEM_ID);

DSL.val(targetItemIds)会让jOOQ正确识别这是一个数组类型参数，生成符合预期的SQL语句。

方案2：自定义类型安全的contains函数 #

为了避免重复编写Plain SQL模板，可以封装一个自定义函数，提升代码的可读性和类型安全性：

// 自定义适配Presto/Athena的contains函数
public static Condition contains(Field<Object[]> arrayField, Field<?> elementField) {
    return DSL.condition("contains({0}, {1})", arrayField, elementField);
}

// 使用示例
Integer[] targetItemIds = {24805247, 2156654};
Field<Integer[]> arrayField = DSL.val(targetItemIds, SQLDataType.INTEGER.getArrayDataType());

ResultQuery<Record> query = dsl.select(ITEM_TABLE.ITEM_ID, ITEM_TABLE.NAME, ITEM_TABLE.DESC)
    .from(ITEM_TABLE)
    .where(contains(arrayField, ITEM_TABLE.ITEM_ID));

这里通过SQLDataType.INTEGER.getArrayDataType()明确指定数组的类型，确保jOOQ生成正确的数组语法。

注意：eq(any(...))会生成多个OR拼接的条件，而上述方案生成的contains(array[...], item_id)更符合你的需求，在处理30-50个ID时，既能保持SQL语句简洁，也可能获得更优的执行计划。

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