How to Efficiently Perform Batch Upserts (Insert/Update) in MyBatis with Oracle?

Batch Upsert (Insert/Update) in MyBatis for Oracle

Context:

In MyBatis, you need to insert or update a list of objects into an Oracle database while handling potential duplicates. The current solution involves a manual loop and separate insert/update operations, which is inefficient.

Solution:

To optimize the process, consider using MyBatis's batching capabilities and Oracle's "merge" functionality. However, batching merge is not directly supported in MyBatis.

Recommended Approach:

To achieve batch upsert, use the following steps:

1. Use a Batch Executor: Open a MyBatis session in batch mode using ExecutorType.BATCH.
2. Iterate Over Records: Loop through the list of objects.
3. Call Single Record Update/Insert: For each object, call the appropriate update/insert method on the mapper, which should operate on a single record.
4. Commit Changes: Finally, commit the changes within the session.

Example Code:

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();
    }
}

Note:

Ensure that the update/insert methods in the mapper are appropriately configured for single record operations. Using foreach or generating a giant SQL statement for batch processing is not recommended.

The above is the detailed content of How to Efficiently Perform Batch Upserts (Insert/Update) in MyBatis with Oracle?. For more information, please follow other related articles on the PHP Chinese website!