mybatis中批量新增、修改、查询（数据库mysql）

1、批量新增

<!-- 批量新增-->
<insert id="batchSave" parameterType="java.util.List">
    insert into
    table_a
    (id,
    number)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id},
        #{item.number}
    </foreach>

2、批量修改

<!-- 批量修改 -->
<update id="batchUpdate" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update table_a
        <set>
            number= #{item.number}
        </set>
        where
        id= #{item.id}
    </foreach>
</update>

3、批量查询

list集合带多字段的批量查询

<!-- 批量查询 -->
<select id="batchQuery" parameterType="java.util.List" resultType="map">
    select
    id,
number,
name
    from
    table_a
    where
    <foreach collection="list" item="item" index="index" open="" close="" separator="or">
       id= #{item.id}
        and number= #{item.number}
    </foreach>
</select>

 

list集合带单字段的批量查询

<!-- 批量查询 -->
<select id="batchQuery" parameterType="java.util.List" resultType="map">
    select
    id,
number,
name
    from
    table_a
    where
id in 
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
      #{item.id}
    </foreach>
</select>

 

数组批量查询

<!-- 批量查询 -->
<select id="batchQuery" parameterType="java.util.String" resultType="map">
    select
    id,
number,
name
    from
    table_a
    where
id in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
   #{item}
</foreach>

</select>