关于mybatis:Mybatis之复杂操作

最近在我的项目中遇到一些之前没有遇到的查问，以及实现，心愿能帮忙到工作中帮忙到正在求解的你们。

1.JSON 数组查问，针对 mysql5.7 及以上版本，数据库字段类型为 JSON 类型。

查问 json 数组下标为 0 的元素 (批量)
json_extract

SELECT d.id,
        d.name,
        d.name as equipment_name,
        d.code,
        d.sn_code,
        d.model_code,
        d.city_code,
        d.district_code,
        d.install_unit_code,
        d.bid_unit_code,
        d.repair_unit_code,
        m.category_code,
        m.pattern,
        m.name AS model_name,
        m.brand_code,
        d.warranty,
        d.warranty as guarantee_period,
        d.warranty_status,
        d.warranty_time,
        d.gmt_created,
        d.gmt_modified,
        d.created_by,
        d.modified_by,
        d.school_code,
        d.build_code,
        d.house_num,
        d.status
        FROM device_device d
        join device_model m
        on d.model_code = m.code
  <where>
    <if test="arrayStatus != null and arrayStatus.length >0">
       AND json_extract(d.device_status,'$[0]') in
        <foreach collection="arrayStatus" item="item" index="index" open="(" separator="," close=")">
             #{item}
        </foreach>
    </if>
  </where>
     

2. 查问 JSON 字段某个符合要求的元素

JSON_CONTAINS

<select id="queryRoom" resultType="com.yuxiang.intelligence.dao.dataobject.AreaSchoolDO">
        select *
        from `area_school`
        <where>
            <if test="schoolList != null and schoolList.size >0">
                and organization_code in
                <foreach collection="schoolList" item="schoolCode" open="(" separator="," close=")" index="index">
                    #{schoolCode}
                </foreach>
            </if>
            <if test="floorCode != null and floorCode !=''">
                and floor_code = #{floorCode}
            </if>
            <if test="houseNum != null and houseNum !=''">
                and JSON_CONTAINS(`area_room`,JSON_OBJECT('houseNum', #{houseNum}))
            </if>
            <if test="dictionaryCode != null and dictionaryCode !=''">
                and JSON_CONTAINS(`area_room`,JSON_OBJECT('dictionaryCode', #{dictionaryCode}))
            </if>
        </where>
        order by organization_code desc
    </select>

3. 依据汇合编号进行更新

<update id="updateStatusBatch" parameterType="java.lang.Integer">
        update `device_device`
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="device_status =case" suffix="end">
                <foreach collection="list" item="item" index="index">
                    <if test="item.code!=null">
                        when code=#{item.code} then #{item.deviceStatus}
                    </if>
                </foreach>
            </trim>
        </trim>
        where code in
        <foreach collection="list" separator="," item="item" index="index" open="(" close=")">
            #{item.code}
        </foreach>
    </update>

4. 依据汇合对象批量更新

when then

<update id="updateBatch" parameterType="java.lang.Integer">
        update `device_device`
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="install_unit_code =case" suffix="end,">
                <foreach collection="deviceList" item="item" index="index">
                    <if test="item.installUnitCode != null">
                        when sn_code=#{item.snCode} then #{item.installUnitCode}
                    </if>
                </foreach>
            </trim>
            <trim prefix="repair_unit_code =case" suffix="end,">
                <foreach collection="deviceList" item="item" index="index">
                    <if test="item.repairUnitCode != null">
                        when sn_code=#{item.snCode} then #{item.repairUnitCode}
                    </if>
                </foreach>
            </trim>
            <trim prefix="bid_unit_code =case" suffix="end,">
                <foreach collection="deviceList" item="item" index="index">
                    <if test="item.bidUnitCode != null">
                        when sn_code=#{item.snCode} then #{item.bidUnitCode}
                    </if>
                </foreach>
            </trim>
        </trim>
        where is_delete = false and sn_code in
        <foreach collection="deviceList" separator="," item="item" index="index" open="(" close=")">
            #{item.snCode}
        </foreach>
    </update>