博客

1.1 基本介绍

Mybatis-plugs 批量新增及批量修改、IN、CASE WHEN 查询，简单，适用，最近用到了，那就留下点痕迹，免得下次用到再去找 
<foreach> 标签里面的各个属性怎么配置！

1.2 代码

实体：

@Data
@TableName("device")
public class Device implements Serializable {

    private static final long serialVersionUID = 1L;

    /**id*/
    private String id;
    /**设备类型id*/
    private Integer typeId;
    /**S/N 码*/
    private String sn;
    /**使用方式，1：售卖，2：租赁 (小程序绑定时修改)*/
    private Integer useMode;
    /**库存状态（1：已出库，2：已入库，3：待入库）*/
    private Integer stockStatus;
    /**设备状态(1：未绑定，2：已绑定，3：已损坏)*/
    private Integer deviceStatus;
    /**门店id (store.id)*/
    private String storeId;
    /**商户id (merchant_cs.id)*/
    private String merchantId;
    /**是否删除(1：是/true，0：否/false)*/
    private Boolean deleted;
    /**备注*/
    private String remark;
    /**创建时间*/
    private Date createTime;
    /**更新时间*/
    private Date updateTime;

}

Mapper：

@Mapper
public interface DeviceMapper extends BaseMapper<Device> {

	 /**
     * 根据sn码批量查询id
     * @param sns
     * @return
     */
    List<String> findIds(@Param("sns") List<String> sns);
    
    /**
     * 根据id查询详细
     * @param id
     * @return
     */
    Map<String, Object> findDetail(@Param("id") String id);

    /**
     *  修改设备相关状态
     * @param list
     * @return
     */
    void batchUpdateWarehouse(@Param("list") List<Device> list);

    /**
     *  设备扫码批量入库
     * @param list
     */
    void batchPortalInputWarehouse(@Param("list") List<Device> list);

}

xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.lq.cloud.mapper.agent.DeviceMapper">

    <sql id="batchInsertDevice">
        id, stock_status, device_status, sn, type_id, deleted, create_time, update_time
    </sql>

    <!-- 根据sn码批量查询id -->
    <select id="findIds" parameterType="java.util.List" resultType="java.lang.String">
        select id from device
        <where>
            <if test="sns != null and sns.size() > 0 ">
                 sn IN
                <foreach collection="sns" item="id" index="index" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
    
    <!-- 根据id查询详细 -->
    <select id="findDetail" resultType="java.util.Map">
        SELECT
            d.id,
            dt.`name`,
            d.sn,
            dt.picture_url pictureUrl,
            cr.current_position currentPosition,
            cr.device_status deviceStatus,
        CASE
                WHEN d.use_mode = 1 THEN '售卖'
                WHEN d.use_mode = 2 THEN '租赁'
                ELSE ''
            END useMode
        FROM
            device d
            LEFT JOIN device_circulation_record cr ON d.id = cr.device_id
            AND cr.create_time = (
            SELECT
                max( create_time )
            FROM
            device_circulation_record)
            LEFT JOIN device_type dt ON dt.id = d.type_id
            where d.id = #{id}
    </select>

    <!-- 城市服务商修改设备相关状态，并保存流转记录 -->
    <!-- collection="list"   collection 属性的值是 Mapper interface 里定义的形参 -->
    <!-- #{item.id}  item 是 <foreach> 标签里 item 属性 的值，通过 对象.属性 来获取传入的各个参数 -->
    <!-- 下同 -->
    <update id="batchUpdateWarehouse"  parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update device
            <set>
                <if test="item.stockStatus != null">
                    stock_status = #{item.stockStatus},
                </if>
                <if test="item.deviceStatus != null">
                    device_status = #{item.deviceStatus},
                </if>
                <if test="item.updateTime != null">
                    update_time = #{item.updateTime}
                </if>
            </set>
            where sn = #{item.sn}
        </foreach>
    </update>


    <!-- 后台 设备扫码批量入库 -->

    <insert id="batchPortalInputWarehouse">
        insert into device (<include refid="batchInsertDevice"></include>)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id},
            #{item.stockStatus},
            #{item.deviceStatus},
            #{item.sn},
            #{item.typeId},
            #{item.deleted},
            #{item.createTime},
            #{item.updateTime}
            )
        </foreach>
    </insert>