如何使用JDBC将JSON数组插入/存储到数据库中?
Json数组是括在方括号内的值的有序集合,即,它以'['开始,以']'结尾。数组中的值以','(逗号)分隔。
样本JSON数组
{"books": [ Java, JavaFX, Hbase, Cassandra, WebGL, JOGL]
}
json-simple是一个轻量级的库,用于处理JSON对象。使用此程序,您可以使用Java程序读取或写入JSON文档的内容。
JSON-简单的Maven依赖
以下是JSON简单库的maven依赖关系-
<dependencies><dependency>
<groupId>com.googlecode.json-simple</groupId>
<artifactId>json-simple</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
将其粘贴在pom.xml文件末尾的<dependencies> </ dependencies>标记中。(在</ project>标记之前)
示例
让我们使用CREATE语句在MySQL数据库中创建一个名为MyPlayers的表,如下所示-
CREATE TABLE MyPlayers(ID INT,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Date_Of_Birth date,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255),
PRIMARY KEY (ID)
);
现在,我们将使用一个文档数组创建一个JSON文档(players_data.json),其中,数组中的每个文档都代表上面创建的MyPlayers表中的一条记录,如下所示:
{"players_data": [
{
"ID": "1",
"First_Name": "Shikhar",
"Last_Name": "Dhawan",
"Date_Of_Birth": "1981-12-05",
"Place_Of_Birth":"Delhi",
"Country": "India"
},
{
"ID": "2",
"First_Name": "Jonathan",
"Last_Name": "Trott",
"Date_Of_Birth": "1981-04-22",
"Place_Of_Birth":"CapeTown",
"Country": "SouthAfrica"
},
{
"ID": "3",
"First_Name": "Kumara",
"Last_Name": "Sangakkara",
"Date_Of_Birth": "1977-10-27",
"Place_Of_Birth":"Matale",
"Country": "Srilanka"
},
{
"ID": "4",
"First_Name": "Virat",
"Last_Name": "Kohli",
"Date_Of_Birth": "1988-11-05",
"Place_Of_Birth":"Mumbai",
"Country": "India"
},
{
"ID": "5",
"First_Name": "Rohit",
"Last_Name": "Sharma",
"Date_Of_Birth": "1987-04-30",
"Place_Of_Birth":"Nagpur",
"Country": "India"
},
{
"ID": "6",
"First_Name": "Ravindra",
"Last_Name": "Jadeja",
"Date_Of_Birth": "1988-12-06",
"Place_Of_Birth":"Nagpur",
"Country": "India"
},
{
"ID": "7",
"First_Name": "James",
"Last_Name": "Anderson",
"Date_Of_Birth": "1982-06-30",
"Place_Of_Birth":"Burnely",
"Country": "England"
},
{
"ID": "8",
"First_Name": "Ryan",
"Last_Name": "McLaren",
"Date_Of_Birth": "1983-02-09",
"Place_Of_Birth":"South Africa",
"Country": "India"
}
]
}
使用JDBC将JSON数组的内容写入数据库-
获取与数据库的连接
使用DriverManager类的registerDriver()方法或
forName()
名为Class的类的方法注册所需数据库的Driver类。
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
通过将数据库的URL,数据库中用户的用户名和密码(字符串格式)作为参数传递给DriverManager类的getConnection()方法,从而创建连接对象。
Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");
读取JSON文件的内容
实例化json-简单库的JSONParser类。
JSONParser jsonParser = new JSONParser();
使用 parse()方法解析获得的对象的内容。
//解析JSON文件的内容JSONObject jsonObject = (JSONObject) jsonParser(new FileReader("E:/players_data.json"));
使用 get()方法将json数组检索到JSONArray对象中。
JSONArray jsonArray = (JSONArray) jsonObject.get("contact");
将读取的JSON内容插入MyPlayers表
创建一个PreparedStatement 对象,以将值插入表MyPLayers中。
PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )");
对于JSON数组中的每个记录,请使用get方法检索键值对,并使用这些方法将值设置为PreparedStatement的适当绑定变量
setXXX()
。
JSONObject record = (JSONObject) object;int id = Integer.parseInt((String) record.get("ID"));
String first_name = (String) record.get("First_Name");
pstmt.setInt(1, id);
pstmt.setString(2, first_name);
以下JDBC程序将player_data.json文件的内容插入MyPlayers表中。
示例
import java.io.FileNotFoundException;import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
public class JsonToDatabase {
public static Connection ConnectToDB() throws Exception {
//注册驱动程序
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//获得连接
String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
return con;
}
public static void main(String args[]) {
//创建一个JSONParser对象
JSONParser jsonParser = new JSONParser();
try {
//解析JSON文件的内容
JSONObject jsonObject = (JSONObject) jsonParser.parse(new FileReader("E:/players_data.json"));
//检索数组
JSONArray jsonArray = (JSONArray) jsonObject.get("players_data");
Connection con = ConnectToDB();
//在MyPlayers表中插入一行
PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )");
for(Object object : jsonArray) {
JSONObject record = (JSONObject) object;
int id = Integer.parseInt((String) record.get("ID"));
String first_name = (String) record.get("First_Name");
String last_name = (String) record.get("Last_Name");
String date = (String) record.get("Date_Of_Birth");
long date_of_birth = Date.valueOf(date).getTime();
String place_of_birth = (String) record.get("Place_Of_Birth");
String country = (String) record.get("Country");
pstmt.setInt(1, id);
pstmt.setString(2, first_name);
pstmt.setString(3, last_name);
pstmt.setDate(4, new Date(date_of_birth));
pstmt.setString(5, place_of_birth);
pstmt.setString(6, country);
pstmt.executeUpdate();
}
System.out.println("Records inserted.....");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} catch (Exception e) {
//TODO自动生成的捕获块
e.printStackTrace();
}
}
}
输出结果
Connection established......Records inserted......
如果您使用SELECT语句验证MyPlayers表的内容,则可以看到插入的记录为-
mysql> select * from MyPlayers;+----+------------+------------+---------------+----------------+-------------+
| ID | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country |
+----+------------+------------+---------------+----------------+-------------+
| 1 | Shikhar | Dhawan | 1981-12-05 | Delhi | India |
| 2 | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica |
| 3 | Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka |
| 4 | Virat | Kohli | 1988-11-05 | Mumbai | India |
| 5 | Rohit | Sharma | 1987-04-30 | Nagpur | India |
| 6 | Ravindra | Jadeja | 1988-12-06 | Nagpur | India |
| 7 | James | Anderson | 1982-06-30 | Burnely | England |
| 8 | Ryan | McLaren | 1983-02-09 | South Africa | India |
+----+------------+------------+---------------+----------------+-------------+
8 rows in set (0.00 sec
以上是 如何使用JDBC将JSON数组插入/存储到数据库中? 的全部内容, 来源链接: utcz.com/z/360354.html