springboot笔记示例七：mybiteplus框架mysql8新类型json集成

springboot笔记示例七：mysql8新类型json集成

###本文md文件下载地址

https://download.youkuaiyun.com/download/a254939392/89492142

md文件下载

建表SQL

CREATE TABLE `my_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `txt` json DEFAULT NULL,
  `txt_array` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert语句

插件非json插入时会自动校验格式会报错，{}或者[] [{}] 是被允许的。

对于 JSON 文档，KEY 名不能重复。

如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

INSERT INTO `my_test` ( `txt`, `txt_array` )VALUES( '{}', '[{}]' );
INSERT INTO `my_test` ( `txt`, `txt_array` )VALUES( '{}', '[]' );

INSERT INTO `my_test` ( `txt`, `txt_array` ) VALUES( '{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}', NULL );
INSERT INTO `my_test` ( `txt`, `txt_array` )
VALUES ( '{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}', '{\"list\": [{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}, {\"id\": 2, \"signUp\": 0, \"activityName\": \"222撒旦士大夫\"}]}' );

select语句

-- column->path 语法糖，在实际使用的时候都会在底层自动转化为JSON_EXTRACT
-- column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。
--  select JSON_EXTRACT(txt,'$.activityName') from my_test == select txt->'$."activityName"' from my_test

SELECT * FROM my_test WHERE txt->'$."activityName"' = '撒旦士大夫';
SELECT * FROM my_test WHERE txt->'$."activityName"' = '撒旦士大夫' and  txt->'$."type"' = 1
SELECT id,txt,txt_array FROM my_test WHERE txt -> '$.activityName' LIKE CONCAT('%','大夫','%') AND txt -> '$.type' = 1
SELECT * FROM my_test WHERE txt->'$."id"' = 1;
SELECT id, txt -> '$.*' AS nam11e FROM my_test

mybiteplus集成

json转换

本文采用最新fastjson2，目前mybatisplus并未支持fastjson2转换，所以需要自定义转换

//自定义fastjson2
@TableField(typeHandler = Fastjson2TypeHandler.class)
//mybatisplus 自带fastjosn直接使用
@TableField(typeHandler = FastjsonTypeHandler.class)
//mybatisplus 自带Jackson直接使用
@TableField(typeHandler = JacksonTypeHandler.class)

package com.config;

import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
/**
 * @Title: Fastjson2 实现 JSON 字段类型处理器
 * @Description: fastjson2转换类
 * @author: lihainan
 * @date: 2023年12月26日 下午1:22:04
 * @version: V1.0
 * @Copyright: nit逆天开源版权
 */
@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class Fastjson2TypeHandler extends AbstractJsonTypeHandler<Object> {
    private final Class<?> type;
    
    public Fastjson2TypeHandler(Class<?> type) {
        if (log.isTraceEnabled()) {
            log.trace("FastjsonTypeHandler(" + type + ")");
        }
        Assert.notNull(type, "Type argument cannot be null");
        this.type = type;
    }
    @Override
    protected Object parse(String json) {
        return JSON.parseObject(json, type);
    }
    @Override
    protected String toJson(Object obj) {
        return JSON.toJSONString(obj);
    }
}

entity定义

package com.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.config.Fastjson2TypeHandler;
import com.dto.business.ActivityList;
import lombok.Data;
import java.io.Serializable;
@Data
@TableName(value = "my_test", autoResultMap = true)//autoResultMap = true 这个必须有哦
public class MyTest implements Serializable {
    /**
     * 主键
     */
    @TableId
    private Long id;
	/**
	* json类型转换为javabean
	*/
    @TableField(typeHandler = JacksonTypeHandler.class)
    private Activity txt;
    /**
	* json数组类型转换为javabean
	*/
    @TableField(typeHandler = Fastjson2TypeHandler.class)
    private List<Activity> txtArray;
}

@Data
public class Activity implements Serializable {
    /**
     * 主键
     */
    @TableId
    private Long id;
    /**
     * 名称
     */
    private String activityName;
}

curd

 	@Resource
    private MyTestDao myTestDao;
    @GetMapping("/insert")
    public void insert() {
        Activity node = new Activity();
        node.setActivityName("撒旦士大夫");
        Activity node2 = new Activity();
        node2.setActivityName("222撒旦士大夫");
  
        List<Activity> list = Lists.newArrayList();
        list.add(node);
        list.add(node2);
        
        MyTest myTest = new MyTest();
        myTest.setTxt(node);
        myTest.setTxtArray(list);
        myTestDao.insert(myTest);
    }

select

MyTest test = new MyTest();
Activity node = new Activity();
node.setActivityName("大夫");
node.setType(1);
test.setTxt(node);

LambdaQueryWrapper<MyTest> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.apply(Objects.nonNull(test.getTxt()), "txt -> '$.activityName' LIKE CONCAT('%',{0},'%')", test.getTxt().getActivityName())//模糊查询
    .apply(Objects.nonNull(test.getTxt().getType()), "txt -> '$.type' = {0}", test.getTxt().getType());//精确查询

List<MyTest> res = myTestDao.selectList(queryWrapper);

json索引优化

mysql官方说明地址：https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

json类型，无法直接创建索引，需要创建二级索引，创建虚拟列实现索引需求，

CREATE TABLE `my_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `txt` json DEFAULT NULL,
  `txt_array` json DEFAULT NULL,
   -- 虚拟列 需要设置表达式
  `js_id` int GENERATED ALWAYS AS (json_extract(`txt`,_utf8mb4'$.id')) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=357 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 增加虚拟列
ALTER TABLE `brm_pro_test`.`my_test` 
ADD COLUMN `sd` varchar AS (json_extract(`txt`,_utf8mb4'$.activityName')) VIRTUAL NULL 
-- 创建索引
ADD INDEX `index_key_id`(`js_id` ASC) USING BTREE;

-- 使用虚拟列查询
explain
SELECT * FROM my_test WHERE js_id = 1;
+----+-------------+---------+------------+------+---------------+--------------+---------+------
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
|  1 | SIMPLE      | my_test | NULL       | ref  | index_key_id  | index_key_id | 5       | const |  1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------

-- 无索引
mysql> explain SELECT * FROM my_test WHERE JSON_EXTRACT(txt,'$[1]') = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
|  1 | SIMPLE      | my_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  356 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-
1 row in set (0.17 sec)

虚拟列：目前mybiteplus 只能使用动态sql 执行查询。实体无法映射字段的