mybatis批量插入或更新（SQL和oracle）

下面是批量插入(SQL)的代码

• 语法 ：insert into 表名（表字段，表字段） values < foreach> (#{},#{})< /foreatch>

<insert id="insertList" useGeneratedKeys="true" parameterType="java.util.List">
        insert into material_files(id,shop_id,file_name,suffix_name,file_size,md5,file_type,is_folder,
        url_address,real_name,parent_id,status_code,folder_type,created_on,updated_on,source,class1,class2,reg_idcard,reg_code,version)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.shopId},#{item.fileName},#{item.suffixName},#{item.fileSize},#{item.md5},#{item.fileType},#{item.isFolder},
            #{item.urlAddress},#{item.realName},#{item.parentId},#{item.statusCode},#{item.folderType},#{item.createdOn},#{item.updatedOn},
            #{item.source},#{item.class1},#{item.class2},#{item.regIdcard},#{item.regCode},#{item.version})
        </foreach>
</insert>

下面是批量更新(SQL)的代码

• separator 是用来分割每一组参数 用 “；”来分割
• 里面的if是判断是否为空

<update id="updateList" useGeneratedKeys="true" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
        update material_files
            <set>
            <if test="item.shopId!=null">
                shop_id= #{item.shopId},
            </if>
            <if test="item.fileName!=null">
                file_name=#{item.fileName},
            </if>
            <if test="item.suffixName!=null">
                suffix_name=#{item.suffixName},
            </if>
            <if test="item.fileSize!=null">
                file_size=#{item.fileSize},
            </if>
            <if test="item.md5!=null">
                md5=#{item.md5},
            </if>
            <if test="item.fileType!=null">
                file_type=#{item.fileType},
            </if>
            <if test="item.isFolder!=null">
                is_folder=#{item.isFolder},
            </if>
            <if test="item.urlAddress!=null">
                url_address=#{item.urlAddress},
            </if>
            <if test="item.realName!=null">
                real_name=#{item.realName},
            </if>
            <if test="item.parentId!=null">
                parent_id= #{item.parentId},
            </if>
            <if test="item.statusCode!=null">
                status_code=#{item.statusCode},
            </if>
            <if test="item.folderType!=null">
                folder_type=#{item.folderType},
            </if>
            <if test="item.source!=null">
                source=#{item.source},
            </if>
            <if test="item.class1!=null">
                class1=#{item.class1},
            </if>
            <if test="item.class2!=null">
                class2=#{item.class2},
            </if>
            <if test="item.regIdcard!=null">
                reg_idcard=#{item.regIdcard},
            </if>
            <if test="item.regCode!=null">
                reg_code=#{item.regCode},
            </if>
            <if test="item.version!=null">
                version=#{item.version},
            </if>
            created_on=#{item.createdOn},updated_on=#{item.updatedOn}
        </set>
            where id =#{item.id}
        </foreach>
</update>

下面是批量插入(oracle)的代码

 Long batchInsertData(@Param("list")List<LifeMinisterImportDataReqVo> list);

<insert id="batchInsertData">
        insert into JDTS.IMPROT_LT_YB_KM(BATCH_CODE,SERVE_PRO_CODE,SERVE_PRO_NAME,SUPPLIER_NAME,CARD_NUM,CARD_PASS,CARD_EFFECT_SDATE,CARD_EFFECT_EDATE,INSERT_TIME,INSERT_BY,CARD_STATUS)
        (
        <foreach collection="list" item="item" index="index" separator="union all">
            select
            #{item.batchCode,jdbcType=VARCHAR},
            #{item.serveProCode,jdbcType=VARCHAR},
            #{item.serveProName,jdbcType=VARCHAR},
            #{item.supplierName,jdbcType=VARCHAR},
            #{item.cardNum,jdbcType=VARCHAR},
            #{item.cardPass,jdbcType=VARCHAR},
            #{item.cardEffectSdate,jdbcType=TIMESTAMP},
            #{item.cardEffectEdate,jdbcType=TIMESTAMP},
            sysdate,
            #{item.insertName,jdbcType=TIMESTAMP},
            '10'
            from dual
        </foreach>
        )
    </insert>

下面是批量删除(oracle)的代码

    void delectByCardNum(@Param("list") List<String> list,@Param("proCode")String proCode);

	<delete id="delectByCardNum">
        delete from JDTS.IMPROT_LT_YB_KM where SERVE_PRO_CODE =#{proCode} and CARD_NUM in
            <foreach collection="list" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
    </delete>