这篇文章主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
1.读取本地json数据
2.jdbc理解数据库
3.批量插入
maven 引入jar包:
com.alibaba
fastjson
1.2.32
commons-io
commons-io
2.4
Java实现代码:
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.io.FileUtils;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
public class Test {
public static void main(String[] args) {
Test test = new Test();
Map map = test.readJsonData();
test.jdbcConnBatch(map);
}
//批量插入数据库
void jdbcConnBatch(Map map){
try{
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=no",
"test", "test");
String sql = "insert into insurance" +
"(product_name,country,tag,tag_type,company," +
"insure_period,observation_period,qzzl,qzbz,qzhm,zjzl," +
"zjbz,zjqc,jbzmq,tdjb,sg," +
"jylstd,fhbfgn,gfqz,bzbf0,bzbf30,bzbf40)" +
" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//3.获得预处理对象
PreparedStatement pstmt = con.prepareStatement(sql);
//遍历map
for (Map.Entry entry : map.entrySet()) {
String value = entry.getValue().toJSONString();
JSONObject json = JSONObject.parseObject(value);
//4.SQL语句占位符设置实际参数
pstmt = process(pstmt,json);
pstmt.addBatch();
}
//5.执行批量操作
int [] counts = pstmt.executeBatch();
System.out.println("执行条数:"+counts.length);
//6.释放资源
pstmt.close();
con.close();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* 读取文件数据加入到map缓存中
*/
Map readJsonData(){
try{
Map map = new ConcurrentHashMap();
File testFile = new File("D:\\data.json");
String jsonString = FileUtils.readFileToString(testFile);
JSONArray jsonArr = JSONArray.parseArray(jsonString);
for(int i = 0; i < jsonArr.size(); i++) {
String str = jsonArr.get(i).toString();
JSONObject json = JSONObject.parseObject(str);
map.put(i,json);
}
return map;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
//处理PreparedStatement数据
PreparedStatement process(PreparedStatement pstmt,JSONObject json ) {
try {
pstmt.setString(1,getParameter(json.get("product_name")));
pstmt.setString(2,getParameter(json.get("country")));
pstmt.setString(3,getParameter(json.get("tag")));
pstmt.setString(4,getParameter(json.get("tag_type")));
pstmt.setString(5,getParameter(json.get("company")));
pstmt.setString(6,getParameter(json.get("insure_period")));
pstmt.setString(7,getParameter(json.get("observation_period")));
pstmt.setString(8,getParameter(json.get("qzzl")));
pstmt.setString(9,getParameter(json.get("qzbz")));
pstmt.setString(10,getParameter(json.get("qzhm")));
pstmt.setString(11,getParameter(json.get("zjzl")));
pstmt.setString(12,getParameter(json.get("zjbz")));
pstmt.setString(13,getParameter(json.get("zjqc")));
pstmt.setString(14,getParameter(json.get("jbzmq")));
pstmt.setString(15,getParameter(json.get("tdjb")));
pstmt.setString(16,getParameter(json.get("sg")));
pstmt.setString(17,getParameter(json.get("jylstd")));
pstmt.setString(18,getParameter(json.get("fhbfgn")));
pstmt.setString(19,getParameter(json.get("gfqz")));
pstmt.setString(20,getParameter(json.get("bzbf0")));
pstmt.setString(21,getParameter(json.get("bzbf30")));
pstmt.setString(22,getParameter(json.get("bzbf40")));
return pstmt;
}catch (SQLException e) {
e.printStackTrace();
}
return null;
}
String getParameter(Object obj) {
String str = null;
str = obj == null ? null : obj.toString();
return str;
}
}
mysql建表SQL:
CREATE TABLE `insurance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`tag` varchar(100) DEFAULT NULL,
`tag_type` varchar(100) DEFAULT NULL,
`company` varchar(100) DEFAULT NULL,
`insure_period` varchar(100) DEFAULT NULL,
`observation_period` varchar(100) DEFAULT NULL,
`qzzl` varchar(100) DEFAULT NULL,
`qzbz` varchar(100) DEFAULT NULL,
`qzhm` varchar(100) DEFAULT NULL,
`zjzl` varchar(100) DEFAULT NULL,
`zjbz` varchar(100) DEFAULT NULL,
`zjqc` varchar(100) DEFAULT NULL,
`jbzmq` varchar(100) DEFAULT NULL,
`tdjb` varchar(2048) DEFAULT NULL,
`sg` varchar(2028) DEFAULT NULL,
`jylstd` varchar(2048) DEFAULT NULL,
`fhbfgn` varchar(100) DEFAULT NULL,
`gfqz` varchar(100) DEFAULT NULL,
`bzbf0` varchar(100) DEFAULT NULL,
`bzbf30` varchar(100) DEFAULT NULL,
`bzbf40` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=231 DEFAULT CHARSET=utf8;
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。
本文标题: JDBC连接MySQL数据库批量插入数据过程详解
本文地址: http://www.cppcns.com/ruanjian/java/286352.html