Mapping MySQL JSON Fields with MyBatis-Plus

Posted on In , Word count in article: 515 Reading time ≈ 2 mins.

Preface

MySQL supports fields of type JSON. In comparison to string-type fields, JSON fields offer the following advantages:

  • Automatic validation of JSON syntax
  • Underlying support for quick access to elements within the JSON. There’s no need to read the entire string and then parse it into a JSON object.

Logically, JSON is no different from a POJO. Spring MVC has already implemented automatic conversion between the two at the Controller tier, evident in request parameters and return values. So, how can the Repository tier achieve automatic conversion?

This article demonstrates achieving seamless, non-intrusive mapping (ORM) between JSON and POJO using MyBatis-Plus in the Repository tier.

Demo

Table

1
2
3
4
5
6
CREATE TABLE `person` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `log` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Dependencies

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Data
@TableName(value = "person", autoResultMap = true)
public class Person {

    private Long id;

    private String name;

    @TableField(typeHandler = JacksonTypeHandler.class)
    private PersonLog log;

    @Data
    public static class PersonLog {
        List<String> hobbies;
        Integer age;
        String address;
    }
}

The PersonLog here is the focal point of our discussion, aiming to map it to JSON.

The Person class needs two annotations, usually used together:

TableName#autoResultMap: Responsible for converting JSON to POJO during query.

TableField#typeHandler: Responsible for converting POJO to JSON during insert. Since spring-web-starter has already imported the Jackson dependency, we can directly use JacksonTypeHandler.

mapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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.example.demo.mapper.PersonMapper">

    <resultMap id="BaseResultMap" type="com.example.demo.entity.Person">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="log" property="log" jdbcType="LONGVARCHAR" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, name, log
    </sql>

</mapper>

In the mapper file, specify the full class name of the typeHandler for the log field.

Testing

1
2
3
@Mapper
public interface PersonMapper extends BaseMapper<Person> {
}

Service tier is omitted.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@RestController
public class PersonController {
    @Autowired
    private PersonMapper personMapper;

    @PostMapping("/json")
    public Integer testJSON2BeanMap(@RequestBody Person person) {
        return personMapper.insert(person);
    }

    @GetMapping("/json/{id}")
    public Person getPersonById(@PathVariable("id") Long id) {
        return personMapper.selectById(id);
    }
}

Test both methods; if no errors occur, it indicates success. We can also check the database after insertion.

The non-intrusive ORM mentioned in the Preface implies that we don’t need to manually execute the conversion between JSON and PersonLog in the code.

Conclusion

This article demonstrates achieving seamless, non-intrusive mapping between JSON and POJO using MyBatis-Plus in the Repository tier.

My exploration of this issue originated from the requirements of a business logic during my internship. When I searched for information online, I found that the descriptions in the posts were not quite complete or intuitive. Therefore, I documented the solution and wrote this article.

Reference

The JSON Data Type

mybatis-plus处理mysql中json格式方法