MyBatis批量插入、批量更新、批量删除、批量查询

批量插入

WaterEleMapper部分代码

int insertList(List<WaterEle> list);

WaterEleMapper.xml部分代码

<!--批量增加测试-->
<insert id="insertList" parameterType="java.util.List">
    insert into t_enterprise_water_ele
    (
    /*方法一*/
    -- WATER_ELE_ID,
    -- ENTERPRISE_ID,
    -- ENTERPRISE_USCC,
    -- ENTERPRISE_NAME,
    -- YEARMONTH,
    -- WATER_SIZE,
    -- WATER_AMOUNT,
    -- ELE_SIZE,
    -- ELE_AMOUNT,
    -- STATUS,
    -- OPERATOR,
    -- OPERATE_TIME
    /*方法二*/
    <include refid="Base_Column_List"/>
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.waterEleId,jdbcType=VARCHAR},
        #{item.enterpriseId,jdbcType=VARCHAR},
        #{item.enterpriseUscc,jdbcType=VARCHAR},
        #{item.enterpriseName,jdbcType=VARCHAR},
        #{item.yearmonth,jdbcType=VARCHAR},
        #{item.waterSize,jdbcType=DECIMAL},
        #{item.waterAmount,jdbcType=VARCHAR},
        #{item.eleSize,jdbcType=DOUBLE},
        #{item.eleAmount,jdbcType=VARCHAR},
        #{item.status,jdbcType=INTEGER},
        #{item.operator,jdbcType=VARCHAR},
        #{item.operateTime,jdbcType=TIMESTAMP}
        )
    </foreach>
</insert>

对于foreach标签的解释具体如下：

使用批量插入执行的SQL语句应该等价于：

insert into redeem_code (batch_id, code, type, facevalue,create_user,create_time)
values (?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? ),(?,?,?,?,?,? )

批量更新

传list集合

（1）单个字段方法一

<update id="updateByBatch" parameterType="java.util.List">
    update t_goods
    set NODE_ID=
    <foreach collection="list" item="item" index="index"
             separator=" " open="case" close="end">
      WHEN GOODS_ID=#{item.goodsId} then #{item.nodeId}
    </foreach>
    WHERE GOODS_ID in
    <foreach collection="list" index="index" item="item"
             separator="," open="(" close=")">
      #{item.goodsId,jdbcType=BIGINT}
    </foreach>
</update>

以上单字段更新实际执行：

UPDATE t_goods SET NODE_ID = WHEN GOODS_ID = ? THEN ? WHERE GOODS_ID IN ( ? )

（2）单个字段方法二

<update id="updateByBatch" parameterType="java.util.List">
    UPDATE
    t_goods
    SET NODE_ID = CASE
    <foreach collection="list" item="item" index="index">
      WHEN GOODS_ID = #{item.goodsId} THEN #{item.nodeId}
    </foreach>
    END
    WHERE GOODS_ID IN
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      #{item.goodsId}
    </foreach>
</update>

以上单字段更新实际执行：

UPDATE t_goods SET NODE_ID = CASE WHEN GOODS_ID = ? THEN ? END WHERE GOODS_ID IN ( ? )

（3）多个字段

<update id="updateBatch" parameterType="java.util.List">
    update t_user
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="STATUS =case" suffix="end,">
            <foreach collection="list" item="i" index="index">
                <if test="i.status!=null">
                    when USER_ID=#{i.userId} then #{i.status}
                </if>
            </foreach>
        </trim>
        <trim prefix=" OPERATE_TIME =case" suffix="end,">
            <foreach collection="list" item="i" index="index">
                <if test="i.operateTime!=null">
                    when USER_ID=#{i.userId} then #{i.operateTime}
                </if>
            </foreach>
        </trim>

        <trim prefix="OPERATOR =case" suffix="end," >
            <foreach collection="list" item="i" index="index">
                <if test="i.operator!=null">
                    when USER_ID=#{i.userId} then #{i.operator}
                </if>
            </foreach>
        </trim>
    </trim>
    where
    <foreach collection="list" separator="or" item="i" index="index" >
        USER_ID=#{i.userId}
    </foreach>
</update>

int updateBatch(List<WaterEle> list);

（4）更新多条记录的同一个字段为同一个值

<update id="updateByBatchPrimaryKey" parameterType="java.util.Map">
UPDATE t_goods
SET NODE_ID = #{nodeId}
WHERE GOODS_ID IN (${goodsIdList})
</update>

UPDATE t_goods SET NODE_ID = ? WHERE GOODS_ID IN (1,2,5);

传map/传String（同批量删除的"传map/传String"）

<update id="deleteByPrimaryKey" parameterType="java.util.Map">
    UPDATE t_order_checkout
    SET NODE_ID = #{nodeId, jdbcType=VARCHAR}, OPERATOR = #{operator, jdbcType=VARCHAR}
    WHERE CHECKOUT_ID IN (${checkoutIdList})
</update>

批量删除（数组）

传数组

int deleteByBatch(String[] array);

<delete id="deleteByBatch" parameterType="java.lang.String">
    delete from t_enterprise_output_value
    where OUTPUT_ID IN
    <foreach collection="array" item="outputId" open="(" separator="," close=")">
        #{outputId}
    </foreach>
</delete>

传map/传String

