很多时候mysql的一列当中存的是json格式的数据,这时候如果要查询某个key对应的值的时候要如何查询呢,这里记录一种查询方法:
json列的值:
{“InventoryMainTypeCode”: 1, “InventoryMainTypeName”: “GOOD”}
现在要查询InventoryMainTypeCode为xxx或者InventoryMainTypeName为xxx的数据:(这里以springboot+mybatis为例子)
代码:
请求类:
Query query = new Query();
@Data
public class Query implements Serializable {
private Map<String, Object> featureMap = new HashMap<>();
public void setFeature(String feature) {
if (StringUtils.isNotBlank(feature)) {
featureMap = JSON.parseObject(feature, Map.class);
}
}
public void addFeature(String key, Object value) {
if (StringUtils.isBlank(key)) {
return;
}
this.featureMap.put(key, value);
}
public void addFeatureMap(Map<String, Object> featureMaps) {
if (MapUtils.isNotEmpty(featureMaps)){
this.featureMap.putAll(featureMaps);
}
}
}
在代码中将key添加到Feature中:
query.addFeature(DicConst.InventoryMainTypeCode.name(), 1);
List<ResultDTO> dTOS = dictionaryManager.queryDicByParam(query);
mapper文件:
List<CnbDictionaryDO> queryDicByParam(CnbDictionaryQuery query);
xml:
<select id="queryDicByParam" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from
<include refid="Table_Name"/>
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="featureMap != null and featureMap.size > 0">
<foreach collection="featureMap" index="key" item="value">
<if test="value != null">
<![CDATA[and feature->'$.${key}' = #{value}]]>
</if>
</foreach>
</if>
</where>
</select>