PreparedStatement批量处理的一个Framework(原创)

场景：
批量进行DML操作，但涉及的表不同，不能使用executeBatch()

需求：
(1)如果DML中有一个错误时，要全部回滚；
(2)如果全部正确，要全部执行；

解决方案：

package jdbc;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry;public class DuplicateStatmentProcessor {private String dirver;private String url;private String user;private String password;public DuplicateStatmentProcessor(String dirver, String url, String user, String password) {super();this.dirver = dirver;this.url = url;this.user = user;this.password = password;}public void process(Map<String, List<String>> sqlWithParams) throws ClassNotFoundException, SQLException {Class.forName(dirver);Connection conn = null;PreparedStatement psmt = null;try {conn = DriverManager.getConnection(url, user, password);conn.setAutoCommit(false);Iterator<Entry<String, List<String>>> iterator = sqlWithParams.entrySet().iterator();while (iterator.hasNext()) {Entry<String, List<String>> entry = iterator.next();psmt = conn.prepareStatement(entry.getKey());int parameterIndex = 1;for (String parameter : entry.getValue()) {psmt.setString(parameterIndex, parameter);parameterIndex++;}psmt.executeUpdate();}conn.commit();} catch (SQLException e) {// 如果出错，则此次executeBatch()的所有数据都不入库 conn.rollback();e.printStackTrace();} finally {conn.setAutoCommit(true);close(conn, psmt);}}private void close(Connection conn, PreparedStatement preStmt) {if (preStmt != null) {try {preStmt.clearBatch();preStmt.clearParameters();preStmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.setAutoCommit(true);conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

 

package jdbc.oracle.demo1;import jdbc.DuplicateStatmentProcessor;public class OracleProcessor extends DuplicateStatmentProcessor {public OracleProcessor() {// oracle.jdbc.driver.OracleDriversuper("oracle.jdbc.OracleDriver","jdbc:oracle:thin:@127.0.0.1:1521:instance1","user","password");}}

 

package jdbc.oracle.demo1;import java.sql.SQLException; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map;import jdbc.DuplicateStatmentProcessor;/*2015-8-4*/ public class Processor {public static void main(String[] args) throws ClassNotFoundException, SQLException {String DEL_TB_PERSON_SQL = "delete from TB_PERSON where id=?";String DEL_TB_COURSE_SQL = "delete from tb_course where id=?";Map<String, List<String>> sqlWithParams = new HashMap<String, List<String>>();sqlWithParams.put(DEL_TB_PERSON_SQL, Arrays.asList("1"));sqlWithParams.put(DEL_TB_COURSE_SQL, Arrays.asList("3"));DuplicateStatmentProcessor processor = new OracleProcessor();processor.process(sqlWithParams);}}

结果：
测试通过

Tips:
关于executeBatch参见http://www.cnblogs.com/softidea/p/4663090.html

总结

以上是生活随笔为你收集整理的PreparedStatement批量处理的一个Framework(原创)的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。