使用Mybatis对mysql的增删改查批量操作示例

在使用批量更新之前 要先设置 mysql 支持 批量执行多条sql 在连接信息后加上[&allowMultiQueries=true]

删除

    <delete id="deleteGenTableColumnByIds" parameterType="Long">
        delete from gen_table_column where table_id in 
        <foreach collection="array" item="tableId" open="(" separator="," close=")">
            #{tableId}
        </foreach>
    </delete>

查询

示例: 排除多个状态

示例中 excludeStatus为数组 请注意数组情况下判空的方式

        <select id="uList" resultType="User">
				select *
				from user
		<where>
				<!-- 排除指定状态-->
            <if test="excludeStatus != null  and excludeStatus.length>0 ">
            	and status not in
                <foreach item="item" collection="excludeStatus" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>

示例: 指定多个状态

 <select id="selectUser" resultType="User">
        select *
        FROM user
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

新增

新增示例1:

参数: 多参数+数组

  public int setCargoBoxType(@Param("sourceId") Long sourceId, @Param("cargoBoxTypes") Integer[] cargoBoxTypes);

  <insert id="setCargoBoxType">
        insert into t_user(source_id,cargo_box_type) values
        <foreach item="cargoBoxType" index="index" collection="cargoBoxTypes" separator=",">
            (#{sourceId},#{cargoBoxType})
        </foreach>
    </insert>

注意java里的@param注解
可以看到 在循环的过程中参数:‘sourceId’ 是固定的
而参数:‘cargoBoxType’ 是每次循环而得到的

新增示例2:

参数: 集合

  public int setUserId(@Param("userIdList") List<User> userList);

 <insert id="setUserId">
        insert into t_user(user_id) values
        <foreach item="item" index="index" collection="userIdList" separator=",">
            (item.userId)
        </foreach>
    </insert>

循环过程中 item就代表是user对象了 所以item.userId就是获取这个对象的id

新增示例3:

<insert id="insertList">
    <foreach item="item" index="index" collection="auditList" separator=";">
        INSERT INTO glis_site_audit (illustrate, var_ext_call, var_own, type, carrier_id)
        VALUES
        (#{item.illustrate}, #{item.varExtCall}, #{item.varOwn}, #{item.type}, #{item.carrierId})
    </foreach>
</insert>

修改

修改示例1

<update id="updateList">
        <foreach separator=";" index="index" item="item" collection="list" close="" open="">
            update tb_activity
            set status=#{item.status}
            where id = #{item.id}
        </foreach>
    </update>

修改示例2

<update id="batchUpdate1" parameterType="java.util.List"> 
     
      update sys_group set level = null where level in
       <foreach separator="," index="index" item="item" collection="list" close=")" open="("> 
         #{item}
      </foreach> 
      
  </update>