最近上了一个老项目，要修改一些业务，具体的思路是在jsp中实现对数据的某些批量操作，因此做一下笔记。
1.整体jdbc建立连接/关闭连接
conn = DbUtil.getConnection();
statement = conn.createStatement();
resultSet = null;
//保存当前提交状态
boolean autoCommit = conn.getAutoCommit();
//关闭自动提交
conn.setAutoCommit(false);
String updateMANUALSql = "*****";
statement.addBatch(updateMANUALSql);
try {
statement.executeBatch();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
conn.rollback();
} finally {
//重置
conn.setAutoCommit(autoCommit);
//清除批处理命令
statement.clearBatch();
}
//关闭resultSet
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception ex) {
ex.printStackTrace();
}
resultSet = null;
}
//关闭statement
statement.clearBatch();
if (statement != null) {
try {
statement.close();
} catch (Exception ex) {
ex.printStackTrace();
}
statement = null;
}
//关闭数据库连接
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
conn = null;
}
2.批量插入更改删除数据优化
String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " +
"macaddress, createtime) values(?,?,?,?,?,?,?)";
try{
conn = DBConnection.getConnection();
ps = conn.prepareStatement(sql); //保存当前提交状态
boolean autoCommit = conn.getAutoCommit();
//关闭自动提交
conn.setAutoCommit(false); int len = list.size();
for(int i=0; i<len; i++) {
ps.setString(1, list.get(i).getGuid());
ps.setString(2, list.get(i).getDeviceBrand());
ps.setString(3, list.get(i).getDeviceName());
ps.setString(4, list.get(i).getDeviceIp());
ps.setString(5, list.get(i).getIpAddress());
ps.setString(6, list.get(i).getMacAddress());
ps.setString(7, list.get(i).getCreateTime()); //插入代码打包，等一定量后再插入
ps.addBatch();
//每200次提交一次
if((i!=0 && i%200==0) || i==len-1){//可以设置不同的大小；如50，100，200，500，1000等等
ps.executeBatch();
//提交，批量插入数据库中。
conn.commit();
ps.clearBatch();
}
}