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>