使用PreparedStatement实现批量数据的操作(代码优化)
package com.shan.blob;
import com.shan.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class InsertTest {
public static void main(String[] args) {
testInsert3();
}
public static void testInsert1(){
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)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));
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
public static void testInsert2(){
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for(int i=0; i<=20000; 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(conn,ps);
}
}
public static void testInsert3(){
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
for(int i=0; i<=20000; i++){
ps.setObject(1, "name_" + i);
ps.addBatch();
if(i%500 == 0){
ps.executeBatch();
ps.clearBatch();
}
}
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为:" + (end-start));
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
}