如何通过JDBC与PostgreSQL持久化非标准数组及复合类型数组？

一、处理PGInterval三维数组（对应interval[][][]） #

PostgreSQL JDBC驱动不支持直接通过setObject()传入PGInterval[][][]，可以用以下两种方式解决：

方式1：手动拼接SQL数组格式字符串 #

将三维数组逐层转换为PostgreSQL可识别的interval数组语法，再通过setObject()传入并指定类型：

PGInterval[][][] pgIntervals = // 你的三维PGInterval数组
StringBuilder sb = new StringBuilder("{{{");
for (PGInterval[][] layer1 : pgIntervals) {
    sb.append("{");
    for (PGInterval[] layer2 : layer1) {
        sb.append("{");
        for (int i = 0; i < layer2.length; i++) {
            PGInterval interval = layer2[i];
            sb.append("interval '")
              .append(interval.getYears()).append(" years ")
              .append(interval.getMonths()).append(" months ")
              .append(interval.getDays()).append(" days ")
              .append(interval.getHours()).append(" hours ")
              .append(interval.getMinutes()).append(" minutes ")
              .append(interval.getSeconds()).append(" seconds'");
            if (i != layer2.length - 1) sb.append(", ");
        }
        sb.append("}");
        if (layer2 != layer1[layer1.length - 1]) sb.append(", ");
    }
    sb.append("}");
    if (layer1 != pgIntervals[pgIntervals.length - 1]) sb.append(", ");
}
sb.append("}}}");

String sql = "INSERT INTO your_table (interval_col) VALUES (?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setObject(1, sb.toString(), Types.ARRAY);
    pstmt.executeUpdate();
}

方式2：通过PGConnection逐层构建数组 #

获取底层PGConnection，从最内层开始逐层创建数组：

PGInterval[][][] pgIntervals = // 你的三维PGInterval数组
PGConnection pgConn = conn.unwrap(PGConnection.class);

// 构建最内层一维interval数组
Array[][] innerArrays = new Array[pgIntervals.length][pgIntervals[0].length];
for (int i = 0; i < pgIntervals.length; i++) {
    for (int j = 0; j < pgIntervals[i].length; j++) {
        Object[] intervalStrs = Arrays.stream(pgIntervals[i][j])
            .map(PGInterval::getValue)
            .toArray();
        innerArrays[i][j] = pgConn.createArrayOf("interval", intervalStrs);
    }
}

// 构建二维数组
Array[] middleArrays = Arrays.stream(innerArrays)
    .map(arr -> {
        try {
            return pgConn.createArrayOf("interval[]", arr);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    })
    .toArray(Array[]::new);

// 构建最终三维数组
Array finalArray = pgConn.createArrayOf("interval[][]", middleArrays);

String sql = "INSERT INTO your_table (interval_col) VALUES (?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setArray(1, finalArray);
    pstmt.executeUpdate();
}

二、处理OffsetDateTime二维数组（对应timestamptz[][]） #

OffsetDateTime是JDBC 4.2标准支持的类型，可通过以下方式处理多维数组：

方式1：用PGConnection逐层构建数组 #

OffsetDateTime[][] dateTimes = // 你的二维OffsetDateTime数组
PGConnection pgConn = conn.unwrap(PGConnection.class);

// 构建内层一维数组
Array[] innerArrays = Arrays.stream(dateTimes)
    .map(row -> {
        try {
            return pgConn.createArrayOf("timestamptz", row);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    })
    .toArray(Array[]::new);

// 构建最终二维数组
Array finalArray = pgConn.createArrayOf("timestamptz[]", innerArrays);

String sql = "INSERT INTO your_table (timestamptz_col) VALUES (?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setArray(1, finalArray);
    pstmt.executeUpdate();
}

方式2：转换为字符串数组后强制类型转换 #

将OffsetDateTime转为ISO格式字符串，再通过SQL强制转换为目标类型：

OffsetDateTime[][] dateTimes = // 你的二维OffsetDateTime数组
String[][] dateStrs = Arrays.stream(dateTimes)
    .map(row -> Arrays.stream(row)
        .map(OffsetDateTime::toString)
        .toArray(String[]::new))
    .toArray(String[][]::new);

String sql = "INSERT INTO your_table (timestamptz_col) VALUES (cast(? as timestamptz[][]))";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setObject(1, dateStrs);
    pstmt.executeUpdate();
}

三、处理复合类型的四维数组（Test[][][][]对应test[][][][]） #

需要先实现Java对象与PostgreSQL复合类型的映射，再逐层构建数组：

步骤1：让POJO实现SQLData接口 #

Test类必须实现SQLData，完成与数据库复合类型的读写转换：

class Test implements SQLData {
    Integer a;
    Integer b;
    private String sqlType;

    // 构造器、getter/setter省略

    @Override
    public String getSQLTypeName() throws SQLException {
        return "test";
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.sqlType = typeName;
        this.a = stream.readInt();
        this.b = stream.readInt();
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeInt(a != null ? a : 0); // 根据业务需求处理null值
        stream.writeInt(b != null ? b : 0);
    }
}

步骤2：注册复合类型映射 #

在连接中注册自定义复合类型与Java类的关联：

PGConnection pgConn = conn.unwrap(PGConnection.class);
pgConn.addDataType("test", Test.class);

步骤3：逐层构建四维复合数组 #

从最内层开始，用createArrayOf逐层创建多维数组：

Test[][][][] testArrays = // 你的四维Test数组
PGConnection pgConn = conn.unwrap(PGConnection.class);

// 构建最内层一维test数组
Array[][][] inner3D = new Array[testArrays.length][testArrays[0].length][testArrays[0][0].length];
for (int i = 0; i < testArrays.length; i++) {
    for (int j = 0; j < testArrays[i].length; j++) {
        for (int k = 0; k < testArrays[i][j].length; k++) {
            inner3D[i][j][k] = pgConn.createArrayOf("test", testArrays[i][j][k]);
        }
    }
}

// 构建二维数组
Array[][] middle2D = Arrays.stream(inner3D)
    .map(layer -> {
        try {
            return pgConn.createArrayOf("test[]", layer);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    })
    .toArray(Array[][]::new);

// 构建三维数组
Array[] middle1D = Arrays.stream(middle2D)
    .map(layer -> {
        try {
            return pgConn.createArrayOf("test[][]", layer);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    })
    .toArray(Array[]::new);

// 构建最终四维数组
Array finalArray = pgConn.createArrayOf("test[][][]", middle1D);

String sql = "INSERT INTO your_table (test_col) VALUES (?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setArray(1, finalArray);
    pstmt.executeUpdate();
}

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