MyBatis 批量插入/更新 Oracle [英] MyBatis Batch Insert/Update For Oracle

问题描述

最近开始学习使用myBatis，我现在面临这样的场景，我需要不断地通过WebService获取一个新的Object列表，然后对于这个列表，我需要将每个对象插入/更新到oracle中通过 myBatis 的 DB 表.

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.

我目前的解决方案可能非常愚蠢，使用Java，从webservice构建对象列表，循环遍历每个对象，做一个myBatis select，如果它不是null(已经存在于db中)，然后做一个myBatis 更新；否则，为这个新对象做一个 myBatis 插入.

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.

功能实现.但是我的技术负责人说它非常低效，因为使用Java进行for循环并逐个插入/更新会消耗大量系统资源.他建议我通过传入对象列表来使用 myBatis 进行批量插入.

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.

myBatis 中的批量插入很简单，但是，由于我不是纯粹的插入(对于我需要更新的现有记录)，我认为这里不适合批量插入.我为此搜索了一段时间，并意识到我可能需要使用合并"而不是插入"(对于 Oracle).

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).

我在 myBatis 中搜索合并的示例仅适用于一个对象，而不是批处理.所以想请教各位高手能不能给我举个例子，如何在MyBatis中进行batch-merge(写Mapper的正确方法)?

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)?

推荐答案

在我的情况下也有相同的情况.我使用 for 循环来检查该记录是否存在于数据库中，然后根据我将此对象添加到两个数组列表中以进行插入或更新.然后在for循环后使用批处理进行插入和更新以列出.

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.

这里是前.根据不同的where条件进行更新

here is ex. for update according to different where condition

1] 这是为了更新

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

2] 这是用于插入

<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>

在dao方法中声明为

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

更新

这是我的批处理会话代码

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

实际上我已经在这里给出了完整的例子问题

这篇关于MyBatis 批量插入/更新 Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！