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

问题描述

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

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

我当前的解决方案可能非常愚蠢，使用Java，从webservice构建Object列表，循环每个人都做一个myBatis选择，如果它不是null（已经存在于db中），那么做一个myBatis更新;否则，为这个新对象执行myBatis插入。

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

myBatis中的批量插入很简单，但是，因为我不是纯插入（对于现有的记录我需要做更新），我不认为批量插入在这里是合适的。我已经google了一段时间，并意识到我可能需要使用merge而不是insert（对于Oracle）。

我搜索的例子for myBatis中的合并仅适用于一个对象，而不是批处理。因此，我想知道专家是否可以提供一些关于如何在MyBatis中进行批量合并的示例（编写Mapper的正确方法）？

解决方案

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

这里是ex。根据不同的条件进行更新

1]这是用于更新

 < foreach collection =attendingUsrListitem =modelseparator =;> 
 UPDATE party SET atte_user_count =＃{model.attending_count} 
 WHERE fb_party_id =＃{model.eid} 
< / foreach> 
  

2]这是插入

 < insert id =insertAccountabilityUsersparameterType =AccountabilityUsersModeluseGeneratedKeys =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 =usersListitem =modelseparator =，> 
（
＃{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）; 
  

更新

<这是我的批处理会话代码

  public static synchronized SqlSession getSqlBatchSession（）{
 ConnectionBuilderAction connection = new ConnectionBuilderAction（ ）; 
 sf = connection.getConnection（）; 
 SqlSession session = sf.openSession（ExecutorType.BATCH）; 
返回会话; 
} 
 
 SqlSession session = ConnectionBuilderAction.getSqlSession（）; 
  

实际上我已经在这里提供了完整的例子来解决这个问题

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

解决方案

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.

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

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