当前位置 主页 > 网站技术 > 代码类 >

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

    栏目:代码类 时间:2019-11-14 11:02

    这篇文章主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

    1.读取本地json数据

    2.jdbc理解数据库

    3.批量插入

    maven 引入jar包:

    <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实现代码:

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