Detailed explanation of batch data insertion process of JDBC connecting to MySQL database

This article mainly introduces the detailed explanation of the batch data insertion process of JDBC connecting to MySQL database. The example code is introduced in great detail, which has a certain reference value for everyone's study or work. Friends in need can refer to it

1. Read local JSON data

2. JDBC understanding database

3. Batch insert

Maven import jar package:

<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>

Java implementation code:

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;
  }

}

MySQL table creation 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;

The above is the whole content of this article. I hope it will help you in your study, and I hope you will support us a lot.