方法一:耗时最长(我的电脑比较老,插两万条大概耗时一分多钟)
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);
}
}
}