本人在做 excel 数据导入导数据库时碰到的问题，在执行 executeBatch()时就会卡住超级慢 数据库：mysql excel 文件：2W+行，56 列 步骤：首先把 excel 的数据解析出来处理放入 List<List<object>> list 中，然后循环批量插入到数据库
代码： //插入 excel 表中的一行数据
public void insert2(List<List<Object>> list) throws SQLException { Connection cOnnection= this.getConnection(); PreparedStatement ps = null; int count = 0; String sql = "INSERT INTO excel(type,batch_number,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33,a34,a35,a36,a37,a38,a39,a40,a41,a42,a43,a44,a45,a46,a47,a48,a49,a50,a51,a52,a53,a54,a55,a56) SELECT ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? FROM DUAL WHERE NOT EXISTS(SELECT a1 FROM excel WHERE type = ? and a4 = ? and a8 = ? and a27 = ?)"; try{ connection.setAutoCommit(false);//设置手动提交 ps = connection.prepareStatement(sql); for (int i = 1; i<list.size(); i++) { int j1 = 0; String str1 = null; ps.setString(1, (String)list.get(i).get(0)); ps.setString(2, (String)list.get(i).get(1)); ps.setString(3, (String)list.get(i).get(2)); ps.setString(4, (String)list.get(i).get(3)); ps.setString(5, (String)list.get(i).get(4)); ps.setString(6, (String)list.get(i).get(5)); ps.setString(7, (String)list.get(i).get(6)); ps.setString(8, (String)list.get(i).get(7)); ps.setString(9, (String)list.get(i).get(8)); ps.setString(10, (String)list.get(i).get(9)); ps.setString(11, (String)list.get(i).get(10)); ps.setString(12, (String)list.get(i).get(11)); ps.setString(13, (String)list.get(i).get(12)); ps.setString(14, (String)list.get(i).get(13)); ps.setString(15, (String)list.get(i).get(14)); ps.setString(16, (String)list.get(i).get(15)); ps.setString(17, (String)list.get(i).get(16)); ps.setString(18, (String)list.get(i).get(17)); ps.setString(19, (String)list.get(i).get(18)); ps.setString(20, (String)list.get(i).get(19)); ps.setString(21, (String)list.get(i).get(20)); ps.setString(22, (String)list.get(i).get(21)); ps.setString(23, (String)list.get(i).get(22)); ps.setString(24, (String)list.get(i).get(23)); ps.setString(25, (String)list.get(i).get(24)); ps.setString(26, (String)list.get(i).get(25)); ps.setString(27, (String)list.get(i).get(26)); ps.setString(28, (String)list.get(i).get(27)); ps.setString(29, (String)list.get(i).get(28)); ps.setString(30, (String)list.get(i).get(29)); ps.setString(31, (String)list.get(i).get(30)); ps.setString(32, (String)list.get(i).get(31)); ps.setString(33, (String)list.get(i).get(32)); ps.setString(34, (String)list.get(i).get(33)); ps.setString(35, (String)list.get(i).get(34)); ps.setString(36, (String)list.get(i).get(35)); ps.setString(37, (String)list.get(i).get(36)); ps.setString(38, (String)list.get(i).get(37)); ps.setString(39, (String)list.get(i).get(38)); ps.setString(40, (String)list.get(i).get(39)); ps.setString(41, (String)list.get(i).get(40)); ps.setString(42, (String)list.get(i).get(41)); ps.setString(43, (String)list.get(i).get(42)); ps.setString(44, (String)list.get(i).get(43)); ps.setString(45, (String)list.get(i).get(44)); ps.setString(46, (String)list.get(i).get(45)); ps.setString(47, (String)list.get(i).get(46)); ps.setString(48, (String)list.get(i).get(47)); ps.setString(49, (String)list.get(i).get(48)); ps.setString(50, (String)list.get(i).get(49)); ps.setString(51, (String)list.get(i).get(50)); ps.setString(52, (String)list.get(i).get(51)); ps.setString(53, (String)list.get(i).get(52)); ps.setString(54, (String)list.get(i).get(53)); ps.setString(55, (String)list.get(i).get(54)); ps.setString(56, (String)list.get(i).get(55)); ps.setString(57, ""); ps.setString(58, ""); ps.setString(59, (String)list.get(i).get(0)); ps.setString(60, (String)list.get(i).get(5)); ps.setString(61, (String)list.get(i).get(9)); ps.setString(62, (String)list.get(i).get(28)); ps.addBatch();//加入批量处理 count++; System.out.println(count); if (i > 0 && i % 1000 == 0) { ps.executeBatch(); connection.commit(); ps.clearBatch(); System.out.println("提交：" + i); } } ps.executeBatch(); // 执行批量处理 connection.commit(); // 提交 connection.setAutoCommit(true);//在把自动提交打开 System.out.println("执行完毕:"+count); }catch (Exception e) { e.printStackTrace(); }finally { this.close(connection, ps, null); } } 
1
Select Language