Java学习交流群：495273252

一、解析问题.

Java向MySql数据库插入万级记录时,采用的方案不同时执行速度会有所不同,数据量越大则优劣越明显.所以选取最优方案尤其重要,本文目前提供如下两种解决方案(不借用第三方框架或工具).

二、解决问题.

1、方案一：循环逐条插入.

关键代码：

//DataModel 为自定义的数据模型类,dataList 即传入的即将要插入的数据集合；public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException{//开始计时；Long begin = new Date().getTime();//创建要执行的sql语句；String sql = "insert into tb_ncdc values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";/* 创建并获取JDBC连接类"Connection"的实例对象.(DBUtil类内为数据库访问的配置信息,需要自定义)*/Connection connection = new DBUtil().getDbCon();//PrepareStatement类存放每条记录对应的字段值；PreparedStatement preparedStatement= connection.prepareStatement(sql); for (int i = 0; i < dataList.size(); i ++) { preparedStatement.clearParameters(); preparedStatement.setString(1, dataList.get(i).getSTN()); preparedStatement.setString(2, dataList.get(i).getWBAN()); preparedStatement.setString(3, dataList.get(i).getYEARMODA()); preparedStatement.setString(4, dataList.get(i).getTEMP()); preparedStatement.setString(5, dataList.get(i).getDEWP()); preparedStatement.setString(6, dataList.get(i).getSLP()); preparedStatement.setString(7, dataList.get(i).getSTP()); preparedStatement.setString(8, dataList.get(i).getVISIB()); preparedStatement.setString(9, dataList.get(i).getWDSP()); preparedStatement.setString(10, dataList.get(i).getMXSPD()); preparedStatement.setString(11, dataList.get(i).getGUST()); preparedStatement.setString(12, dataList.get(i).getMAX()); preparedStatement.setString(13, dataList.get(i).getMIN()); preparedStatement.setString(14, dataList.get(i).getPRCP()); preparedStatement.setString(15, dataList.get(i).getSNDP()); preparedStatement.setString(16, dataList.get(i).getFRSHTT()); preparedStatement.execute(); } /*如果autocommit=false时(默认为true,即自动提交事务)记得将本次事务提交,否则数据库里没有数据的；*/ //connection.commit(); //所有数据库操作结束后记得关闭连接,减少内存的占用； preparedStatement.close(); connection.close(); // 结束时间 Long end = new Date().getTime(); //总耗时 System.out.println("插入"+dataList.size()+"条数据的总时间为 : " + (end - begin) + " ms"); return 1;}

2、方案二：分批事务插入.

//DataModel 为自定义的数据模型类,dataList 即传入的即将要插入的数据集合；public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException { //设定每批、每次事务插入多少条数据； int itemNum = 1000; //开始时间； Long begin = new Date().getTime(); // 创建sql前缀 String prefix = "INSERT INTO tb_ncdc VALUES "; /* 创建并获取JDBC连接类"Connection"的实例对象.(DBUtil类内为数据库访问的配置信息,需要自定义) */ Connection connection = new DBUtil().getDbCon(); // PrepareStatement类存放每条记录对应的字段值； PreparedStatement preparedStatement= connection.prepareStatement(""); // 创建sql后缀 StringBuffer suffix = new StringBuffer(); // 设置事务为非自动提交 connection.setAutoCommit(false); //根据总的数据量计算需要分多少次事务插入； int numTrans = dataList.size() / itemNum + 1; //设定首次事务中的数据在集合中的索引为0； int numData = 0; // 外层循环,j代表提交事务次序； for (int j = 1; j <= numTrans; j++) { // 从索引numData开始查找总数为itemNum个数据,即为本批要插入的数据量； for (int i = numData; i < numData + itemNum; i++) { //判定如果是最后一批,可能会不足itemNum数量,则够数结束,防止数组越界； if (i == dataList.size()) { break; } // 构建sql后缀 suffix.append("('" + dataList.get(i).getSTN() + "','" + dataList.get(i).getWBAN() + "','" + dataList.get(i).getYEARMODA() + "','" + dataList.get(i).getTEMP() + "','" + dataList.get(i).getDEWP() + "','" + dataList.get(i).getSLP() + "','" + dataList.get(i).getSTP() + "','" + dataList.get(i).getVISIB() + "','" + dataList.get(i).getWDSP() + "','" + dataList.get(i).getMXSPD() + "','" + dataList.get(i).getGUST() + "','" + dataList.get(i).getMAX() + "','" + dataList.get(i).getMIN() + "','" + dataList.get(i).getPRCP() + "','" + dataList.get(i).getSNDP() + "','" + dataList.get(i).getFRSHTT() + "'),"); } // 构建完整sql String sql = prefix + suffix.substring(0, suffix.length() - 1); // 添加sql批； preparedStatement.addBatch(sql); // 执行sql批； preparedStatement.executeBatch(); // 提交本次事务 connection.commit(); // 清空上一次的sql后缀； suffix = new StringBuffer(); numData += itemNum; } // 所有数据库操作结束后记得关闭连接,减少内存的占用； preparedStatement.close(); connection.close(); // 结束时间 Long end = new Date().getTime(); // 耗时 System.out.println("插入" + dataList.size() + "条数据的总时间为 : "+ (end - begin) + " ms"); return 1;}

三 、总结问题.

1.两种方案的主要区别在于,sql语句的不同、batch批和事务的使用.

单条插入sql语句：insert into Table (col1,col2...) values (val11,val12...);

多条批插入sql语句：insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;

2.本次测试的实例中,插入69万条数据左右,方案二要比方案一的速度快上10倍左右.具体测试得到的具体毫秒数可能不同.影响因素个人认为有如下几条：

(1)数据模型,每条数据记录的字段越多,就需要调整itemNum(每批插入的数据量,可以采用二分法找到最合适的数值),或者调整MySql数据库对每次执行sql语句的字节长度限制(网上自行搜索).itemNum值找到最合适的,速度才可能在其他条件同等的条件下是最快的；

(2)主机配置.包括处理器性能、硬盘性能 ,mysql数据库可能也会影响到速度；

原文作者