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