java - 在 Oracle 表中存储 JSON 数组

Question

我正在处理文件中的大量 JSON 数据，每个 JSON 对象都有一个像这样的数组

field28436": [{
            "id": "C28679",
            "value": "Dutch"
        }, {
            "id": "C28963",
            "value": "English"
        }, {
            "id": "C28966",
            "value": "French"
        }, {
            "id": "C28968",
            "value": "German"
        }]

我需要将其存储到 oracle 数据库中的单列中。请建议一种数据类型或将其存储在单列中的方法。我正在使用 JAVA 来解析 JSON。例如，如果我使用 HashMap 将此值解析为键值对，如何将其存储在单个列中？可能吗？

Answer 1

如果您 100% 确定长度永远不会超过 4000 字节，请使用 varchar2 列。否则使用 CLOB 或 BLOB 列。

在任何情况下，您都应该使用 CHECK 约束来“增强”该列，以验证该值是有效的 JSON，例如:

create table my_table
(
   id integer primary key, 
   data clob, 
   constraint validate_json CHECK (data IS JSON)
);

Oracle 建议使用 BLOB 列，以避免 CLOB 使用多字节字符集的开销。然而，这使得在 Java(或任何 SQL 客户端)中处理 JSON 变得更加复杂。

要存储这样的值，请使用 PreparedStatement 并使用 setString() 方法。当前的 Oracle 驱动程序不再需要 setClob() 来处理长字符串 - 至少对于 INSERT 或 UPDATE 语句来说不需要。

String jsonData = "field28436": [....]";
PreparedStatement pstmt = connection.prepareStatement(
      "insert into my_table (id, data) values (?, ?)");
pstmt.setInt(1, 42);
pstmt.setString(2, jsonData);
pstmt.executeUpdate();

要读取数据将使用类似的东西:

PreparedStatement pstmt = connection.prepareStatement("select data from my_table where id = ?");
pstmt.setInt(1, 42);
ResultSet rs = psmt.executeQuery();
if (rs.next()) {
  String jsonData = rs.getString(1);
}

有关更多信息，我建议阅读 JSON Developer's Guide