java mapper批量更新、插入、查询

1、组合字段批量查询

    <select id="batchSelect" resultMap="BaseResultMap">
        select
        <include refid="BaseColumnList"/>
        from good
        where mark = 1 and (shop_id,sku_id) in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            (#{item.shopId},#{item.skuId})
        </foreach>
    </select>

2、根据id批量更新

<update id="batchUpdate" parameterType="java.util.List">

        update good
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="sku_id =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.skuId!=null">
                        when id=#{item.id} then #{item.skuId}
                    </if>
                    <if test="item.skuId == null">
                        when id=#{item.id} then good.sku_id
                    </if>
                </foreach>
            </trim>
            <trim prefix="shop_id =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.shopId!=null">
                        when id=#{item.id} then #{item.shopId}
                    </if>
                    <if test="item.shopId == null">
                        when id=#{item.id} then good.shop_id
                    </if>
                </foreach>
            </trim>
            <trim prefix="shop_rate =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.shopRate!=null">
                        when id=#{item.id} then #{item.shopRate}
                    </if>
                    <if test="item.shopRate == null">
                        when id=#{item.id} then good.shop_rate
                    </if>
                </foreach>
            </trim>
            <trim prefix="technician_rate =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.technicianRate!=null">
                        when id=#{item.id} then #{item.technicianRate}
                    </if>
                    <if test="item.technicianRate == null">
                        when id=#{item.id} then good.technician_rate
                    </if>
                </foreach>
            </trim>

            <trim prefix="update_user_id =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.updateUserId!=null">
                        when id=#{item.id} then #{item.updateUserId}
                    </if>
                    <if test="item.updateUserId == null">
                        when id=#{item.id} then good.update_user_id
                    </if>
                </foreach>
            </trim>

            <trim prefix="update_user_name =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.updateUserName!=null">
                        when id=#{item.id} then #{item.updateUserName}
                    </if>
                    <if test="item.updateUserName == null">
                        when id=#{item.id} then good.update_user_name
                    </if>
                </foreach>
            </trim>
            <trim prefix="update_time =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.updateTime!=null">
                        when id=#{item.id} then #{item.updateTime}
                    </if>
                    <if test="item.updateTime == null">
                        when id=#{item.id} then good.update_time
                    </if>
                </foreach>
            </trim>
        </trim>
        where mark = 1
        and id in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

3、批量插入数据

    <insert id="insertBatchSomeColumn" parameterType="java.util.List">
        insert into good(
        sku_id,
        shop_id,
        shop_rate,
        technician_rate,
        type,
        update_user_id,
        update_user_name,
        create_time,
        update_time
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.skuId},
            #{item.shopId},
            #{item.shopRate},
            #{item.technicianRate},
            #{item.type},
            #{item.updateUserId},
            #{item.updateUserName},
            now(),
            now()
            )
        </foreach>

    </insert>