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

<dependency>     <groupId>com.alibaba</groupId>     <artifactId>fastjson</artifactId>     <version>1.2.32</version>   </dependency>    <dependency>     <groupId>commons-io</groupId>     <artifactId>commons-io</artifactId>     <version>2.4</version>   </dependency>

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<Integer, JSONObject> map = test.readJsonData();    test.jdbcConnBatch(map);   }   //批量插入数据库  void jdbcConnBatch(Map<Integer, JSONObject> 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<Integer, JSONObject> 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<Integer, JSONObject> readJsonData(){    try{      Map<Integer, JSONObject> 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;  }   }

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;