mybatis

merge into 能够以一次请求实现类似于insertOrUpdate的操作。减少请求次数。


<insert id="insertOrUpdate" parameterType="ArrayList">
    merge into P_MAP_TENANT_USER r using
    (
    <foreach collection="pMapTenantUsers" index="index" item="item" open="" close="" separator="union">
        select
        #{item.tenantId} as TENANT_ID,
        #{item.userId} as USER_ID,
        #{item.roleType} as ROLE_TYPE
        from dual
    </foreach>
    )tmp
    on (
    tmp.TENANT_ID = r.TENANT_ID
    and tmp.USER_ID = r.USER_ID
    )
    when matched THEN
    update
    <set>
        r.ROLE_TYPE = tmp.ROLE_TYPE,
    </set>
    when not matched THEN
    insert
    <trim prefix="(" suffix=")" suffixOverrides="," >
        TENANT_ID ,
        USER_ID ,
        ROLE_TYPE
    </trim>
    <trim  prefix="values (" suffix=")" suffixOverrides=",">
        tmp.TENANT_ID,
        tmp.USER_ID,
        tmp.ROLE_TYPE
    </trim>
</insert>