iBatis batch处理那些事
昨天应同事要求在框架中（Spring+iBatis2.3.4）加入Batch处理，于是满足之，由于需要更灵活并且不想为批量插入、批量更新、批量删除等操作单独写对应的方法，于是写了这样的一个方法

public Object batchExecute(final CallBack callBack) {Object result = getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {@Overridepublic Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {executor.startBatch();Object obj = callBack.execute(new IbatisSqlExecutor(executor));executor.executeBatch();return obj;}});return result;}

不想将SqlMapExecutor侵入到业务代码中，于是又有了如下3个类，在今天的主题中不是关键，可以忽略，只是为了将代码贴完整

public interface SqlExecutor {Object insert(String id, Object parameterObject) throws SQLException;Object insert(String id) throws SQLException;int update(String id, Object parameterObject) throws SQLException;int update(String id) throws SQLException;int delete(String id, Object parameterObject) throws SQLException;int delete(String id) throws SQLException;Object queryForObject(String id, Object parameterObject) throws SQLException;Object queryForObject(String id) throws SQLException;Object queryForObject(String id, Object parameterObject, Object resultObject) throws SQLException;}

class IbatisSqlExecutor implements SqlExecutor {private SqlMapExecutor executor;IbatisSqlExecutor(SqlMapExecutor executor) {this.executor = executor;}@Overridepublic Object insert(String id, Object parameterObject) throws SQLException {return executor.insert(id, parameterObject);}// 剩下的就省略了，和insert都类似}

public interface CallBack {Object execute(SqlExecutor executor);}

然后执行了一个类似以下伪代码行为的操作：

getDao().batchExecute(new CallBack() {        @Override        public Object execute(SqlExecutor executor) {                for (int i = 0; i < 10000; ++i) {                        // 注意每个sql_id的sql语句都是不相同的                        executor.insert("id1", obj1);                        executor.insert("id2", obj2);                        // ...                        executor.insert("idn", objn);                }                return null;        }});

再然后...尼玛不但速度没变快还异常了，原因竟然是生成了太多的PreparedStatement，你妹不是批处理吗？不是应该一个sql只有一个PreparedStatement吗？
跟踪iBatis代码，发现了iBatis是这样处理的

// 以下代码来自com.ibatis.sqlmap.engine.execution.SqlExecutor$Batch    public void addBatch(StatementScope statementScope, Connection conn, String sql, Object[] parameters) throws SQLException {      PreparedStatement ps = null;// 就是它：currentSql      if (currentSql != null && currentSql.equals(sql)) {        int last = statementList.size() - 1;        ps = (PreparedStatement) statementList.get(last);      } else {        ps = prepareStatement(statementScope.getSession(), conn, sql);        setStatementTimeout(statementScope.getStatement(), ps);// 就是它：currentSql        currentSql = sql;        statementList.add(ps);        batchResultList.add(new BatchResult(statementScope.getStatement().getId(), sql));      }      statementScope.getParameterMap().setParameters(statementScope, ps, parameters);      ps.addBatch();      size++;    }

不细解释了，只看currentSql这个实例变量就知道了，如果sql与前一次不同那么会新建一个PreparedStatement，所以刚才的伪代码应该这样写：

getDao().batchExecute(new CallBack() {        @Override        public Object execute(SqlExecutor executor) {                for (int i = 0; i < 10000; ++i) {                        executor.insert("id1", obj1);                }                for (int i = 0; i < 10000; ++i) {                        executor.insert("id2", obj2);                }                // ...你就反复写for循环吧                return null;        }});

很不爽是不？反正我是决了一个定，改iBatis的源码
改源码最好这么来：
1.复制对应类的源码到工程下，本例中要复制的是com.ibatis.sqlmap.engine.execution.SqlExecutor
注意要保持包名，其实是类的全限定名称要一样哇，这样根据ClassLoader的类加载机制会先加载你工程中的SqlExecutor而不加载iBatis jar包中的对应SqlExecutor
如图：



2.改之，只改static class Batch这个内部类即可，策略是去掉currentSql，将PreparedStatement放入HashMap
如下：

public void addBatch(StatementScope statementScope, Connection conn, String sql, Object[] parameters) throws SQLException {PreparedStatement ps = statementMap.get(sql);if (ps == null) {ps = prepareStatement(statementScope.getSession(), conn, sql);setStatementTimeout(statementScope.getStatement(), ps);statementMap.put(sql, ps);batchResultMap.put(sql, new BatchResult(statementScope.getStatement().getId(), sql));}statementScope.getParameterMap().setParameters(statementScope, ps, parameters);ps.addBatch();++size;}

下面贴出修改后完整的代码，方便有同样需求的同学修改，只贴出内部类com.ibatis.sqlmap.engine.execution.SqlExecutor$Batch，com.ibatis.sqlmap.engine.execution.SqlExecutor没有做出任何修改

private static class Batch {private Map<String, PreparedStatement> statementMap = new HashMap<String, PreparedStatement>();private Map<String, BatchResult> batchResultMap = new HashMap<String, BatchResult>();private int size;/** * Create a new batch */public Batch() {size = 0;}/** * Getter for the batch size * @return - the batch size */@SuppressWarnings("unused")public int getSize() {return size;}/** * Add a prepared statement to the batch * @param statementScope - the request scope * @param conn - the database connection * @param sql - the SQL to add * @param parameters - the parameters for the SQL * @throws SQLException - if the prepare for the SQL fails */public void addBatch(StatementScope statementScope, Connection conn, String sql, Object[] parameters) throws SQLException {PreparedStatement ps = statementMap.get(sql);if (ps == null) {ps = prepareStatement(statementScope.getSession(), conn, sql);setStatementTimeout(statementScope.getStatement(), ps);statementMap.put(sql, ps);batchResultMap.put(sql, new BatchResult(statementScope.getStatement().getId(), sql));}statementScope.getParameterMap().setParameters(statementScope, ps, parameters);ps.addBatch();++size;}/** * TODO (Jeff Butler) - maybe this method should be deprecated in some release, * and then removed in some even later release. executeBatchDetailed gives * much more complete information. * <p/> * Execute the current session's batch * @return - the number of rows updated * @throws SQLException - if the batch fails */public int executeBatch() throws SQLException {int totalRowCount = 0;for (Map.Entry<String, PreparedStatement> iter : statementMap.entrySet()) {PreparedStatement ps = iter.getValue();int[] rowCounts = ps.executeBatch();for (int j = 0; j < rowCounts.length; j++) {if (rowCounts[j] == Statement.SUCCESS_NO_INFO) {// do nothing} else if (rowCounts[j] == Statement.EXECUTE_FAILED) {throw new SQLException("The batched statement at index " + j + " failed to execute.");} else {totalRowCount += rowCounts[j];}}}return totalRowCount;}/** * Batch execution method that returns all the information * the driver has to offer. * @return a List of BatchResult objects * @throws BatchException (an SQLException sub class) if any nested *             batch fails * @throws SQLException if a database access error occurs, or the drive *             does not support batch statements * @throws BatchException if the driver throws BatchUpdateException */@SuppressWarnings({ "rawtypes", "unchecked" })public List executeBatchDetailed() throws SQLException, BatchException {List answer = new ArrayList();int i = 0;for (String sql : statementMap.keySet()) {BatchResult br = batchResultMap.get(sql);PreparedStatement ps = statementMap.get(sql);try {br.setUpdateCounts(ps.executeBatch());} catch (BatchUpdateException e) {StringBuffer message = new StringBuffer();message.append("Sub batch number ");message.append(i + 1);message.append(" failed.");if (i > 0) {message.append(" ");message.append(i);message.append(" prior sub batch(s) completed successfully, but will be rolled back.");}throw new BatchException(message.toString(), e, answer, br.getStatementId(), br.getSql());}++i;answer.add(br);}return answer;}/** * Close all the statements in the batch and clear all the statements * @param sessionScope */public void cleanupBatch(SessionScope sessionScope) {for (Map.Entry<String, PreparedStatement> iter : statementMap.entrySet()) {PreparedStatement ps = iter.getValue();closeStatement(sessionScope, ps);}statementMap.clear();batchResultMap.clear();size = 0;}}