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
     
      > sqlWithParams) throws ClassNotFoundException, SQLException {        Class.forName(dirver);        Connection conn = null;        PreparedStatement psmt = null;        try {            conn = DriverManager.getConnection(url, user, password);            conn.setAutoCommit(false);            Iterator
      
       
        >> iterator = sqlWithParams.entrySet().iterator();            while (iterator.hasNext()) {                Entry
        
         > 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.OracleDriver        super("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
     
      > sqlWithParams = new HashMap
      
       >();        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