MyBatis的几种批量操作 - MySql

---

说明

此处针对MySql的数据库操作,
datasource.url：jdbc:mysql://127.0.0.1:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true

批量新增

dao

List<Map<String, Object>> listData = new ArrayList<>();

/**
 * 批量新增
 * @param params  参数1
 * @param list 参数2
 */
void saveBatchData(@Param("params")Map<String, Object> map, @Param("list")List<Map<String, Object>> list);

Mapper

此处的 “list” 为 dao里面的 @Param(“list”) 的 list

<insert id="saveBatchData" parameterType="java.util.List" >
    insert into user_table
    ( USER_NAME,USER_STATUS,CREATE_TIME,UPDATE_TIME ) values
    <foreach collection="list" item="item" separator=",">
        ( #{item.userName},#{item.userStatus},#{item.createDate},#{item.updateDate} )
    </foreach>
</insert>

批量删除

dao

数组:

List<String> list = new ArrayList<>();
list.add("1");
String[] stringArray = new String[list.size()];

/**
 * 批量删除
 * @param stringArray 参数
 */
void deleteBatchDataByArray(String[] stringArray);

集合:

List<Map<String, Object>> listData = new ArrayList<>();

/**
 * 批量删除
 * @param list 参数
 */
void deleteBatchDataByList(@Param("list")List<Map<String, Object>> list);

Mapper

数组:

<delete id="deleteBatchDataByArray">
    delete from user_table  where USER_ID in
    <foreach item="item" index="index" collection="array" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

集合:

<delete id="deleteBatchDataByList" parameterType="java.util.List">
    delete from user_table where
    <foreach collection="list" item="item" index="index"  separator="or">
        ( USER_ID = #{item.userId} )
    </foreach>
</delete>

批量更新

dao

List<Map<String, Object>> listData = new ArrayList<>();

/**
 * 批量更新
 * @param params  参数1
 * @param list 参数2
 */
void updateBatchData(@Param("params")Map<String, Object> params, @Param("list")List<Map<String, Object>> list);

Mapper

<update id="updateBatchData" parameterType="java.util.List" >
    <foreach collection="list" item="item" separator=";">
        update user_table
        <set>
            USER_NAME = #{item.userName},
            USER_STATUS = #{item.userStatus},
            UPDATE_TIME = #{item.updateDate}
        </set>
        where
            USER_ID = #{item.userId}
    </foreach>
</update>