将 JSON 数据加载到 PostgreSQL 11 表中

Question

需要将以下JSON加载到PostgreSQL-11表中，并需要帮助：

[ { "id":"1", "name":"abc_xyz", "language":"English", "title":"facebook", "description":"This is a test", "categories":[ "https://facebook/category/28", "https://facebook/category/29", "https://facebook/category/30", "https://facebook/category/31" ] }, "id":"2", "name":"abc_xyz", "language":"French", "title":"Twitter", "description":"This is another test", "categories":[ "https://twitter/category/2", "https://twitter/category/23", "https://twitter/category/35" ] } ]

JSON 数据需要加载到两个表中： 表A列：

id int, 
name varchar,
language varchar, 
description varchar

表 B 列：

Association_Id serial,
TableA.Id int,
Category_Id int,
Last_Update_Time timestamp DEFAULT NOW()

表 A 将包含如下行：

id = 1,
name = abc_xyz,
language = English,
title = facebook,
description = This is a test

表 B 行：

Association_Id = 1
TableA_Id = 1
Category_Id  = 28
Association_Id = 2
TableA_Id = 1
Category_Id  = 29
Association_Id = 3
TableA_Id = 1
Category_Id  = 30

等等等等。

请帮忙...提前致谢

Answer 1

这里有一个小程序。它是用Java编写的，但在另一种语言中会以类似的方式工作。它是完全独立的。我试图让它变得简约，但它仍然可能是一个很好的起点。

它确实：

• 使用 GSON 库读取 JSON 文件
• 通过 JDBC 预准备语句将数据导入 Postgres 数据库

结果

使用psql命令行程序查询表A和表B返回：

stephan=# select * from TableA;                                                                                                
id |  title   |  name   | language |     description      
----+----------+---------+----------+----------------------
1 | facebook | abc_xyz | English  | This is a test
2 | Twitter  | abc_xyz | French   | This is another test
(2 rows)
stephan=# select * from TableB;
association_id | tablea_id | category_id |      last_update_time      
----------------+-----------+-------------+----------------------------
29 |         1 |          28 | 2019-06-13 18:04:52.671833
30 |         1 |          29 | 2019-06-13 18:04:52.671833
31 |         1 |          30 | 2019-06-13 18:04:52.671833
32 |         1 |          31 | 2019-06-13 18:04:52.671833
33 |         2 |           2 | 2019-06-13 18:04:52.692635
34 |         2 |          23 | 2019-06-13 18:04:52.692635
35 |         2 |          35 | 2019-06-13 18:04:52.692635
(7 rows)

爪哇岛

import java.util.Properties;
import java.sql.*;
import com.google.gson.Gson;
class Entry {
int id;
String name;
String language;
String title;
String description;
String[] categories;
}

public class Main {
public static void main(String[] args) {
String json = "[{"id":"1","name":"abc_xyz","language":"English","title":"facebook","description":"This is a test","categories":["https://facebook/category/28","https://facebook/category/29","https://facebook/category/30","https://facebook/category/31"]},{"id":"2","name":"abc_xyz","language":"French","title":"Twitter","description":"This is another test","categories":["https://twitter/category/2","https://twitter/category/23","https://twitter/category/35"]}]";
try {
Entry[] entries = readJSON(json);
importIntoDB(entries);
} catch (Exception e) {
e.printStackTrace();
}
}
private static Entry[] readJSON(String json) {
Entry[] entries;
Gson g = new Gson();
entries = g.fromJson(json, Entry[].class);
return entries;
}
private static Connection createConnection()
throws ClassNotFoundException, SQLException {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/stephan";
Properties props = new Properties();
props.setProperty("user", "stephan");
props.setProperty("password", "secret");
//props.setProperty("ssl", "true");
return DriverManager.getConnection(url, props);
}
private static void importIntoDB(Entry[] entries)
throws SQLException, ClassNotFoundException {
Connection connection = createConnection();
try (connection) {
connection.setAutoCommit(false);
PreparedStatement insertTableA = connection.prepareStatement(
"INSERT INTO TableA (id, name, language, title, description) VALUES(?, ?, ?, ?, ?)");
PreparedStatement insertTableB = connection.prepareStatement(
"INSERT INTO TableB (TableA_Id, Category_Id) VALUES (?, ?)");
for (Entry entry : entries) {
insertTableA.setInt(1, entry.id);
insertTableA.setString(2, entry.name);
insertTableA.setString(3, entry.language);
insertTableA.setString(4, entry.title);
insertTableA.setString(5, entry.description);
insertTableA.execute();
for (String category : entry.categories) {
insertTableB.setInt(1, entry.id);
String categoryIdString = category.substring(category.lastIndexOf('/') + 1);
int categoryId = Integer.parseInt(categoryIdString);
insertTableB.setInt(2, categoryId);
insertTableB.execute();
}
connection.commit();
}
insertTableA.close();
insertTableB.close();
}
}
}

所需库

上述程序需要 Postgres JDBC 库和 GSON 库进行 JSON 反序列化。

您可以从此处下载：

https://jdbc.postgresql.org/download.html

http://central.maven.org/maven2/com/google/code/gson/gson/2.8.5/gson-2.8.5.jar

批次

如果你必须导入大量的条目，你可以考虑将 PreparedStatements 组合成批处理，然后一次执行多个语句，看看方法addBatch和executeBatch。