MySQL和Mybatis中JSON类型的使用

一. 背景

• 最近在做项目的时候，由于不同场景拥有各自的自定义属性，并且属性个数大于1，但查询数据时还需要用这些自定义属性作为查询条件，因此需要将自定义属性持久化存储，并且可以使用任意属性及其值进行查询。
• 为了满足上述需求，调研发现MySQL5.7+版本拥有json数据类型，并且可以对json中的某个属性进行where字段的查询，因此研究了一下Mysql中json数据类型的使用。同时，由于项目需要以服务接口形式访问，因此也涉及到MyBatis中json数据类型的使用。在这里小记用于回顾。

---

二. MySQL中JSON类型的使用

• mysql官方文档中json介绍

2.1 概述

1. Mysql5.7+开始支持json类型数据存储，不能有默认值;
2. json类型数据形式
   1. JSON对象
      • 表示方法：{key1:value1, key2:value2, ………}
      • 示例：{"id":1, "name":"houhou"}
   2. JSON数组
      • 表示方法：[value1,value2,value3…..]
      • 示例：[1,2,3]或["haha","xixi","houhou"]
   3. 对象数组
      • 表示方法：[{key1:value1,key2:value2},{key3:value3,key4:value4},…….]
      • 示例：[{"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": "fileTest3.xml","idT":12}');
INSERT INTO tableA( `id, `custom_biz_ex_info` ) values (2,'{"idT": "13", "fileName": "fileTest1.xml"}')
INSERT INTO tableA( `id, `custom_biz_ex_info` ) values (3,'{"idT": "17", "fileName": "fileTest2.xml"}')

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
1	1	{“idT”: “13”, “fileName”: “fileTest1.xml”}
2	2	{“idT”: “17”, “fileName”: “fileTest2.xml”}
3	3	{“idT”: “22”, “fileName”: “fileTest3.xml”}

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’)
1	“fileTest1.xml”
2	“fileTest2.xml”
3	“fileTest3.xml”

---

三. MyBatis中JSON类型的使用

3.1 概述

• 在MyBatis中使用时，通常都需要用JSON类型中的某个key作为查询字段，该key对应的value作为模糊查询条件，这里主要讲解本种查询的使用。
• 要点
  • 模糊查询：concat()函数的使用
  • Map在MyBatis中的使用
  • MyBatis中${…} 和 #{…} 的区别

3.2 要点讲解

（1） concat()函数的使用

1. 定义：CONCAT(str1,str2,…)
   • 返回结果为连接参数产生的字符串；如果有任何一个参数为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. mapper.xml

<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

（2）法2：collection为自定义map，index为map的key，item为map的value

1. Dao层

Page<DTO> listDTO(@Param("customMap") Map<String, String> customMap);

2. mapper.xml

<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集合中的键值。本方法没有安全问题，推荐。

四. 参考资料

• foreach属性
• mybatis中如何通过迭代或者循环获取Map的值？
• MyBatis 注解版 ( 三 ) ${}和 #{}的区别