如何通过JdbcTemplate绑定Java数组到PostgreSQL数组？

PostgreSQL表结构 #

CREATE TABLE IF NOT EXISTS icon (
    id int8 GENERATED ALWAYS AS IDENTITY,
    "key" varchar(100) NOT NULL,
    description text NULL,
    keywords text[] NULL,
);

需求是检索包含指定关键词列表的所有图标，固定参数的查询可以正常获取结果：

public List<Icon> getIconsByKeywords(String[] keywords) {
    ...

    jdbcTemplate.query("select * from icon where keywords @> ARRAY['k4','k5','k6']", rs -> {
        Icon icon = new Icon();
        icon.setKey(rs.getString("key"));
        icon.setDescription(rs.getString("description"));
    });

    ...
}

但运行时动态绑定Java字符串数组到PostgreSQL数组时遇到障碍，尝试过转为Object数组、以字符串形式传入ARRAY、直接传递String数组均无效。已知通过数据库连接创建Array对象可以实现绑定：

Array array = jdbcTemplate.getDataSource().getConnection().createArrayOf("text", keywords);

jdbcTemplate.query("select * from icon where keywords @> ?", rs -> {
    Icon icon = new CustomIcon();
    icon.setKey(rs.getString("key"));
    icon.setDescription(rs.getString("description"));
}, array);

需要一种无需直接操作数据库连接，仅通过JdbcTemplate实现关键词数组绑定的方法。

1. 使用NamedParameterJdbcTemplate #

Spring的NamedParameterJdbcTemplate支持直接绑定数组类型参数，底层会自动处理Java数组到PostgreSQL数组的转换，无需手动创建Array对象：

public List<Icon> getIconsByKeywords(String[] keywords) {
    String sql = "select * from icon where keywords @> :keywords";
    
    Map<String, Object> params = new HashMap<>();
    params.put("keywords", keywords);
    
    return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> {
        Icon icon = new Icon();
        icon.setKey(rs.getString("key"));
        icon.setDescription(rs.getString("description"));
        return icon;
    });
}

确保使用的PostgreSQL JDBC驱动版本在42.x及以上，这类版本对数组类型的自动转换支持更完善。

2. 使用ArraySqlParameterValue #

如果坚持使用原生JdbcTemplate，可以借助Spring提供的ArraySqlParameterValue封装数组参数，它会自动通过数据源连接创建对应的SQL数组对象，无需手动管理连接：

import org.springframework.jdbc.core.ArraySqlParameterValue;

public List<Icon> getIconsByKeywords(String[] keywords) {
    String sql = "select * from icon where keywords @> ?";
    
    return jdbcTemplate.query(sql, (rs, rowNum) -> {
        Icon icon = new Icon();
        icon.setKey(rs.getString("key"));
        icon.setDescription(rs.getString("description"));
        return icon;
    }, new ArraySqlParameterValue("text", keywords));
}

3. 字符串拼接生成数组语法（不推荐） #

可以通过拼接字符串生成PostgreSQL的ARRAY语法，但这种方式存在SQL注入风险，仅适用于关键词完全可信的场景：

// 仅作示例，禁止用于生产环境
public List<Icon> getIconsByKeywords(String[] keywords) {
    String keywordStr = String.join("','", keywords);
    String sql = String.format("select * from icon where keywords @> ARRAY['%s']", keywordStr);
    
    return jdbcTemplate.query(sql, (rs, rowNum) -> {
        Icon icon = new Icon();
        icon.setKey(rs.getString("key"));
        icon.setDescription(rs.getString("description"));
        return icon;
    });
}

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