1.适用情况:当对数据库更新(insert,update,delete)的数据量过大的时候,如果用s.executeUpdate()语句,容易造成数据库瘫痪。
2.首先,我们为了简化代码,先将关闭数据库连接的代码封装在一个叫DBHelp的类中:
public class DBHelp {
public static void close(Statement s, Connection c) {
close(null, s, c);
}
public static void close(ResultSet rs, Statement s, Connection c) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (c != null)
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3.举例对数据库进行批量插入,循环的时候先将sql加入批次,即:s.addBatch(),可以把大量数据分成多个批次依次执行即,s.executeBatch(),如下:
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/how2j?user=root&password=admin";
String driverName = "com.mysql.cj.jdbc.Driver";
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection c = null;
PreparedStatement s =null;
ResultSet rs =null;
try {
c = DriverManager.getConnection(url);
String sql = "insert into user(username,password) values(?,?)";
s = c.prepareStatement(sql);
for (int i = 1; i < 1099; i++) {
s.setString(1, "zhang"+i);
s.setString(2, "09"+i);
s.addBatch();
if(i%100==0) {
//一百条为一批,进行批处理
s.executeBatch();
}
}
//最后一批:i%100!=0
s.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBHelp.close(rs, s, c);
}
}