Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//自己写的工具类不要误解
String sql1 = "insert into person values (1,'aaa')";
String sql2 = "insert into person values (2,'bbb')";
String sql3 = "delete from person where id = 2";
stmt = conn.createStatement();//这种方式,如果sql语句有上百条以上,那就要有很多次addBatch。很麻烦
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
int[] i = stmt.executeBatch();// 获得三条语句每条执行后影响的行数。形成一个数组
for (int j : i) {
System.out.println(j);// 结果是三个1
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(rs, stmt, conn);//工具类,释放连接
}
方式二:用PreparedStatment批处理,只能处理sql语句相同,而参数不同。
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into person values (?,?)";
stmt = conn.prepareStatement(sql);
for(int i=0;i<100;i++){
stmt.setInt(1, i+1);
stmt.setString(2, "aaa");//参数数据,实际应用时,可以通过别的方式进行赋值。
stmt.addBatch();
}
stmt.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(rs, stmt, conn);
}
注意:对mysql进行存储,如果批处理有1000万条,需要40分钟左右!而且此时方式二要注意,prepareStatement在缓存sql时的溢出。
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into person values (?,?)";
stmt = conn.prepareStatement(sql);
for(int i=0;i<1000000;i++){//mysql一般需要40分钟才会存完
stmt.setInt(1, i+1);
stmt.setString(2, "aaa");
<span style="white-space:pre"> </span> stmt.addBatch();
if((i+1)%1000==0){
stmt.executeBatch();//每执行一次<span style="font-family: Arial, Helvetica, sans-serif;">就清一次sql缓存。</span>
stmt.clearBatch(); //否则每一次是执行1000,2000,3000...条sql语句而不是每一次执行1000条
}
}
stmt.executeBatch();//最后少于1000条sql语句也要执行。
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(rs, stmt, conn);
}