java中JDBC实现往MySQL插入百万级数据的实例代码
作者: / 2019-09-09 / 浏览次数:

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,'xxxxxxxxx');
 set i = i+1;
 end while;
end $
call proc_initdata;

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 凯发agpstm =null;
 resultset rt = null;
 try {
 class.forname;
 conn = drivermanager.getconnection; 
 string sql = "insert into userinfo values, , 
 pstm = conn.preparestatement;
 long starttime = system.currenttimemillis;
 random rand = new random;
 int a,b,c,d;
 for  {
 pstm.setint;
 pstm.setint;
 a = rand.nextint;
 b = rand.nextint;
 c = rand.nextint;
 d = rand.nextint;
 pstm.setstring;
 pstm.setstring;27 pstm.executeupdate;
 long endtime = system.currenttimemillis;
 system.out.println); 
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 }finally{
 if{
 try {
 pstm.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 if{
 try {
 conn.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;

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

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

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

使用事务提交方式

先将命令的提交方式设为false,即手动提交conn.setautocommit;最后在所有命令执行完之后再提交事务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;
 conn = drivermanager.getconnection; 
 string sql = "insert into userinfo values, , 
 pstm = conn.preparestatement;
 conn.setautocommit;
 long starttime = system.currenttimemillis;
 random rand = new random;
 int a,b,c,d;
 for  {
 pstm.setint;
 pstm.setint;
 a = rand.nextint;
 b = rand.nextint;
 c = rand.nextint;
 d = rand.nextint;
 pstm.setstring;
 pstm.setstring;
 pstm.executeupdate;
 conn.commit;
 long endtime = system.currenttimemillis;
 system.out.println); 
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 }finally{
 if{
 try {
 pstm.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 if{
 try {
 conn.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;

以上代码插入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;
 conn = drivermanager.getconnection; 
 string sql = "insert into userinfo values, , 
 pstm = conn.preparestatement;
 long starttime = system.currenttimemillis;
 random rand = new random;
 int a,b,c,d;
 for  {
 pstm.setint;
 pstm.setint;
 a = rand.nextint;
 b = rand.nextint;
 c = rand.nextint;
 d = rand.nextint;
 pstm.setstring;
 pstm.setstring;
 pstm.addbatch;
 pstm.executebatch;
 long endtime = system.currenttimemillis;
 system.out.println); 
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 }finally{
 if{
 try {
 pstm.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 if{
 try {
 conn.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;

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;
 conn = drivermanager.getconnection; 
 string sql = "insert into userinfo values, , 
 pstm = conn.preparestatement;
 conn.setautocommit;
 long starttime = system.currenttimemillis;
 random rand = new random;
 int a,b,c,d;
 for  {
 pstm.setint;
 pstm.setint;
 a = rand.nextint;
 b = rand.nextint;
 c = rand.nextint;
 d = rand.nextint;
 pstm.setstring;
 pstm.setstring;
 pstm.addbatch;
 pstm.executebatch;
 conn.commit;
 long endtime = system.currenttimemillis;
 system.out.println); 
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 }finally{
 if{
 try {
 pstm.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;
 if{
 try {
 conn.close;
 } catch  {
 e.printstacktrace;
 throw new runtimeexception;

【某某业务】网站建设、网站设计、服务器空间租售、网站维护、网站托管、网站优化、百度推广、自媒体营销、微信公众号
如有意向---联系我们
热门栏目
热门资讯

网站建设 网站托管 成功案例 新闻动态 关于我们 联系我们 服务器空间 加盟合作 网站优化

备案号: 

公司地址:江苏省南京市玄武区玄武湖 咨询QQ:9490489 手机: 电话: