当前位置：移动技术网 > IT编程>开发语言>Java > java中JDBC实现往MySQL插入百万级数据的实例代码

java中JDBC实现往MySQL插入百万级数据的实例代码

2019年07月22日 | 移动技术网IT编程 | 我要评论

想往某个表中插入几百万条数据做下测试，原先的想法，直接写个循环10w次随便插入点数据试试吧，好吧，我真的很天真....

drop procedure if exists proc_initdata;--如果存在此存储过程则删掉
delimiter $
create procedure proc_initdata()
begin
  declare i int default 1;
  while i<=100000 do
    insert into text values(i,concat('姓名',i),'xxxxxxxxx');
    set i = i+1;
  end while;
end $
call proc_initdata();

执行call proc_initdata()后，本来想想，再慢10w条数据顶多30分钟能搞定吧，结果我打了2把lol后，回头一看，还在执行，此时心里是彻底懵逼的....待我打完第三把结束后，终于执行完了，这种方法若是让我等上几百万条数据，是不是早上去上班，下午下班回来还没结束呢？10w条数据，有图有真相

jdbc往数据库中普通插入方式

后面查了一下，使用jdbc批量操作往数据库插入100w+的数据貌似也挺快的，

先来说说jdbc往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

private string url = "jdbc:mysql://localhost:3306/test01";
  private string user = "root";
  private string password = "123456";
  @test
  public void test(){
    connection conn = null;
    preparedstatement pstm =null;
    resultset rt = null;
    try {
      class.forname("com.mysql.jdbc.driver");
      conn = drivermanager.getconnection(url, user, password);    
      string sql = "insert into userinfo(uid,uname,uphone,uaddress) values(?,concat('姓名',?),?,?)";
      pstm = conn.preparestatement(sql);
      long starttime = system.currenttimemillis();
      random rand = new random();
      int a,b,c,d;
      for (int i = 1; i <= 1000; i++) {
          pstm.setint(1, i);
          pstm.setint(2, i);
          a = rand.nextint(10);
          b = rand.nextint(10);
          c = rand.nextint(10);
          d = rand.nextint(10);
          pstm.setstring(3, "188"+a+"88"+b+c+"66"+d);
          pstm.setstring(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27           pstm.executeupdate();
      }
      long endtime = system.currenttimemillis();
      system.out.println("ok,用时：" + (endtime - starttime)); 
    } catch (exception e) {
      e.printstacktrace();
      throw new runtimeexception(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
    }
  }

输出结果：ok,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。

在讨论批量处理之前，先说说遇到的坑，首先，jdbc连接的url中要加rewritebatchedstatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100w条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来...

那么低版本的驱动包是否对100w+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。

使用事务提交方式

先将命令的提交方式设为false，即手动提交conn.setautocommit(false);最后在所有命令执行完之后再提交事务conn.commit();

private string url = "jdbc:mysql://localhost:3306/test01";
  private string user = "root";
  private string password = "123456";
  @test
  public void test(){
    connection conn = null;
    preparedstatement pstm =null;
    resultset rt = null;
    try {
      class.forname("com.mysql.jdbc.driver");
      conn = drivermanager.getconnection(url, user, password);    
      string sql = "insert into userinfo(uid,uname,uphone,uaddress) values(?,concat('姓名',?),?,?)";
      pstm = conn.preparestatement(sql);
      conn.setautocommit(false);
      long starttime = system.currenttimemillis();
      random rand = new random();
      int a,b,c,d;
      for (int i = 1; i <= 100000; i++) {
          pstm.setint(1, i);
          pstm.setint(2, i);
          a = rand.nextint(10);
          b = rand.nextint(10);
          c = rand.nextint(10);
          d = rand.nextint(10);
          pstm.setstring(3, "188"+a+"88"+b+c+"66"+d);
          pstm.setstring(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
          pstm.executeupdate();
      }
      conn.commit();
      long endtime = system.currenttimemillis();
      system.out.println("ok,用时：" + (endtime - starttime)); 
    } catch (exception e) {
      e.printstacktrace();
      throw new runtimeexception(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
    }
  }

以上代码插入10w条数据，输出结果：ok,用时：18086，也就十八秒左右的时间，理论上100w也就是3分钟这样，勉强还可以接受。

批量处理

接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。

private string url = "jdbc:mysql://localhost:3306/test01?rewritebatchedstatements=true";
  private string user = "root";
  private string password = "123456";
  @test
  public void test(){
    connection conn = null;
    preparedstatement pstm =null;
    resultset rt = null;
    try {
      class.forname("com.mysql.jdbc.driver");
      conn = drivermanager.getconnection(url, user, password);    
      string sql = "insert into userinfo(uid,uname,uphone,uaddress) values(?,concat('姓名',?),?,?)";
      pstm = conn.preparestatement(sql);
      long starttime = system.currenttimemillis();
      random rand = new random();
      int a,b,c,d;
      for (int i = 1; i <= 100000; i++) {
          pstm.setint(1, i);
          pstm.setint(2, i);
          a = rand.nextint(10);
          b = rand.nextint(10);
          c = rand.nextint(10);
          d = rand.nextint(10);
          pstm.setstring(3, "188"+a+"88"+b+c+"66"+d);
          pstm.setstring(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
          pstm.addbatch();
      }
      pstm.executebatch();
      long endtime = system.currenttimemillis();
      system.out.println("ok,用时：" + (endtime - starttime)); 
    } catch (exception e) {
      e.printstacktrace();
      throw new runtimeexception(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
    }
  }

10w输出结果：ok,用时：3386，才3秒钟.

批量操作+事务

然后我就想，要是批量操作+事务提交呢？会不会有神器的效果？

private string url = "jdbc:mysql://localhost:3306/test01?rewritebatchedstatements=true";
  private string user = "root";
  private string password = "123456";
  @test
  public void test(){
    connection conn = null;
    preparedstatement pstm =null;
    resultset rt = null;
    try {
      class.forname("com.mysql.jdbc.driver");
      conn = drivermanager.getconnection(url, user, password);    
      string sql = "insert into userinfo(uid,uname,uphone,uaddress) values(?,concat('姓名',?),?,?)";
      pstm = conn.preparestatement(sql);
      conn.setautocommit(false);
      long starttime = system.currenttimemillis();
      random rand = new random();
      int a,b,c,d;
      for (int i = 1; i <= 100000; i++) {
          pstm.setint(1, i);
          pstm.setint(2, i);
          a = rand.nextint(10);
          b = rand.nextint(10);
          c = rand.nextint(10);
          d = rand.nextint(10);
          pstm.setstring(3, "188"+a+"88"+b+c+"66"+d);
          pstm.setstring(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
          pstm.addbatch();
      }
      pstm.executebatch();
      conn.commit();
      long endtime = system.currenttimemillis();
      system.out.println("ok,用时：" + (endtime - starttime)); 
    } catch (exception e) {
      e.printstacktrace();
      throw new runtimeexception(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (sqlexception e) {
          e.printstacktrace();
          throw new runtimeexception(e);
        }
      }
    }
  }

以下是100w数据输出对比：（5.1.17版本mysql驱动包下测试，交替两种方式下的数据测试结果对比）

批量操作(10w)	批量操作+事务提交(10w)	批量操作(100w)	批量错作+事务提交(100w)
ok,用时：3901	ok,用时：3343	ok,用时：44242	ok,用时：39798
ok,用时：4142	ok,用时：2949	ok,用时：44248	ok,用时：39959
ok,用时：3664	ok,用时：2689	ok,用时：44389	ok,用时：39367

可见有一定的效率提升，但是并不是太明显，当然因为数据差不算太大，也有可能存在偶然因数，毕竟每项只测3次。

预编译+批量操作

网上还有人说使用预编译+批量操作的方式能够提高效率更明显，但是本人亲测，效率不高反降，可能跟测试的数据有关吧。

预编译的写法，只需在jdbc的连接url中将写入useserverprepstmts=true即可，

如：

复制代码代码如下:

 private string url = "jdbc:mysql://localhost:3306/test01?useserverprepstmts=true&rewritebatchedstatements=true"

 

好了，先到这里...

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持移动技术网。

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

RecycleView 源码解析

ListView 能局部刷新吗？可以。。。 [阅读全文]
springboot 整合 Jpush 极光推送

产品简介：JPush 是经过考验的大规模 App 推送平台，每天推送消息数超过 5 亿条。开发者集成 SDK ... [阅读全文]
[杭电多校2020]第一场 1004 Distinct Sub-palindromes

Distinct Sub-palindromes题目链接：http://acm.hdu.edu.cn/showp... [阅读全文]
报错处理：java.lang.IllegalStateException: You need to use a Theme.AppCompat theme with this activity 

记录一个安卓报错的处理方法：java.lang.IllegalStateException: You need ... [阅读全文]
Swift -- 将本地生成的UIImage进行持久化保存（存到文件中fileManager.createFile）

//在相册或者拍照的代理方法中struct ImageSource { var img: UIImage ... [阅读全文]
Windows的Android studio安装教程

一、JDK安装1、JDK下载安装包下载地址：http://www.oracle.com/technetwor... [阅读全文]
Fragment的介绍以及加载详细说明

Fragment与Activity的区别1.Feagment是安装3.0之后才有的2.一个Activity可以运... [阅读全文]
Android Camera video数据流

在Android系统中，实现一个具有录像功能的应用程序只需要调用MediaRecorder的相应接口即可。下面简... [阅读全文]
SpringBoot +Dcloud个推成功案例复制即用

SpringBoot + Dcloud个推调用方法赋值的实体单推方法的实例部分群推的方法实例下面是工具方法类**... [阅读全文]
Jetpack Paging3分页库

Jetpack Paging3分页库简介分页库可帮助您一次加载和显示一小块数据。按需载入部分数据会减少网络带宽和... [阅读全文]

网友评论


验证码：

java中JDBC实现往MySQL插入百万级数据的实例代码

2019年07月22日 | 移动技术网IT编程 | 我要评论

您可能感兴趣的文章:

相关文章:

网友评论