MySQL和Mybatis中JSON类型的使⽤
⽂章⽬录
⼀. 背景
最近在做项⽬的时候，由于不同场景拥有各⾃的⾃定义属性，并且属性个数⼤于1，但查询数据时还需要⽤这些⾃定义属性作为查询条件，因此需要将⾃定义属性持久化存储，并且可以使⽤任意属性及其值进⾏查询。
为了满⾜上述需求，调研发现MySQL5.7+版本拥有json数据类型，并且可以对json中的某个属性进⾏where字段的查询，因此研究了⼀下Mysql中json数据类型的使⽤。同时，由于项⽬需要以服务接⼝形式访问，因此也涉及到MyBatis中json数据类型的使⽤。在这⾥⼩记⽤于回顾。
⼆. MySQL中JSON类型的使⽤
2.1 概述
1. Mysql5.7+开始⽀持json类型数据存储，不能有默认值;
2. json类型数据形式
1. JSON对象
1. 表⽰⽅法：{key1:value1, key2:value2, ………}
2. ⽰例：{"id":1, "name":"houhou"}
2. JSON数组
1. 表⽰⽅法：[value1,value2,value3…..]
2. ⽰例：[1,2,3]或["haha","xixi","houhou"]
3. 对象数组
1. 表⽰⽅法：[{key1:value1,key2:value2},{key3:value3,key4:value4},…….]
2. ⽰例：[{"id":1, "name":"houhou"},{"id":2, "name":"xixi"}]
2.2 MySQL中json的使⽤
1. 创建表
CREATE TABLE `tableA(
`id` int(11),
`custom_biz_ex_info` json NULL
)
2. 插⼊数据
INSERT INTO tableA( `id, `custom_biz_ex_info` ) values (1,'{"fileName": "l","idT":12}');
INSERT INTO tableA( `id, `custom_biz_ex_info` ) values (2,'{"idT": "13", "fileName": "l"}')
INSERT INTO tableA( `id, `custom_biz_ex_info` ) values (3,'{"idT": "17", "fileName": "l"}')
3. 查询数据
SELECT * FROM tableA where custom_biz_ex_info->'$.fileName' like '%file%';
SELECT * FROM tableA where json_extract(`custom_biz_ex_info`,'$.fileName') like concat('%', 'file', '%') ;
序号id custom_biz_ex_info
11{“idT”: “13”, “fileName”: “l”}
22{“idT”: “17”, “fileName”: “l”}
33{“idT”: “22”, “fileName”: “l”}
SELECT custom_biz_ex_info->'$.fileName' FROM tableA where custom_biz_ex_info->'$.fileName' like '%file%';
SELECT json_extract(`custom_biz_ex_info`,'$.fileName') FROM `tableA` where json_extract(`custom_biz_ex_info`,'$.fileName') like '%file%';
序号json_extract(custom_biz_ex_info,’$.fileName’)
json值的类型有哪些1“l”
2“l”
3“l”
三. MyBatis中JSON类型的使⽤
3.1 概述
在MyBatis中使⽤时，通常都需要⽤JSON类型中的某个key作为查询字段，该key对应的value作为模糊查询条件，这⾥主要讲解本种查询的使⽤。
要点
模糊查询：concat()函数的使⽤
Map在MyBatis中的使⽤
MyBatis中${…} 和 #{…} 的区别
3.2 要点讲解
（1） concat()函数的使⽤
1. 定义：CONCAT(str1,str2,…)
1. 返回结果为连接参数产⽣的字符串；如果有任何⼀个参数为NULL ，则返回值为 NULL。
2. ⽰例
mysql> select concat('10'); // 10
mysql> select concat('11','22','33'); // 112233
mysql> select concat('11','22',null); // NULL
（2）${…} & #{…}
1. 定义
1. ${}：直接填充，即解析出来的参数值不带单引号
2. #{} 会预编译，即解析传过来参数带单引号
2. ⽰例
// 如果⼊参是table= user, column = id
//1.${}
select * from ${table} where ${column} =1; --> select * from user where id =1;
//2.#{}
select * from #{table} where #{column} =1; --> select * from 'user' where 'id' =1;
3. 说明：使⽤${}的拼接⽅式存在sql注⼊攻击的风险。如下⽰例：
select * from aTable where c1=${uu}`
如u的查询条件输⼊为'or '1=1，就会返回所有数据，即select * from aTable where c1= '' or '1=1'。
3.3 MyBatis中json 的使⽤
（1）法1：collection=“params.keys”（不推荐）
1. Dao层
//@Param("params") 必须写params，这样在mapper层就可以使⽤params.keys和params.values
Page<DTO>listDTO(@Param("params") Map<String, String> customMap);
2. l
<select id="listDTO"resultType="com.zijikanwa.DTO">
select * from tableA
<where>
<if test="customMap!=null and customMap.size()>0">
<foreach collection="params.keys"item="key">
and json_extract(custom_biz_ex_info, CONCAT('$.',#{key})) like concat('%',#{params[${key}]},'%')
</foreach>
</if>
</where>
</select>
3. 说明：本⽅法可以得到正确的结果，但由于$会有SQL注⼊的风险，不推荐使⽤。
4. 福利：foreach属性
foreach
属性是
否
必
选
说明
item 必
选
循环体中的具体对象，如item.age, item.info.details具体说明：若collection属性为list或array，则item
代表list或array⾥⾯的⼀个元素; 若collection属性为map，则item代表的是map中的value集合中的单个value
collection 必
选
foreach遍历的对象。1. 属性值为list或array或map。List对象默认⽤list代替作为键，数组对象⽤array代替作为键，Map对象没有默认的键。2.使⽤@Param(“params”)来设置键，设置keyName后，list,array将会失效。 3.如果传⼊参数类型为map，这个⼊参有注解@Param(“params”)，则map的所有的key集合可以写成params.keys，所有值集合可以写成params.values。这样foreach就可以对key集合或值集合进⾏迭代了
separator 可
选
元素之间的分隔符。例如在in(1,2)的时候，separator=","会⾃动在元素中间⽤“,“隔开，避免⼿动输⼊逗号导致sql错误
open 可
选
foreach代码的开始符号，⼀般是(和close=")"合⽤。常⽤在in(),values()时
close 可
选
foreach代码的关闭符号，⼀般是)和open="("合⽤。常⽤在in(),values()时
index可
选在list和数组中,index是元素的序号；在map中，index是元素的key
foreach
属性是
否
必
选
说明
（2）法2：collection为⾃定义map，index为map的key，item为map的value
1. Dao层
Page<DTO>listDTO(@Param("customMap") Map<String, String> customMap);
2. l
<select id="listDTO"resultType="com.zijikanwa.DTO">
select * from tableA
<where>
<if test="customMap!=null and customMap.size()>0">
<foreach collection="customMap"index="key"item="value">
and json_extract(custom_biz_ex_info, CONCAT('$.',#{key})) like concat('%',#{value},'%')
</foreach>
</if>
</where>
</select>
3. 说明：collection为customMap，index为map的key，item为map的value，因此#{key}，#{value}即可取到这个map集合中的键
值。本⽅法没有安全问题，推荐。
四. 参考资料
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论