MySql中json类型的使用___mybatis存取mysql中的json

清疚 2023-10-01 15:52 26阅读 0赞

## MySql中json类型的使用 ##

MySQL从5.7.8起开始支持JSON字段，这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能，至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

--------------------

## 一、使用json的目的 ##

1、可以直接过滤记录  
2、可以直接update，而无须先读取  
3、可以在一条SQL中完成多条纪录的修改！  
4、通过json类型，完美的实现了表结构的动态变化  
5、通过计算生成列且在该列上建立索引。提高查询效率

## 二、开始使用 ##

### 1.建表 ###

建表语句如下：

CREATE TABLE `msg_info` (
      `id` int(10) unsigned NOT NULL,
      `message` json NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 2.插入数据 ###

insert into msg_info values (1,'{"name":"zhangsan","phone":"13752763211"}');
    insert into msg_info values (2,'{"name":"lisi","phone":"13752763222"}');

### 3.查询更新操作 ###

1、过滤记录

select * from	msg_info where message->'$.phone'='13752763211';

2、查询json内指定字段

select message->'$.name' from	msg_info;
    # 这样查询出来的字段是带双引号的，使用如下语句可去除双引号，也可以使用关键字JSON_UNQUOTE
    select message->>'$.name' from	msg_info;

3、直接更新json串内的字段内容

UPDATE msg_info set message = JSON_SET(message, '$.name', 'lili') WHERE id = 1;
    # 为json串添加字段
    update msg_info set message = JSON_INSERT(message, '$.age', 30) WHERE id = 1;

### 3.动态扩展字段 ###

1、为json添加虚拟字段

ALTER TABLE msg_info  ADD v_phone  varchar (12) GENERATED ALWAYS  AS (JSON_UNQUOTE(message->'$.phone' ));

2、为虚拟字段创建索引，提高查询效率

# 通过执行计划可以查看创建索引前后的变化
    ALTER TABLE msg_info ADD INDEX idx_phone(v_phone);

## mybatis存取mysql中的json ##

mysql 5.7后新增了一个json类型字段，以往json入库都是转字符串，取到前端造成了不少困扰。今天就做了个小例子把这个整合到ssm例子中。

这边也顺便说下如果idea在启动tomcat客户端控制台出现乱码处理办法

打开idea安装目录-bin  
用记事本打开idea.exe.vmoptions和idea64.exe.vmoptions文件  
在文件后面添加一行：-Dfile.encoding=UTF-8

好了进入正题

第一步先配置一个typehandler,代码如下

import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
     
    import org.apache.ibatis.type.BaseTypeHandler;
    import org.apache.ibatis.type.JdbcType;
    import org.codehaus.jackson.map.ObjectMapper;
    import org.codehaus.jackson.map.SerializationConfig.Feature;
    import org.codehaus.jackson.map.annotate.JsonSerialize.Inclusion;
     
    /**
     * mapper里json型字段到类的映射。
     * 用法一:
     * 入库：#{jsonDataField, typeHandler=com.adu.spring_test.mybatis.typehandler.JsonTypeHandler}
     * 出库：
     * <resultMap>
     * <result property="jsonDataField" column="json_data_field" javaType="com.xxx.MyClass" typeHandler="com.adu.spring_test.mybatis.typehandler.JsonTypeHandler"/>
     * </resultMap>
     *
     * 用法二：
     * 1）在mybatis-config.xml中指定handler:
     *      <typeHandlers>
     *              <typeHandler handler="com.adu.spring_test.mybatis.typehandler.JsonTypeHandler" javaType="com.xxx.MyClass"/>
     *      </typeHandlers>
     * 2)在MyClassMapper.xml里直接select/update/insert。
     *
     */
    public class JsonTypeHandler<T extends Object> extends BaseTypeHandler<T> {
        
        private static final ObjectMapper mapper = new ObjectMapper();
        private Class<T> clazz;
     
        public JsonTypeHandler(Class<T> clazz) {
        
            if (clazz == null) throw new IllegalArgumentException("Type argument cannot be null");
            this.clazz = clazz;
        }
     
        @Override
        public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        
            ps.setString(i, this.toJson(parameter));
        }
     
        @Override
        public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        
            return this.toObject(rs.getString(columnName), clazz);
        }
     
        @Override
        public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        
            return this.toObject(rs.getString(columnIndex), clazz);
        }
     
        @Override
        public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        
            return this.toObject(cs.getString(columnIndex), clazz);
        }
     
        private String toJson(T object) {
        
            try {
        
                return mapper.writeValueAsString(object);
            } catch (Exception e) {
        
                throw new RuntimeException(e);
            }
        }
     
        private T toObject(String content, Class<?> clazz) {
        
            if (content != null && !content.isEmpty()) {
        
                try {
        
                    return (T) mapper.readValue(content, clazz);
                } catch (Exception e) {
        
                    throw new RuntimeException(e);
                }
            } else {
        
                return null;
            }
        }
     
        static {
        
            mapper.configure(Feature.WRITE_NULL_MAP_VALUES, false);
            mapper.setSerializationInclusion(Inclusion.NON_NULL);
        }

mapper代码

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.cm.dao.UserDao">
     
        <resultMap id="user" type="com.cm.model.UserModel">
            <id column="id" jdbcType="NUMERIC" property="id"/>
            <result column="name" jdbcType="VARCHAR" property="name"/>
            <result column="age" jdbcType="NUMERIC" property="age"/>
            <result column="hobby" jdbcType="NUMERIC" property="hobby" typeHandler="com.cm.mybaits.JsonTypeHandler"/>
     
        </resultMap>
        <select id="getAllUsers" resultMap="user">
            select * from user
        </select>
         
        <insert id="addUser">
            
            insert ignore into user (name, age, hobby) values (#{
        id}, #{
        name} ,#{
        hobby, typeHandler=com.cm.mybaits.JsonTypeHandler})
        </insert>
         
        <update id="updateUser">
            update user set name=#{
        name} where id=#{
        id}
        </update>
     
        <delete id="deleteUser" parameterType="String">
            delete from user where id=#{
        id}
        </delete>
     
        <select id="getUser" resultType="UserModel">
            select * from user where id = #{
        id}
        </select>
    </mapper>

mysql表结构  
　![在这里插入图片描述][fe5bd08eedad44588fcd56271838ff51.png]  
插入的测试代码  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_18_color_FFFFFF_t_70_g_se_x_16]

效果预览

入库

![在这里插入图片描述][6fa922016ef542e3bd65fb5636fa1d7d.png]  
取数据  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_14_color_FFFFFF_t_70_g_se_x_16]  
这边有个坑是mysql 驱动一定要5.1.40，不然取出来的json中文是乱码。虽然说是低于5.1.36会乱码，但是我试了5.1.6还是乱码。

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.40</version>
    </dependency>

[fe5bd08eedad44588fcd56271838ff51.png]: https://img-blog.csdnimg.cn/fe5bd08eedad44588fcd56271838ff51.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_18_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/534c3d80430841c192d7a55b5ca17c64.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQXJjaGllX2phdmE=,size_18,color_FFFFFF,t_70,g_se,x_16
[6fa922016ef542e3bd65fb5636fa1d7d.png]: https://img-blog.csdnimg.cn/6fa922016ef542e3bd65fb5636fa1d7d.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_14_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/f3ffbc622f504e2eb4b1c3cfbf0374bf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQXJjaGllX2phdmE=,size_14,color_FFFFFF,t_70,g_se,x_16