如何使用JDBC在Java中向PostgreSQL插入自定义类型数组

步骤1：改造自定义Java类实现SQLData接口 #

这是JDBC自定义类型映射的强制要求，实现后JDBC可以自动完成Java对象和Postgres复合类型的转换：

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class ElementPK implements SQLData {
    public Long workspaceId;
    public Long elementId;
    public Long historyId;

    // 必须提供无参构造方法，供JDBC反射实例化
    public ElementPK() {}

    public ElementPK(Long workspaceId, Long elementId, Long historyId) {
        this.workspaceId = workspaceId;
        this.elementId = elementId;
        this.historyId = historyId;
    }

    // 返回Postgres中定义的自定义类型名称，必须和schema定义完全一致
    @Override
    public String getSQLTypeName() throws SQLException {
        return "element_pk_t";
    }

    // 从数据库读取类型的反序列化逻辑，字段顺序必须和PG类型定义顺序一致
    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.workspaceId = stream.readLong();
        this.elementId = stream.readLong();
        this.historyId = stream.readLong();
    }

    // 写入数据库的序列化逻辑，字段顺序必须和PG类型定义顺序一致
    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeLong(workspaceId);
        stream.writeLong(elementId);
        stream.writeLong(historyId);
    }
}

步骤2：使用PreparedStatement插入数组 #

以下为完整实现代码，假设你的业务表存在一个类型为element_pk_t[]的列pk_list：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Array;
import java.sql.DriverManager;
import java.util.Map;

public class PgCustomArrayInsert {
    public static void main(String[] args) throws Exception {
        // 1. 获取数据库连接
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://你的数据库地址:端口/库名", "用户名", "密码")) {
            // 2. 注册自定义类型映射
            Map<String, Class<?>> typeMap = conn.getTypeMap();
            typeMap.put("element_pk_t", ElementPK.class);
            conn.setTypeMap(typeMap);

            // 3. 构造要插入的ElementPK对象数组
            ElementPK[] pkArray = new ElementPK[]{
                new ElementPK(1L, 1001L, 2001L),
                new ElementPK(2L, 1002L, 2002L)
            };

            // 4. 生成JDBC Array对象，第一个参数为PG的自定义类型名称
            Array sqlArray = conn.createArrayOf("element_pk_t", pkArray);

            // 5. 执行插入
            String insertSql = "INSERT INTO 你的业务表名(pk_list) VALUES (?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                ps.setArray(1, sqlArray);
                ps.executeUpdate();
            } finally {
                // 释放Array资源
                sqlArray.free();
            }
        }
    }
}

注意事项 #

• 复合类型的读写顺序必须和Postgres中CREATE TYPE定义的字段顺序完全一致，否则会出现字段映射错位
• 如果通过连接池获取连接，需要先将连接unwrap为PG原生连接再设置类型映射，避免连接池代理类不支持类型映射配置，示例：

  // 连接池场景适配
  if (conn.isWrapperFor(org.postgresql.PGConnection.class)) {
      org.postgresql.PGConnection pgConn = conn.unwrap(org.postgresql.PGConnection.class);
      Map<String, Class<?>> typeMap = pgConn.getTypeMap();
      typeMap.put("element_pk_t", ElementPK.class);
      pgConn.setTypeMap(typeMap);
  }
  

• 建议使用42.2.0及以上版本的PostgreSQL JDBC驱动，旧版本对复合类型数组的支持存在已知缺陷。

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