mybatis 批量插入和批量更新方法小记

1.批量插入 insert

<insert id="insertBatch" parameterType="List">
        insert into t_tool(place,material_name,material_class,wrench_model,num_code,
        existing,receive,seal,scrap,amount,create_user,create_date,update_user,update_date,delete_flag,price,shelf)
        values
        <foreach item="row" collection="list" open="" separator="," close="">
            (#{row.place},
            #{row.materialName},#{row.materialClass},
            #{row.wrenchModel},#{row.numCode},
            #{row.existing}, #{row.receive},
             #{row.seal},
            #{row.scrap}, #{row.amount},#{row.createUser},#{row.createDate},
            #{row.updateUser},#{row.updateDate},0,
            #{row.price},
            #{row.shelf})
        </foreach>
</insert>

2.批量更新 update

2.1

更新时需要在数据库连接配置时设置允许批量查询 : allowMultiQueries=true

spring:
  application:
    #服务名称
    name: tool-service
  datasource:
    url: jdbc:mysql://${mysql-host:localhost}:${mysql-port:3306}/${mysql-database-mydata:mydata}?useUnicode=true&characterEncoding=utf-8&&allowMultiQueries=true&serverTimezone=GMT%2B8
    username: ${mysql-username:root}
    password: ${mysql-passwd:123456}
    type: com.alibaba.druid.pool.DruidDataSource

2.2

mapper.xml更新代码

    <update id="updateBatchMaterial" parameterType="List">
        <foreach collection="list" item="row" open="" separator=";" close="">
            update t_tool
            set
            place = #{row.place},
            material_name = #{row.materialName},
            material_class = #{row.materialClass},
            num_code = #{row.numCode},
            wrench_model = #{row.wrenchModel},
            price = #{row.price},
            shelf = #{row.shelf}
            where id = #{row.id}
        </foreach>
    </update>