mysql批量处理

批量添加

 <insert id="add">
    insert into application_sp(application_num,subject_id,group_id, currency, medium)
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.applicationNum},#{item.subjectId},#{item.groupId},#{item.currency},#{item.medium})
    </foreach>
</insert>

批量处理有就修改，没有就添加

<insert id="addSubject">
    insert into application_subject(application_num, subject_id, group_id) values
    <foreach collection="list" item="item" separator="," >
        (#{item.applicationNum},#{item.subjectId},#{item.groupId})
    </foreach>
    on duplicate key update
    subject_id = values(subject_id)
</insert>

批量修改

<update id="updateBank" parameterType="java.util.List">
<foreach collection="bankInfoList" item="item" index="index" separator=";" >
    update bank_info set
    bank_account =#{item.bankAccount},
    bank_name =#{item.bankName},
    account =#{item.account},
    bank_address =#{item.bankAddress},
    update_time = now()
    where id = #{item.id}
</foreach>

批量查询

<select id="getGroupIdBySp" resultType="java.lang.String">
    select distinct(group_id) from application_sp where group_id in (select group_id from sp_group where sp_manage_id = #{spManageId})
    <if test="applicationNumList != null and applicationNumList.size()>0">
        and application_num in
        <foreach item="applicationNum" collection="applicationNumList" index="index" open="(" separator="," close=")">
            #{applicationNum}
        </foreach>
    </if>
</select>

批量查询，查询条件为嵌套list

mediunFun ： [
{
“function”: 1,
“mediums”: [1,2,4]
},
{
“function”: 2,
“mediums”: [1,5]
},
{
“function”: 5,
“mediums”: [3]
}
]

select currency,medium,num, sp_function ,sp_alias, currency_accept ,
server_param,client_param,si.display_name,icon_url,country,si.display_name
from sp_info si left join sp_manage sm on si.sp_manage_id = sm.id where 1=1
<if test="spManageId>0">
    and sp_manage_id = #{spManageId}
</if>
<if test="country!=''">
    and country = #{country}
</if>
<if test="mediumFun!=null and mediumFun.size()>0">
    AND (sp_function,medium) in
    <foreach item="item" collection="mediumFun" open="(" close=") " separator=",">
        <foreach collection="item.mediums" item="medium" separator=",">
            (#{item.function},#{medium})
        </foreach>
    </foreach>
</if>