MyBatis batch update for oracle

Question

I am trying to do the batch update in myBatis with oracle Database, where i have to update 10K+ records. But when i use below

I am getting exception says 'invalid character'. It is working fine when i do single update statement multiple times. Please help with your example if you already tried this.

<update id="batchUpdate">
                <foreach collection="empList" item="employee" separator=";">
                  UPDATE Employee
                   SET  EMP_JOBTITLE = #{employee.jobTitle},
                        EMP_STATUS = #{employee.status}
                        WHERE Employee_ID = #{employee.empId}
                  </foreach>
    </update>

Possible duplicate of [MyBatis Batch Insert/Update For Oracle](http://stackoverflow.com/questions/23486547/mybatis-batch-insert-update-for-oracle) — Sameer Kazi, Sep 06 '16 at 08:57

score 0 · Answer 1 · edited Jan 08 '15 at 19:27

0

If you use Oracle:

<update id="batchUpdate">
call 
begin
<foreach collection="empList" item="employee" close=";" eparator=";">
                  UPDATE Employee
                   SET  EMP_JOBTITLE = #{employee.jobTitle},
                        EMP_STATUS = #{employee.status}
                        WHERE Employee_ID = #{employee.empId}
</foreach>
end
</update>

edited Jan 08 '15 at 19:27

Opal

81,889
28
189
210

answered Jan 08 '15 at 19:19

peter111

1

This gives me ORA-06576: not a valid function or procedure name – rimsky Mar 25 '15 at 18:37

score -1 · Answer 2 · answered Mar 25 '15 at 19:16

I was not able to resolve that error, but came across this post in which a code contributor recommended that the proper way to batch update is to open a session in batch mode and repeatedly call update for a single record.

Here's what works for me:

public void updateRecords(final List<GisObject> objectsToUpdate) {
    final SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    try {
        final GisObjectMapper mapper = sqlSession.getMapper(GisObjectMapper.class);
        for (final GisObject gisObject : objectsToUpdate) {
            mapper.updateRecord(gisObject);
        }
        sqlSession.commit();
    } finally {
        sqlSession.close();
    }
}

Then, remove the foreach in your update and modify it to update a single record (remove the "employee.")

MyBatis batch update for oracle

2 Answers2