How does MybatisPlus handle the json type of Mysql

MybatisPlus handles Mysql's json type

1. Define the JSON field in the database table;

2. Add @TableName (autoResultMap = true) to the entity class, and add it to the JSON Add @TableField(typeHandler = JacksonTypeHandler.class) to the field mapping attributes;

1. There is an attribute in the entity class that is other objects, or List; mysql is used when storing in the database json format, at this time you can use an annotation @TableField(typeHandler = JacksonTypeHandler.class) of mybatis plus

@TableField(typeHandler = JacksonTypeHandler.class)

In this way, the object can be automatically converted to json format when saving

2 .So how to map when taking it out? There are two situations:

a: When xml is not used:

@Data
@TableName(value = "person",autoResultMap = true)

b: When it is used When downloading the xml file:

<result property="advance" column="advance" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>

MyBatis Plus - How to use the ResultMap constructed by autoResultMap in xml

MyBatis Plus has a big flaw, which is the ResultMap used when inserting and selecting are different, the fix is ​​to add the annotation @TableName(autoResultMap = true) to the entity class. However, this autoResultMap cannot be used on custom methods, and only takes effect on MyBatis Plus built-in methods.

Show the problems of autoResultMap

Entity class Person

There are custom typehandlers in this entity class: IntegerListTypeHandler, StringListTypeHandler

@TableName(autoResultMap = true)
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    @TableField(typeHandler = IntegerListTypeHandler.class)
    private List<Integer> orgIds;
    @TableField(typeHandler = StringListTypeHandler.class)
    private List<String> hobbies;
}

@Mapper
public interface PersonMapper extends BaseMapper<Person> {
    /**
     * 自定义的根据Id获取Person的方法，与MyBatis-Plus中的selectById相同的功能（但是不能使用autoResultMap生成的ResultMap）.
     */
    @Select("SELECT * FROM person WHERE id=#{id}")
    Person selectOneById(int id);
}

• The custom method cannot get some fields

Because the orgIds and hobbies in Person require a custom typeHandler, the custom method uses resultType= Person, not the generated ResultMap, so they are all null

Person person = new Person();
person.setAge(1);
person.setName("tim");
person.setOrgIds(Lists.newArrayList(1,2,3));
person.setHobbies(Lists.newArrayList("basketball", "pingpong"));
personMapper.insert(person);
# 可以得到正确的字段值
Person personInDb = personMapper.selectById(person.getId());
# orgIds和hobbies都为null
personInDb = personMapper.selectOneById(person.getId());
Preconditions.checkArgument(personInDb.getHobbies().equals(person.getHobbies()));
Preconditions.checkArgument(personInDb.getName().equals(person.getName()));
Preconditions.checkArgument(personInDb.getAge().equals(person.getAge()));
Preconditions.checkArgument(personInDb.getOrgIds().equals(person.getOrgIds()));

Improvement

Set @ResultMap("mybatis-plus_Person")

/**
 * 设置了ResultMap为`mybatis-plus_Person`后就可以拿到正确的值.
 */
@ResultMap("mybatis-plus_Person")
@Select("SELECT * FROM person WHERE id=#{id}")
Person selectOneById(int id);

Name The rule is: mybatis-plus_{entity class name}

• Personal understanding

MyBatis Plus itself is not a dynamic ORM, but just a When mybatis is initialized, common SQL statements and resultMap settings are provided for mybatis and will not change the behavior of MyBatis itself.

• FAQ

@TableField(typeHandler = IntegerListTypeHandler.class) does not take effect: the resultType is not configured on the custom method

MyBatis-Plus - JacksonTypeHandler VS FastjsonTypeHandler

JacksonTypeHandler

• Support MVC JSON parsing

• Support MySQL JSON parsing

The traditional method is to do typeHandler mapping processing through the XML SQL resultMap, but This will affect the function of MP, so JacksonTypeHandler is compatible with the function of MP and supports MySQL JSON parsing.

FastjsonTypeHandler

• Supports MVC JSON parsing

• Does not support MySQL JSON parsing

Can be supported through XML, but the MP feature will be lost.

<resultMap id="rxApiVO" type="RxApiVO" >
    <result column="api_dataway" property="apiDataway" typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler" />
</resultMap>

Note:

When parsing MVC JSON, you don’t need to add @TableName(value = “t_test”, autoResultMap = true) [highlighted part], but When MySQL JSON is parsing the query, if it is not added, the result will be null

MySQL JSON When parsing the query, only the JSON format is supported: {"name":"Tom","age":12}, not supported :{"name":"Tom","age":12} and "{"name":"Tom","age":12}"

MybatisPlus reads and writes Mysql's json field

Preconditions

Make sure that the mysql version is 5.7

1. Create a new mysql table and add a json field

2. Pojo Class

package com.cxstar.domain;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import java.io.Serializable;
import java.util.Date;
@lombok.Data
@TableName(autoResultMap = true)
public class Data implements Serializable {
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;       
	
	// 部分字段省略-------------
    private String title;       
    private String author;         
    private String publisher; 
    // -----------------------   
    @TableField(typeHandler = FastjsonTypeHandler.class)
    private JSONObject aggJson;
}

3. Test class

package com.cxstar;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.cxstar.domain.Data;
import com.cxstar.domain.SearchMsg;
import com.cxstar.mapper.DataMapper;
import com.cxstar.service.OrderService;
import com.cxstar.service.spider.impl.*;
import com.cxstar.service.utils.ExecutorThread;
import com.cxstar.service.utils.SpiderThread;
import com.cxstar.service.utils.SynContainer;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;
@SpringBootTest
class OrderApplicationTests {
    @Autowired
    DataMapper dataMapper;
    @Test
    void testJson() {
		// insert -----------------------------------
        Data data = new Data();
        data.setTitle("计算机安全技术与方法");
        data.setPublisher("<<计算机技术>>编辑部出版");
        JSONObject jb = new JSONObject();
        jb.put("searchKey", "英格");
        jb.put("curPage", "1");
        JSONArray js = new JSONArray();
        js.add("西北政法大学");
        js.add("西安理工大学");
        jb.put("source", js);
        data.setAggJson(jb);
        dataMapper.insert(data);
        // ------------------------------------------
        // select --------------------------------------
        Data data1 = dataMapper.selectById(5837);
        JSONObject jb2 = data1.getAggJson();
        System.out.println(jb2.getJSONArray("source"));
        // ---------------------------------------------
		
		// group by -----------------------------------------------
		LambdaQueryWrapper<Data> lqw = new LambdaQueryWrapper<>();
        lqw.select(Data::getAggJson);
        lqw.groupBy(Data::getAggJson);
        List<Data> dataList = dataMapper.selectList(lqw);
        System.out.println(dataList);
        // --------------------------------------------------------
    }
}

The above is the detailed content of How does MybatisPlus handle the json type of Mysql. For more information, please follow other related articles on the PHP Chinese website!