我是使用pstmt = conn.prepareStatement(sql);得到PreparedStatement类后
通过pstmt.setString(1, "eaa");等代码补充sql语句
多次使用pstmt.addBatch();增加预编译
最后执行pstmt.executeBatch();执行所有预编译过的sql语句
通过conn.commit();提交到mysql数据库中。
例子如下:
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://" + "localhost" + ":3306/" + "test" + "?useUnicode=true&characterEncoding=UTF8&rewriteBatchedStatements=true&autoReconnect=true", "root", "mysql");
conn.setAutoCommit(false);
String sql = "REPLACE INTO type_id(type) VALUES(?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "eaASaa");
pstmt.addBatch();
pstmt.setString(1, "a");
pstmt.addBatch();
pstmt.setString(1, "b");
pstmt.addBatch();
pstmt.setString(1, "c");
pstmt.addBatch();
pstmt.setString(1, "a");
pstmt.addBatch();
pstmt.setString(1, "eaa");
pstmt.addBatch();
pstmt.setString(1, "d");
pstmt.addBatch();
int[] sss = pstmt.executeBatch();
conn.commit();
System.out.println("sss.length=" + sss.length);
} catch (Exception ex) {
Logger.getLogger(NewClass.class.getName()).log(Level.SEVERE, null, ex);
try {
pstmt.setString(1, "g");
pstmt.addBatch();
pstmt.setString(1, "l");
pstmt.addBatch();
int[] sss = pstmt.executeBatch();
conn.commit();
} catch (SQLException ex1) {
Logger.getLogger(NewClass.class.getName()).log(Level.SEVERE, null, ex1);
}
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException ex) {
Logger.getLogger(NewClass.class.getName()).log(Level.SEVERE, null, ex);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(NewClass.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
但是当我预编译的sql有错误,会出现什么情况呢?
通过实验得到以下结论:
批处理执行(pstmt.executeBatch();)时是根据添加预编译的顺序来执行的,如果是第一条错了那么所有的sql都不会执行;
如果是中间或者后面的sql错了,第一个错误之前的所有sql都会成功执行,当批处理运行到错误的sql时,直接跳到catch中,如果在catch中commit会发现数据库中成功插入了一些数据。ps:如果想要把批处理中所有正确的sql都成功插入,那么可以INSERT IGNORE INTO来插入