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 AND json_extract(d.device_status,'$[0]') in #{item}

2.查询JSON字段某个符合要求的元素 【Mybatis之复杂操作】JSON_CONTAINS

select * from `area_school` and organization_code in #{schoolCode} and floor_code = #{floorCode} and JSON_CONTAINS(`area_room`,JSON_OBJECT('houseNum', #{houseNum})) and JSON_CONTAINS(`area_room`,JSON_OBJECT('dictionaryCode', #{dictionaryCode})) order by organization_code desc

3.根据集合编号进行更新

update `device_device` when code=#{item.code} then #{item.deviceStatus} where code in #{item.code}

4.根据集合对象批量更新 when then

update `device_device` when sn_code=#{item.snCode} then #{item.installUnitCode} when sn_code=#{item.snCode} then #{item.repairUnitCode} when sn_code=#{item.snCode} then #{item.bidUnitCode} where is_delete = false and sn_code in #{item.snCode}