java insert数组到postgres数据库

数组格式在数据库中并不是常用操作,比较常用的是字符串存储后,使用时再进行数据加工.这里记录下直接操作postgres insert数组的数据操作.

表结构

CREATE TABLE schema.table(
	report_rows json NULL,
	series _varchar NULL
)

实际存在两种数组结构:1 字符串数组 2 json数组. json数组使用json进行存储,也可以是jsonb二进制存储方式,但是二进制数据虽然更快,但在数据库中不直观,这里就使用了json.

方式1: 指定数组数据类型

    @Data
    public static class SelfData {
        public String[] series;
        public JsonNode report_rows;
    }

    public void insertArrayTopostgres() {
        String dataStr="{\"series\":[\"2024/06/18 00:00:00\"],\"rows\":[{\"sum\":[34517],\"values\":[[204],[132]],\"byValue\":[\"i am robot\"]}]}";
        SelfData selfData = JsonUtils.parseStr(dataStr, SelfData.class);

        String url2 = "jdbc:postgresql://localhost:5432/mydatabase";
        String user = "myuser";
        String password = "mypassword";

        try (Connection connection = DriverManager.getConnection(url2, user, password)) {
            // json存储要求是json字符串格式
            String jsonRows = objectMapper.writeValueAsString(selfData.getReport_rows());
            // 这里是数据库数组类型(java.sql.Array)的关键处理. "varchar"用于指定数组内的数据类型 selfData.getSeries()是实际的数据
            Array series = connection.createArrayOf("varchar", selfData.getSeries());
            String sql = "INSERT INTO schema.table (report_rows, series) VALUES(?, ?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setObject(1, jsonRows);
                preparedStatement.setArray(2, series);
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

数据库实际存储效果

方式2: sql中直接指定数据类型,不在代码中进行类型指定

     Object execute = jdbcTemplatePostgres.execute("INSERT INTO schema.table (report_rows, series) VALUES(?::json, ?::_varchar);", new PreparedStatementCallback<Object>() {
          @Override
          public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
              String[] split = "report_rows, series".split(",");
              for (int i = 0; i < split.length; i++) {
                  ps.setObject(i + 1, ReflectUtils.getFieldValue(data, split[i].trim()));
              }
              return ps.executeUpdate();
          }
      });

代码实现上很简单.sql语句才是精髓: INSERT INTO schema.table (report_rows, series) VALUES(?::json, ?::_varchar);