JDBC连接MySQL数据库批量插入数据过程详解

JDBC连接MySQL数据库批量插入数据过程详解

这篇文章主要介绍了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

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_periodhttp://` 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;

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_periodhttp://` 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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。