方法一：耗时最长（我的电脑比较老，插两万条大概耗时一分多钟）
package demo04; import java.sql.Connection; import java.sql.PreparedStatement; import org.junit.jupiter.api.Test; import utils.JDBCUtils; /** * 使用prepaerdStatement实现批量插入 * @author pc * */ public class InsertTest { @Test public void test(){ Connection conn = null; PreparedStatement ps = null; try { long start = System.currentTimeMillis(); conn = JDBCUtils.getConnection(); String sql = "insert into admin (username)values(?)"; ps = conn.prepareStatement(sql); for (int i = 0; i <= 20000; i++) { ps.setObject(1, "name_"+i); ps.execute(); } long end = System.currentTimeMillis(); System.out.println("花费时间为："+(end-start)); //花费时间为：908010 } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResourse(conn, ps); } } }
改进一：设置参数让mysql开启对批处理的支持（耗时3s多，效率明显提升）
package demo04; import java.sql.Connection; import java.sql.PreparedStatement; import org.junit.jupiter.api.Test; import utils.JDBCUtils; /** * 使用prepaerdStatement实现批量插入 * @author pc * */ public class InsertTest { /** * mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。 * ?rewriteBatchedStatements=true 写在配置文件的url后面 * 使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar */ @Test public void test2(){ Connection conn = null; PreparedStatement ps = null; try { long start = System.currentTimeMillis(); conn = JDBCUtils.getConnection(); String sql = "insert into admin (username)values(?)"; ps = conn.prepareStatement(sql); for (int i = 0; i <= 20000; i++) { ps.setObject(1, "name_"+i); //1. “攒batch” ps.addBatch(); //2. 执行 if(i % 500 == 0) { ps.executeBatch(); } //3. 清空batch ps.clearBatch(); } long end = System.currentTimeMillis(); System.out.println("花费时间为："+(end-start)); //花费时间为：3060 } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResourse(conn, ps); } } }
改进二：不允许自动提交数据，等数据积攒到一定量了之后一次提交
package demo04; import java.sql.Connection; import java.sql.PreparedStatement; import org.junit.jupiter.api.Test; import utils.JDBCUtils; /** * 使用prepaerdStatement实现批量插入 * @author pc * */ public class InsertTest { @Test public void testFinal(){ Connection conn = null; PreparedStatement ps = null; try { long start = System.currentTimeMillis(); conn = JDBCUtils.getConnection(); //设置不允许自动提交数据 conn.setAutoCommit(false); String sql = "insert into admin (username)values(?)"; ps = conn.prepareStatement(sql); for (int i = 0; i <= 20000; i++) { ps.setObject(1, "name_"+i); //1. “攒batch” ps.addBatch(); //2. 执行 if(i % 500 == 0) { ps.executeBatch(); } //3. 清空batch ps.clearBatch(); } //统一提交数据 conn.commit(); long end = System.currentTimeMillis(); System.out.println("花费时间为："+(end-start)); //花费时间为：898 } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResourse(conn, ps); } } }
