MyBatis Batch Insert/Update For Oracle

Question

I've recently started learning to use myBatis.I am now facing such a scenario, I need to constantly fetch a new list of Objects through WebService, then for this list, I need to insert/update each object into the oracle DB table through myBatis.

The tricky part is, I cannot simply do a batch insert every time, because some of the objects might already exist in DB, for these records, I need to update the fields of them instead of a new insertion.

My current solution might be very stupid, using Java, build the list of Object from webservice, loop through each of them, do a myBatis select, if it is not a null(already exists in the db), then do a myBatis update; otherwise, do a myBatis insert for this new object.

The function is achieved. But my technical lead says it is very low-efficient, since doing a for loop using Java and insert/update one by one will consume a lot of system resource. He advised me to do batch insert using myBatis by passing a list of objects in.

Batch insertion in myBatis is straightforward, however, since I am not purely inserting(for existing records I need to do update), I don't think batch insert is appropriate here. I've googled a while for this, and realized maybe I will need to use "merge" instead of "insert" (for Oracle).

The examples I googled out for merge in myBatis is only for one object, not in a batch. Thus I want to find out whether experts could offer me some examples on how to do a batch-merge in MyBatis( The correct way to write a Mapper)?

Solution 1

In my case also there is same scenario. I used for loop to check whether this record exists in databse or not and then according to that I added this object in to two arraylist for insert or update. And then used batch for insert and update after for loop for that to list.

here is ex. for update according to different where condition

1] this is for update

<foreach collection="attendingUsrList" item="model"  separator=";">
    UPDATE parties SET attending_user_count = #{model.attending_count}
    WHERE  fb_party_id = #{model.eid}  
</foreach>

2] this is for insert

<insert id="insertAccountabilityUsers" parameterType="AccountabilityUsersModel" useGeneratedKeys="false">
    INSERT INTO accountability_users 
        (
            accountability_user_id, accountability_id, to_username,
            record_status, created_by, created_at, updated_by, updated_at
        ) 
    VALUES
    <foreach collection="usersList" item="model" separator=","> 
        (           
            #{model.accountabilityUserId}, #{model.accountabilityId}, #{model.toUsername}, 
            'A', #{model.createdBy}, #{model.createdAt}, #{model.updatedBy}, #{model.updatedAt}     
        )
    </foreach>
</insert>

In dao method declare as

void insertAccountabilityUsers(@Param("usersList") List<AccountabilityUsersModel> usersList);

Update

Here is my batch session code

public static synchronized SqlSession getSqlBatchSession() {
    ConnectionBuilderAction connection = new ConnectionBuilderAction();
    sf = connection.getConnection();
    SqlSession session = sf.openSession(ExecutorType.BATCH);
    return session;
}

SqlSession session = ConnectionBuilderAction.getSqlSession(); 

Actually I already given full example here for this question

Solution 2

The accepted answer is not the recommended way of handling batch operations. It does not show true batch statements since the batch executor mode should be used when opening a session. See this post in which a code contributor recommended that the proper way to batch update (or insert) is to open a session in batch mode and repeatedly call update (or insert) 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();
    }
}

Do not use foreach in your update/insert and ensure that it only updates/inserts a single record. I was running into unsolvable oracle errors by doing it according to the accepted answer (invalid character, statement not ended, etc.). As the linked post indicates, the update (or insert) shown in the accepted answer is actually just a giant sql statement.

Comments

[Kevin]

@Sammer Kazi, sure. But you are doing update and insert on their own separately?

[Sameer Kazi]

yes after for loop ends I run two queries for batch update and batch insert. and in for my for loop I only check is this record exit in db or not. In above example insert and update query are only for example here they are not from same for loop.

[Kevin]

oh, I kinda understand. At java level, you loop through the list, then you create two sub-lists, sublist1 contains the objects that are in DB, sublist2 contains objects that are not. Then for sublist1, you do a batch update, for sublist2 you do a batch insertion?

[Sameer Kazi]

yes but if list size is zero then do not call insert or update method otherwise this will raise syntax error.

[Kevin]

@Sammer Kazi, how to you check for existence? You will use MyBatis select to see if it is null or not?

[Kevin]

let us continue this discussion in chat

[Jaydeep]

How about concurrency (obviously optimistic) check when using Batch Update in such a way ?

[Phate]

Does not work...gives bad SQL grammar []; nested exception is java.sql.BatchUpdateException: ORA-00933 bad termination of command

[Eric]

This is cool. And when use with spring, could get such sql session via sqlSessionFactory.openSession(ExecutorType.BATCH, false), which use batch mode, and disable auto commit.

[heemin]

Provided insert solution does not work for Oracle DB.

[rimsky]

Thanks @Eric Wang, I wasn't sure who had done it at the time and was hoping someone would pipe up. The reputation history doesn't seem to show names. However, it seems that perhaps Sameer Kazi did because the downvote occurred one minute after he edited his own post.

[rimsky]

-1 @Sameer Kazi Did you downvote my answer? If so, why? Although your answer creates a batch session, it should not use foreach. See the link I provided.

[Sameer Kazi]

hey this is not batch update you are calling update method in for loop means database call in for loop so this is not batch update, In selected answer only one database call and update.

[Saorikido]

This is not a batch insert/update, it's a giant sql statement. see here github.com/mybatis/mybatis-3/issues/484

[sphinks]

Update and insert can not be done this way for Oracle.

[user1955401]

I don't know. This code example here: ORACLE INSERT ALL, shows exactly the syntax, and if you use an insert with a foreach, it does produce the correct insert statement as i traced the sql with Mybatis 3.5.7. It looks fine. one insert statement, the parameters, 3 rows updated, so the @SamirKamzi code should be fine. The only issue would be you don't get the keys of the rows you insert. With a batch executor you'd get that.