mybatis 批量插入以及merge into用法 - yanghenan19870513的专栏 - CSDN博客

安宇雨 - 随手采集
2019-08-27 21:45:13
随手采集
0000-未整理-等待研究

一、mybiats foreach标签

<insert id="insertBatch" parameterType="List">INSERT INTO TStudent(name,age)<foreach collection="list" item="item" index="index" open="("close=")"separator="union all">SELECT #{item.name} as a, #{item.age} as b FROM DUAL</foreach></insert>

<insert id="insertBatch">    INSERT INTO t_user            (id, name, del_flag)    VALUES    <foreach collection ="list" item="user" separator =",">         (#{user.id}, #{user.name}, #{user.delFlag})    </foreach ></insert>

<update id="mergeBank" parameterType="java.util.List">        merge into T_MY_BANK a        using        (        <foreach collection="list" index="index" item="item" open=""                 close="" separator="union all">            SELECT            sys_guid() as id,            #{item.bankCode,jdbcType=VARCHAR} as bankCode,            #{item.bankName,jdbcType=VARCHAR} as bankName,            #{item.bankName,jdbcType=VARCHAR} as subBankName,            #{item.bankId,jdbcType=VARCHAR} as bankId,            '1' as  isOverseas            FROM dual        </foreach>        ) b        on (        a.BANK_ID = b.bankId        )        when        matched then        UPDATE SET        a.BANK_CODE = b.bankCode,        a.BANK_NAME=b.bankName,        a.SUB_BANK_NAME = b.subBankName,        a.UPDATE_DT=sysdate        when        not matched then        INSERT(        a.ID,        a.BANK_CODE,        a.BANK_NAME,        a.SUB_BANK_NAME,        a.BANK_ID,        a.CREATE_DT,        a.UPDATE_DT        ) VALUES(        b.id,        b.bankCode,        b.bankName,        b.subBankName,        b.bankId,        sysdate,        sysdate        )    </update>

<insert id="insert" parameterType="com.pojo.UserInfo">    <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">        SELECT to_char(sysdate,'yyyymmdd')||USER_SEQ.nextval AS ID FROM dual    </selectKey>    MERGE INTO user_info ui     USING (SELECT #{userId} user_id FROM dual) d      ON (ui.userId = d.user_id)      WHEN matched THEN           UPDATE SET ui.name = #{name}    WHEN not matched THEN          INSERT (ID,user_id,name    )VALUES(        #{id,jdbcType=VARCHAR},#{userId,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR}    )</insert>

<update id="updateBatch" parameterType="java.util.List"> begin  <foreach collection="list" item="item" index="index" separator=";" >     UPDATE USER_INFO    <set>        <if test="null != item.name">            <![CDATA[name = #{item.name,jdbcType=VARCHAR}]]>        </if>    </set>    <![CDATA[        WHERE  ID = #{item.id,jdbcType=VARCHAR}     ]]>  </foreach>   ;end;</update>

Original url: Access
Created at: 2019-08-27 21:45:12
Category: default
Tags: none

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到