MyBatis批量插入/更新Oracle。

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.

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

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.

棘手的是，我不能每次都简单地进行批插入，因为有些对象可能已经存在于DB中，对于这些记录，我需要更新它们的字段，而不是新的插入。

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，从webservice构建对象列表，遍历每一个对象，执行myBatis选择，如果它不是null(已经存在于db中)，然后进行myBatis更新;否则，为这个新对象执行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.

函数实现。但是我的技术主管说它是非常低效率的，因为使用Java执行一个for循环并逐个插入/更新将消耗大量的系统资源。他建议我使用myBatis通过传入对象列表来进行批量插入。

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中批量插入是很简单的，因为我不是纯插入(对于我需要更新的现有记录)，我不认为批量插入是合适的。我在谷歌上搜索了一段时间，发现也许我需要使用“merge”而不是“insert”(对于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)?

我在myBatis中搜索merge的示例只针对一个对象，而不是批处理。因此，我想知道专家们是否可以给我一些如何在MyBatis中执行批处理合并的例子(编写映射器的正确方法)?

2 个解决方案

#1

In my case also there is same scenario. I used for loop to check is this record is exist 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.

在我的例子中也有同样的情况。我使用for循环检查这个记录是否存在于databse中，然后根据这个结果，我将这个对象添加到两个arraylist中进行插入或更新。然后使用批处理来插入和更新for循环之后的列表。

here is ex. for update according to different where condition

这里是ex。根据不同的情况更新。

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

在dao方法中声明为

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

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

2 个解决方案

#1

更多相关文章

随机推荐