使用PreparedStatement实现批量数据的操作
package com.sammery.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.sammery.util.JDBCUtils;
public class InsertTest {
@Test
public void testInsert2() throws Exception{
Connection connection = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "INSERT INTO goods(`name`) VALUES(?)";
ps = connection.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_"+i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("运行时间:"+(end-start)+"毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
}
@Test
public void testInsert3() throws Exception{
Connection connection = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql = "INSERT INTO goods(`name`) VALUES(?)";
ps = connection.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_"+i);
ps.addBatch();
if(i % 500 == 0){
ps.executeBatch();
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("运行时间:"+(end-start)+"毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
}
@Test
public void testInsert4() throws Exception{
Connection connection = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
String sql = "INSERT INTO goods(`name`) VALUES(?)";
ps = connection.prepareStatement(sql);
for (int i = 1; i <= 1000000; i++) {
ps.setObject(1, "name_"+i);
ps.addBatch();
if(i % 500 == 0){
ps.executeBatch();
ps.clearBatch();
}
}
connection.commit();
long end = System.currentTimeMillis();
System.out.println("运行时间:"+(end-start)+"毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
}
}