mybatis 多条件批量删除整理

Dear 丶 2022-03-09 17:46 571阅读 0赞

批量增加

<insert id="batchSaveUser">
            insert into t_user (user_name,sex) values
            
            
            <foreach collection="users" item="u" separator=",">
                (#{u.userName},#{u.sex})
            </foreach>
        </insert>

批量删除

<delete id="batchDeleteUser">
            delete from t_user where id in (
            <foreach collection="ids" item="id" separator=",">
                #{id}
            </foreach>
            )  
    
     </delete>

<delete id="batchDeleteUser">
            delete from t_user where id in 
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
    
    </delete>

复杂条件批量删除

<delete id="deleteByLogic"  parameterType = "java.util.List">
         delete from user where id > 2000
             or id in
          <foreach collection="list"  item="item" open="(" separator="," close=")"  >
               #{item}
          </foreach>
    </delete>

复杂条件批量删除 建议增加联合索引

<delete id="batchDeleteByCondition">
            delete from test where
            <foreach collection="list" item="item" separator=" or " index="index">
                (name = #{item.name} and area_code = #{item.areaCode} and type = #{item.type})
            </foreach>
        </delete>

复杂条件批量删除 IN操作

<delete id="deleteBatch" parameterType="java.util.List">
        delete from test
        where (PRSNLID, ENTID) in
        <foreach item="item" index="index" collection="list" separator="," open="(" close=")">
    (#{item.prsnlid},#{item.entid})
    	</foreach>
    </delete>