iBatis batch处理那些事
来源:互联网 发布:方舟2017优化好了吗 编辑:程序博客网 时间:2024/05/22 06:05
昨天应同事要求在框架中(Spring+iBatis2.3.4)加入Batch处理,于是满足之,由于需要更灵活并且不想为批量插入、批量更新、批量删除等操作单独写对应的方法,于是写了这样的一个方法
- public Object batchExecute(final CallBack callBack) {
- Object result = getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {
-
- @Override
- public 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;
- }
- @Override
- public 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;
- }
- }
- public Object batchExecute(final CallBack callBack) {
- Object result = getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {
- @Override
- public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
- executor.startBatch();
- Object obj = callBack.execute(new IbatisSqlExecutor(executor));
- executor.executeBatch();
- return obj;
- }
- });
- return result;
- }
- 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;
- }
- @Override
- public 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;
- }
- });
- // 以下代码来自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++;
- }
- 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;
- }
- });
- 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;
- }
- 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;
- }
- }
0 0
- iBatis batch处理那些事
- iBatis batch处理那些事
- IBatis批量处理那些事
- iBatis框架batch处理优化
- iBatis框架的batch处理
- iBatis框架batch处理优化
- iBatis框架batch处理优化【摘】
- iBATIS框架batch处理优化浅析
- iBATIS框架batch处理优化浅析
- iBatis框架batch处理问题分析及优化
- 一个iBatis框架进行batch处理的问题
- IBATIS batch用法探究
- iBatis batch使用(转载)
- 批量处理(Batch processing
- ibatis的batch的使用的思考
- BATCH处理batchjob的逻辑
- 清除系统垃圾文件Batch处理
- MySQL使用Batch批量处理
- windows下 pycharm开发spark
- Java多线程与并发(三)之死锁
- struts2 中,如何覆盖默认的复杂格式的错误消息
- 开源许可证GPL、BSD、MIT、Mozilla、Apache和LGPL的区别
- 树——二叉树层序遍历的不同方法
- iBatis batch处理那些事
- 升级Mac自带svn版本
- 'Add.jsp' 新建文件(夹)
- c++之多态性(动态绑定)
- 生产者-消费者问题(The producer-consumer problem)
- QT 显示动态gif图片 (release版)
- ORM框架简介
- 系统分区
- APUE,TLPI,UNP读书笔记——linux IO模型以及IO复用