java - 将 JSON 文件中的数据插入 MySql 表
问题描述
我正在尝试将数据插入 MySQL 数据库表。我已经创建了这样的表:
mysql> SHOW COLUMNS FROM sampledata;
+------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| Confidence | bigint(20) | YES | | NULL | |
| ConnectionId | bigint(20) | YES | | NULL | |
| Imei | bigint(20) | YES | | NULL | |
| Imsi | bigint(20) | YES | | NULL | |
| IsData | varchar(10) | YES | | NULL | |
| IsSignalling | varchar(10) | YES | | NULL | |
| IsVoice | varchar(10) | YES | | NULL | |
| Latitude | double(20,20) | YES | | NULL | |
| Longitude | double(20,20) | YES | | NULL | |
| Mcc | int(11) | YES | | NULL | |
| Mnc | int(11) | YES | | NULL | |
| SegmentDuration | time | YES | | NULL | |
| SegmentStartTime | datetime | YES | | NULL | |
| ServingCellLabel | varchar(20) | YES | | NULL | |
| Sv | int(11) | YES | | NULL | |
| TrackingAreaCode | int(11) | YES | | NULL | |
| Uncertainity | int(11) | YES | | NULL | |
+------------------+---------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
我试过下面的代码
package mysql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.tools.JavaFileObject;
import jdk.nashorn.internal.parser.JSONParser;
public class sampleData {
public int insertJSONtoDB() throws Exception {
int status = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "user", "user123");
PreparedStatement preparedStatement = con.prepareStatement("insert into sampledata values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
JSONParser parser = new JSONParser();
Object obj = parser.parse(new FileReader("/home/Desktop/PD/sampledata.json"));
JavaFileObject jsonObject = (JSONObject) obj;
String id = (String) jsonObject.get("Confidence"); // from JSON tag
preparedStatement.setString(1, Confidence); // to the Database table
String name = (String) itemize.get("ConnectionId");
preparedStatement.setString(2, ConnectionId);
status = preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (con != null) {
con.close();
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
return status;
}
}
但它不起作用。此外,当我尝试导入任何 SQL 或 JSON 包时,它会显示错误。我尝试在 Eclipse 中设置构建路径,但无济于事。谁能告诉我我做错了什么。下面是我的 JSON 文件的片段。
{
"Confidence": "1.994667E-07",
"Uncertainty": "178",
"IsData": "FALSE",
"Latitude": "1.694202",
"ConnectionId": "330708186825281",
"Mcc": "999",
"Sv": "01",
"Longitude": "0.434623",
"IsVoice": "FALSE",
"IsSignalling": "TRUE",
"SegmentStartTime": "16/02/2017 09:56:59.912",
"Imei": "99999006686069",
"SegmentDuration": "00:00:00.0350000",
"Mnc": "99",
"ServingCellLabel": "Cell18",
"Imsi": "999992223223602",
"TrackingAreaCode": "1234"
},
{
"Confidence": "1.504506E-12",
"Uncertainty": "314",
"IsData": "FALSE",
"Latitude": "1.633704",
"ConnectionId": "260339442647675",
"Mcc": "999",
"Sv": "02",
"Longitude": "0.668554",
"IsVoice": "FALSE",
"IsSignalling": "TRUE",
"SegmentStartTime": "16/02/2017 09:57:01.377",
"Imei": "99999207564306",
"SegmentDuration": "00:00:00.0280000",
"Mnc": "99",
"ServingCellLabel": "Cell19",
"Imsi": "999993793410366",
"TrackingAreaCode": "1235"
},
{
"Confidence": "0.3303348",
"Uncertainty": "129",
"IsData": "FALSE",
"Latitude": "1.847635",
"ConnectionId": "260339442647676",
"Mcc": "999",
"Sv": "14",
"Longitude": "1.356349",
"IsVoice": "FALSE",
"IsSignalling": "TRUE",
"SegmentStartTime": "16/02/2017 09:57:01.555",
"Imei": "99999605176135",
"SegmentDuration": "00:00:00.0290000",
"Mnc": "99",
"ServingCellLabel": "Cell13",
"Imsi": "999992216631694",
"TrackingAreaCode": "1236"
},
{
"Confidence": "0.01800376",
"Uncertainty": "463",
"IsData": "FALSE",
"Latitude": "1.914598",
"ConnectionId": "330708186825331",
"Mcc": "999",
"Sv": "74",
"Longitude": "1.222736",
"IsVoice": "FALSE",
"IsSignalling": "TRUE",
"SegmentStartTime": "16/02/2017 09:57:02.689",
"Imei": "99999007880884",
"SegmentDuration": "00:00:00.0260000",
"Mnc": "99",
"ServingCellLabel": "Cell7",
"Imsi": "999992226681236",
"TrackingAreaCode": "1237"
}
解决方案
看来您在文件中有数据数组,因此您应该创建一个模型对象来读取。使用 Google 提供的免费 gson 库 Like
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package com.moieen.stack.overflow.question;
import java.util.List;
/**
*
* @author moieen.abbas
*/
public class MyDataVO {
private List<Data> list_data;
public List<Data> getData() {
return list_data;
}
public void setData(List<Data> list_data) {
this.list_data = list_data;
}
}
class Data{
private String Confidence;
private String Uncertainty;
private String IsData;
private String Latitude;
private String ConnectionId;
private String Mcc;
private String Sv;
private String Longitude;
private String IsVoice;
private String IsSignalling;
private String SegmentStartTime;
private String Imei;
private String SegmentDuration;
private String Mnc;
private String ServingCellLabel;
private String Imsi;
private String TrackingAreaCode;
public String getConfidence() {
return Confidence;
}
public void setConfidence(String Confidence) {
this.Confidence = Confidence;
}
public String getUncertainty() {
return Uncertainty;
}
public void setUncertainty(String Uncertainty) {
this.Uncertainty = Uncertainty;
}
public String getIsData() {
return IsData;
}
public void setIsData(String IsData) {
this.IsData = IsData;
}
public String getLatitude() {
return Latitude;
}
public void setLatitude(String Latitude) {
this.Latitude = Latitude;
}
public String getConnectionId() {
return ConnectionId;
}
public void setConnectionId(String ConnectionId) {
this.ConnectionId = ConnectionId;
}
public String getMcc() {
return Mcc;
}
public void setMcc(String Mcc) {
this.Mcc = Mcc;
}
public String getSv() {
return Sv;
}
public void setSv(String Sv) {
this.Sv = Sv;
}
public String getLongitude() {
return Longitude;
}
public void setLongitude(String Longitude) {
this.Longitude = Longitude;
}
public String getIsVoice() {
return IsVoice;
}
public void setIsVoice(String IsVoice) {
this.IsVoice = IsVoice;
}
public String getIsSignalling() {
return IsSignalling;
}
public void setIsSignalling(String IsSignalling) {
this.IsSignalling = IsSignalling;
}
public String getSegmentStartTime() {
return SegmentStartTime;
}
public void setSegmentStartTime(String SegmentStartTime) {
this.SegmentStartTime = SegmentStartTime;
}
public String getImei() {
return Imei;
}
public void setImei(String Imei) {
this.Imei = Imei;
}
public String getSegmentDuration() {
return SegmentDuration;
}
public void setSegmentDuration(String SegmentDuration) {
this.SegmentDuration = SegmentDuration;
}
public String getMnc() {
return Mnc;
}
public void setMnc(String Mnc) {
this.Mnc = Mnc;
}
public String getServingCellLabel() {
return ServingCellLabel;
}
public void setServingCellLabel(String ServingCellLabel) {
this.ServingCellLabel = ServingCellLabel;
}
public String getImsi() {
return Imsi;
}
public void setImsi(String Imsi) {
this.Imsi = Imsi;
}
public String getTrackingAreaCode() {
return TrackingAreaCode;
}
public void setTrackingAreaCode(String TrackingAreaCode) {
this.TrackingAreaCode = TrackingAreaCode;
}
}
public class sampleData {
public static void main(String[] args) throws IOException {
String contents = new String(Files.readAllBytes(Paths.get("/home/Desktop/PD/sampledata.json")));
//Use Google GSON Library
Gson obj_gson = new Gson();
MyDataVO obj_dataVo= obj_gson.fromJson(contents, MyDataVO.class);
Data obj_data=obj_dataVo.getData().get(0);
//Get all data from obj_data.
System.out.println(obj_data.getConfidence());
}
}
推荐阅读
- java - Retrofit Api 接口中的属性值必须是常量
- python - 如何在numpy中将向量与矩阵组合
- django - 自动填写表单域
- matlab - 在带有矩阵的图像处理中使用 parfor
- javascript - JQuery返回的孩子数量超过了现有数量
- amazon-redshift - Amazon Redshift 的“ON UPDATE”等效项
- java - 帕斯卡三角 - 不需要的输出
- download - 使用 Google Sign-in 登录网络服务器
- laravel - Laravel 获取具有零关系计数的模型计数
- bash - 在 bash 中将每一行分配为一个新变量