Fastest Way To Update Huge Number Of Rows With Input Param List In Mybatis To Oracle Db

I'm updating huge amount of data by passing a variable List in MyBatis to Oracle DB. Methods from this link are not efficient enough for me, the ways to commit update sql query li

Solution 1:

Using batch executor is the recommended way, but you need to do it properly. Two issues that I noticed.

1. Setting a proper batch size is important. The linked answer sends all the data at the end which is not efficient very much.
2. Using ${} to reference parameters makes each statement unique and prevents the driver from reusing the statement (the benefit of batch executor is lost, basically). See this FAQ for the difference between #{} and ${}.

Here is a typical batch operation using MyBatis. As the best batchSize depends on various factors, you should measure the performance using the actual data.

intbatchSize=1000;
try (SqlSessionsqlSession= sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  YourMappermapper= sqlSession.getMapper(YourMapper.class);
  intsize= list.size();
  for (inti=0; i < size;) {
    mapper.update(list.get(i));
    i++;
    if (i % batchSize == 0 || i == size) {
      sqlSession.flushStatements();
      sqlSession.clearCache();
    }
  }
  sqlSession.commit();
}

And here is an efficient version of the update statement.

<updateid="update">
  UPDATE <includerefid="tableName" />
  SET
    item_price = #{item.price},
    update_time = #{item.updateTime}
  WHERE id = #{item.id}
</update>