Mybatis 批量添加 foreach 用法 批量删除map用法

mybatis 批量入库纪要：

　　Map入库：

　　传入参数为map类型，map里面存放list集合，如：

{
　　busId=[
　　　　40535,
　　　　40535
　　],
　　commandType=0x17,
　　routeId=22942,
　　commandBody=0x00;0;321;,
　　terminalId=0,
　　serviceId=0
}
　　sql如下：

<insert id="sendMsgCommand" >
    insert into send_command_table
      (
        command_id,
        command_type,
        route_id,
        bus_id,
        terminal_id,
        service_id,
        command_body
       )
    select seq_send_command_id.nextval,bus.*  from
        <foreach collection="busId" item="myBus" open="(" separator="union all" close=")">
            select
            #{commandType,jdbcType=VARCHAR},
            #{routeId,jdbcType=VARCHAR},
            #{myBus,jdbcType=VARCHAR},
            terminal_code,
            #{serviceId,jdbcType=VARCHAR},
            #{commandBody,jdbcType=VARCHAR}
            from terminal_master_table  where bus_id  = #{myBus,jdbcType=VARCHAR}
        </foreach>
        bus
</insert>

　　foreach中 collection的值是map中list集合的键

 

　　传入参数是list集合，list中存放map或者对象，则：

  sql如下：

<insert id="sendMsgCommand" >
    insert into send_command_table
      (
        command_id,
        command_type,
        route_id,
        bus_id,
        terminal_id,
        service_id,
        command_body
       )
    select seq_send_command_id.nextval,bus.*  from
        <foreach collection="list" item="item" open="(" separator="union all" close=")">
            select
            #{item.commandType,jdbcType=VARCHAR},
            #{item.routeId,jdbcType=VARCHAR},
            #{item.myBus,jdbcType=VARCHAR},
            terminal_code,
            #{item.serviceId,jdbcType=VARCHAR},
            #{item.commandBody,jdbcType=VARCHAR}
            from terminal_master_table  where bus_id  = #{item.myBus,jdbcType=VARCHAR}
        </foreach>
        bus
</insert>

　　foreach中 collection的值是list

 

批量删除

 

 <delete id="del">
        delete from road_bill_info_table where ele_bill_id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">#{item,jdbcType=VARCHAR}</foreach>
        and run_date between p_time_patition.givenStartTime(#{runDate,jdbcType=VARCHAR})
        and p_time_patition.givenEndTime(#{runDate,jdbcType=VARCHAR})
    </delete>