如何在SpringBoot中使用JdbcClient插入PostgreSQL复合类型数组

你遇到的问题本质上是Spring JdbcClient默认不认识自定义的PostgreSQL复合类型数组，直接传递List<ShopItem>的话，JDBC驱动无法自动完成Java对象到PostgreSQL复合类型数组的转换，所以会抛出类型转换错误。下面给你两种可行的解决方案，都是通过手动处理类型映射来解决这个问题：

方案一：使用JDBC Array对象（推荐，更安全） #

这种方式不需要拼接字符串，直接通过JDBC的Array对象来构造PostgreSQL能识别的复合类型数组，避免了SQL注入和字符串格式错误的风险。

你只需要修改DAO方法中的参数传递逻辑，把List<ShopItem>转换成JDBCArray对象：

@Override
public void upsert(long guildId, @NotNull ShopOrder shopOrder) {
    jdbc.sql("""
            INSERT INTO shop_orders (guild_id, user_id, shop_items)
            VALUES (?, ?, ?)
            ON CONFLICT (guild_id, user_id)
            DO UPDATE SET shop_items = EXCLUDED.shop_items;
            """)
        .param(1, guildId)
        .param(2, shopOrder.getUserId())
        // 自定义参数类型转换
        .param(3, new AbstractSqlTypeValue() {
            @Override
            protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
                // 将每个ShopItem转换为对应复合类型的字段数组（注意顺序要和PostgreSQL的shop_item类型完全一致！）
                Object[] itemArrays = shopOrder.getShopItems().stream()
                        .map(item -> new Object[]{
                                item.getId(),
                                item.getQuantity(),
                                item.getQuantityMultiplier(),
                                item.getPrice()
                        })
                        .toArray();
                // 创建PostgreSQL复合类型数组，指定元素类型为"shop_item"
                return con.createArrayOf("shop_item", itemArrays);
            }
        })
        .update();
}

关键注意点： #

• 构造Object[]时，字段顺序必须和你定义的PostgreSQLshop_item类型完全匹配（即id→quantity→quantity_multiplier→price），否则会出现数据错位的问题。
• 这种方式依赖PostgreSQL JDBC驱动的支持，确保你的pom.xml中已经引入了正确版本的postgresql驱动依赖。

方案二：用PGobject包装复合类型数组字符串 #

如果你更倾向于手动构造PostgreSQL数组的字符串格式，可以用PGobject来包装这个字符串，并指定类型为shop_item[]。不过这种方式需要注意字符串转义的问题（比如id中包含单引号时要处理），否则会导致SQL语法错误。

首先写一个工具方法来转换List<ShopItem>为PGobject：

private PGobject convertToShopItemArray(List<ShopItem> shopItems) throws SQLException {
    if (shopItems.isEmpty()) {
        throw new IllegalArgumentException("Shop items cannot be empty");
    }
    StringBuilder arrayBuilder = new StringBuilder("{");
    for (int i = 0; i < shopItems.size(); i++) {
        ShopItem item = shopItems.get(i);
        // 注意：如果id包含单引号，需要转义为两个单引号，避免SQL语法错误
        String escapedId = item.getId().replace("'", "''");
        // 拼接PostgreSQL复合类型的单个元素格式
        String itemStr = String.format("('%s',%d,%d,%.2f)",
                escapedId,
                item.getQuantity(),
                item.getQuantityMultiplier(),
                item.getPrice());
        arrayBuilder.append(itemStr);
        if (i != shopItems.size() - 1) {
            arrayBuilder.append(",");
        }
    }
    arrayBuilder.append("}");
    // 包装为PGobject，指定类型为shop_item[]
    PGobject arrayObject = new PGobject();
    arrayObject.setType("shop_item[]");
    arrayObject.setValue(arrayBuilder.toString());
    return arrayObject;
}

然后修改DAO方法中的参数传递：

@Override
public void upsert(long guildId, @NotNull ShopOrder shopOrder) {
    try {
        jdbc.sql("""
                INSERT INTO shop_orders (guild_id, user_id, shop_items)
                VALUES (?, ?, ?)
                ON CONFLICT (guild_id, user_id)
                DO UPDATE SET shop_items = EXCLUDED.shop_items;
                """)
            .param(1, guildId)
            .param(2, shopOrder.getUserId())
            .param(3, convertToShopItemArray(shopOrder.getShopItems()))
            .update();
    } catch (SQLException e) {
        // 转换异常处理，包装为运行时异常抛出
        throw new RuntimeException("Failed to convert shop items to PostgreSQL array", e);
    }
}

关键注意点： #

• 必须处理字符串转义：如果ShopItem的id中包含单引号，一定要替换为两个单引号，否则会破坏数组字符串的格式，甚至引发SQL注入风险。
• 数值类型（quantity、price等）要确保格式符合PostgreSQL的要求，比如price用%.2f来保证是两位小数，和数据库的DECIMAL(4,2)对应。

为什么你原来的代码不行？ #

你之前尝试在SQL中加?::shop_item[]的强制转换，但问题出在JDBC传递的参数本身类型不兼容：JDBC把List<ShopItem>当成了普通的Java集合，无法直接在数据库层面转换为复合类型数组。必须先在Java端把参数转换成JDBC驱动能识别的类型（Array或PGobject），数据库才能正确处理。

备注：内容来源于stack exchange，提问作者Omar Diaa