MySQL 8.0 JSON数据类型与MyBatis结合使用

前言

MYSQL 8.0 JSON数据类型基础应用

---

1.表

CREATE TABLE `tk_test_json` (
  `id` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `no` varchar(100) DEFAULT NULL,
  `users` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2.程序

@Data
@TableName(value="tk_test_json",autoResultMap = true)
public class TestDO {

    @TableId(value = "id", type = IdType.ASSIGN_UUID)
    private String id;

    private  String no;


    @TableField(typeHandler = JacksonTypeHandler.class)
    private List<TestUserDO> users;

}

@Data
public class TestUserDO {

    private String id;

    private String mail;

    private Boolean success;

}

<?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="xxx.TestExtraMapper">

    <resultMap type="xxx.entity.TestDO" id="BaseResultMap">
        <result property="id" column="id" jdbcType="VARCHAR"/>
        <result property="no" column="no" jdbcType="VARCHAR"/>
        <result property="users" column="users" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>

    </resultMap>

    <select id="selectAll" resultMap="BaseResultMap">
        select * from tk_test_json
    </select>

    <select id="conditionSelect" resultMap="BaseResultMap">
        select * from hr_pana_ticket_new.tk_test_json ttj where ttj.users <![CDATA[->>]]> '$[*].id' like '%-9d4e-0ff1063e9%'
    </select>

</mapper>

如果使用Mybatis Plus ，一定要使用autoResultMap = true，否则，mybatis plus 查询返回的JSON对应为null。