使用Mybatis 3.4.6进行Oracle批更新并获得"-1"结果 [英] Using Mybatis 3.4.6 for Oracle Batch-Update and Got the "-1" result

问题描述

我的代码如下:

<update id="biz-update" parameterType="java.util.List">
    <foreach collection="list" index="index" item="item" open="begin" close=";end;" separator=";">
        update
        biz_tbl
        <set>
            freeze_amount = nvl(freeze_amount,0) + #{item.payAmount}
        </set>
        where
        id = #{item.cardId}
    </foreach>
</update>

当使用Integer时，我总是得到-1结果；使用Boolean时，我总是得到错误的结果 我像这样尝试application.yml:

And I always got the -1 result when using Integer or got the false result when using Boolean And I tried application.yml like this:

mybatis:
    configuration:
        default-executor-type: simple

似乎无关紧要. 那么，如何才能得到正确的oracle批处理更新结果?

it seems doens't matter. So, how can I got the right result of batch-update for oracle?

推荐答案

这不是批处理操作.
它试图通过单个PreparedStatement#execute()调用执行多条语句，而Oracle的JDBC驱动程序不支持它.(更正:该驱动程序支持它).
正确的方法是执行 real 批处理操作.

That is not a batch operation.
It's trying to execute multiple statements with a single PreparedStatement#execute() call and Oracle's JDBC driver does not support it (correction: the driver supports it).
The correct way is to perform a real batch operation.

mapper语句包含一个普通的UPDATE语句.

The mapper statement contains a plain UPDATE statement.

<update id="biz-update">
  update biz_tbl
  set freeze_amount = nvl(freeze_amount,0) + #{payAmount}
  where id = #{cardId}
</update>

下面的代码执行批处理操作.

The below code executes batch operation.

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
  for (YourItem item : list) {
    sqlSession.update("biz-update", item);
  }
  List<BatchResult> results = sqlSession.flushStatements();
  int totalNumberOfAffectedRows = Arrays.stream(results.get(0).getUpdateCounts()).sum();
  sqlSession.commit();
} finally {
  sqlSession.close();
}

• sqlSession#flushStatements()返回BatchResult的列表.在这种情况下，批处理中只有一个语句，因此列表大小为1.如果执行多个语句(例如，更新表A，然后将其插入表B)，则该列表可能包含多个BatchResult.
• BatchResult#getUpdateCounts()返回一个int数组.第一个元素(= int)是第一个UPDATE更新的行数，第二个元素是第二个UPDATE更新的行数，依此类推.
• 如果要更新许多行，则应间歇刷新语句.请参阅此答案以了解如何控制批量大小.
• sqlSession#flushStatements() returns a list of BatchResult. In this case, there is only one statement in the batch, so the list size is 1. If you execute multiple statements (e.g. update table A, then insert into table B), the list may contain multiple BatchResults.
• BatchResult#getUpdateCounts() returns an int array. The first element (=int) is the number of rows updated by the first UPDATE, the second element is the number of rows updated by the second UPDATE, and so forth.
• If you are updating many rows, you should flush statements intermittently. Please see this answer for how to control batch size.

这篇关于使用Mybatis 3.4.6进行Oracle批更新并获得"-1"结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！