<delete id="deleteByRole" parameterType="java.util.Map">
    DELETE
    FROM
    t_user_role
    <where>
        <if test="userIdList != null">
            USER_ID IN (#{userIdList,jdbcType=VARCHAR})
        </if>
        <if test="roleId != null">
            AND ROLE_ID=#{roleId,jdbcType=VARCHAR}
        </if>
        <if test="sysCode != null">
            AND SYSCODE=#{sysCode}
        </if>
    </where>
</delete>

因为表中没有fileIds字段，所以如果传map进入的话，需要在map中定义该字段

map.put("fileIds", "1,2,3");

//美元符$直接注入
<delete id="deleteByPrimaryKey" parameterType="java.util.Map">
    DELETE FROM t_attachment WHERE FILE_ID  IN (${fileIds})
</delete>

完整的sql语句是：

DELETE FROM t_attachment WHERE FILE_ID IN (1,2,3)

适用于表中该字段是int或者bigint类型，不适用于varchar。如果该字段是varchar类型，则正确的sql语句应该是：

DELETE FROM t_attachment WHERE FILE_ID IN ("1","2","3");

map中就应该这样定义：

数组/集合 -->(1,2,3,4)
String userIdList = formData.get("userIdList");
String[] users = userIdList.split(",");
String str = "";
for (String user : users) {
    str += "\"" + user + "\"" + ",";
}
String substring = str.substring(0, str.lastIndexOf(","));
System.out.println(substring);

如果要传substring进sql语句中，但是substring不是数据库表中的字段，三种方法：
① map.put("substring",substring)，sql接收参数parameterType传java.util.Map
② 将substring放入对象中，传对象进去
③ 使用@Param("substring")注解

int deleteByCheckoutId(@Param("checkoutIdList") String checkoutIdList);

而int deleteByCheckoutId(long cDetailId);不需要注解是因为cDetailId对应表中的C_DETAIL_ID，因为是表中现存的所以可以不用加，我个人理解。

<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from t_checkout_detail
    where C_DETAIL_ID = #{cDetailId,jdbcType=BIGINT}
</delete>

多参数批量删除示例

如果删除不是以主键为条件，而是多个条件同时成立才可以删除

<delete id="deleteByUserIdSysRoleBatch">
    delete from t_user_role
    where SYSCODE = #{sysCode,jdbcType=VARCHAR} AND ROLE_ID = #{roleId,jdbcType=VARCHAR} AND USER_ID IN
    <foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

Controller层接口

@RequestMapping(value = "", method = RequestMethod.POST)
public ResponseObj<Boolean> setMoreUserToRole(@RequestBody Map<String, String> formData) {

    String userIdList = formData.get("userIdList");
    String[] users = userIdList.split(",");
    String str = "";
    for (String user : users) {
        str += "\"" + user + "\"" + ",";
    }
    String substring = str.substring(0, str.lastIndexOf(","));
    System.out.println(substring);

    String sysCode = formData.get("sysCode");
    String roleId = formData.get("roleId");
    userRoleMapper.deleteByUserIdSysRoleBatch(sysCode, roleId, users);

    List<UserRole> list = new ArrayList<>();
    for (int i = 0; i < users.length; i++) {
        UserRole userRole = new UserRole();
        String roleId = formData.get("roleId");
        if (roleId != null && !"".equals(roleId)) {
            userRole.setRoleId(roleId);
        }
        userRole.setStatus(SysData.STATUS_NORMAL);
        userRole.setOperateTime(DateUtil.getDateTime());
        userRole.setOperator(formData.get("operator"));
        userRole.setSysCode(formData.get("sysCode"));
        userRole.setId(CommonUtil.getSysRef());
        userRole.setUserId(users[i]);

        list.add(userRole);
    }

    int i = userRoleMapper.addByBatch(list);
    if (i == users.length)
        return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
    return new ResponseObj<Boolean>(false, RetCode.FAIL);
}

int deleteByUserIdSysRoleBatch(@Param("sysCode") String sysCode, 
                               @Param("roleId") String roleId,
                               @Param("userIds") String[] userId);

批量查询

in()语句实现

注：以下orderList可以使用map/String传入

<select id="selectOrder" parameterType="java.util.Map" resultMap="OrderMap">
    SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
</select>

（1）对于表中的主键是bigint / int

==>  Preparing: SELECT * FROM t_order WHERE ORDER_ID in (?) 
==> Parameters: 1,2,5(String)
<==    Columns: ORDER_ID, GOODS_ID, UNIT_PRICE, SOLD_NUM, ORDER_STATUS, OPERATOR, OPERATE_TIME
<==        Row: 1, 356346093, 43.00, 3, 109002, admin, 2019-12-16 21:01:20.0
<==      Total: 1

注意"1,2,5"传过来的是String类型，实际sql语句如下：

SELECT * FROM t_order WHERE ORDER_ID in (#{orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1,2,5')  #只能查到一条

将mapper.xml中的sql语句改为

SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
SELECT * FROM t_order WHERE ORDER_ID in (1,2,5)  #就可以全部查到

（2）对于表中的主键是varchar

SELECT * FROM t_order WHERE ORDER_ID in (#{orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1,2,5')  #查询不到

将mapper.xml中的sql语句改为

SELECT * FROM t_order WHERE ORDER_ID in (${orderList})
SELECT * FROM t_order WHERE ORDER_ID in ('1','2','5')  #就可以全部

'1,2,5'转为'1','2','5'步骤如下：

String orderList = (String) formData.get("orderList");
if (!StringUtils.isEmpty(orderList)) {
    String[] split = orderList.split(",");
    String sqlParamter = "";
    for (String s : split) {
        sqlParamter += "'" + s + "',";
    }
    String substring = sqlParamter.substring(0, sqlParamter.length() - 1);
    formData.put("orderList", substring);
}