Sqlserver mybatis 封装批量操作

批量插入

<insert id="insertBatches" parameterType="java.util.List">
    INSERT INTO tabelName(
    reason,
    status
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.reason,jdbcType=VARCHAR},
        #{item.status,jdbcType=SMALLINT}
        )
    </foreach>
</insert>

in

<select id="queryList" resultType="java.util.Map">
    SELECT *
    FROM tabelName
    WHERE
    status=1
    and id IN
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

Union All

 insert into DISPOSAL_PROCESS (ID, NAME, PLAN_ID,PARENT_ID, DIGEST, STEP,SORT)

        <foreach collection="list" item="process" index="index" separator="UNION ALL">
            select
            #{process.id,jdbcType=VARCHAR},
            #{process.name,jdbcType=VARCHAR},
            #{process.planId,jdbcType=VARCHAR},
            #{process.parentId,jdbcType=VARCHAR},
            #{process.digest,jdbcType=VARCHAR},
            #{process.step,jdbcType=VARCHAR},
            #{process.sort,jdbcType=DECIMAL}

        </foreach>

    </insert>

发表评论