方式一:使用statement实现
public class Demo {
@Test
public void test() throws Exception {
Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
long begin = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
String sql = "insert into goods (`name`) values ('name_" + i + "')";
st.executeUpdate(sql);
}
long end = System.currentTimeMillis();
System.out.println(end - begin);
}
}
方式二:使用PreparedStatement实现
public class Demo {
@Test
public void test() throws Exception {
Connection conn = null;
PreparedStatement statement = null;
try {
long begin = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods (`name`) values (?)";
statement = conn.prepareStatement(sql);
for (int i = 1; i <= 10000; i++) {
statement.setObject(1, "name_" + i);
statement.execute();
}
long end = System.currentTimeMillis();
System.out.println(end - begin);//327414
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, statement);
}
}
}
方式三:使用PreparedStatement实现(Batch相关方法)
public class Demo {
@Test
public void test() throws Exception {
Connection conn = null;
PreparedStatement statement = null;
try {
long begin = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods (`name`) values (?)";
statement = conn.prepareStatement(sql);
for (int i = 1; i <= 10000; i++) {
statement.setObject(1, "name_" + i);
//1.攒SQL
statement.addBatch();
if (i % 500 == 0) {
//2.执行操作
statement.executeBatch();
//3.清空SQL
statement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println(end - begin);//1180
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, statement);
}
}
}
需要修改两个点:
- mysql服务器默认是关闭批处理的,在连接时要设置参数来开启批处理的支持
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
- 使用更新的mysql 驱动:mysql-connector-java-5.1.37-bin.jar
方式四:使用PreparedStatement实现(阻止自动提交)
public class Demo {
@Test
public void test() throws Exception {
Connection conn = null;
PreparedStatement statement = null;
try {
long begin = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);//阻止自动提交
String sql = "insert into goods (`name`) values (?)";
statement = conn.prepareStatement(sql);
for (int i = 1; i <= 10000; i++) {
statement.setObject(1, "name_" + i);
//1.攒SQL
statement.addBatch();
if (i % 500 == 0) {
//2.执行操作
statement.executeBatch();
//3.清空SQL
statement.clearBatch();
}
}
conn.commit();//提交数据
long end = System.currentTimeMillis();
System.out.println(end - begin);//1793
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, statement);
}
}
}