mybatis-plus configuration for custom data types

Working and studying at the same time is fast, and all kinds of strange requirements make my head spin. Without further ado, let's get started.

The known requirement is to design a questionnaire table, and currently the questionnaire only has multiple-choice questions. How should we design the table and database?

The question table is similar to the following:

Pain Level	Description	Score
0	No pain	0
1	Mild pain	2
2	Severe pain	4

There are multiple options under each question, and each option has a different score, which is used to calculate the final score of the questionnaire.

If we create a single table, it will be very complex and the logic will not be clear. It is better to extract the questions.

Options Table

/**
 * Description of the option
 */
private String description;
/**
 * Score of the option
 */
private Integer score;

Then save the JSON directly in the database, which will also make future expansion easier.

So how do we save JSON to the database?

Questionnaire Table

	//Questionnaire ID
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
    //Question name
    private String name;

    private Integer typeId;
    //Option 1, corresponding to the options table
    private OptionDto op1;

It's simple, just use fastjson. Rewrite the set method of the specific field in the questionnaire table.

 public void setOp1(OptionDto dto) {
     //Convert the OptionDto object to a JSON string
        this.op1 = JSON.toJSONString(dto);
    }

This way, we can convert OptionDto to JSON and store it in the database.

However, when I retrieve data from the database, I find that the OptionDto data becomes null. The query statement is correct, and the SQL can retrieve the data, but it just cannot be assigned.

This is because MyBatis-Plus cannot handle our custom data types. Our database is VARCHAR, but the code is not String, so it cannot be assigned.

Type Handler#

The good news is that MyBatis-Plus provides a custom type handler to handle the data flow between code and the database, which is the TypeHandler.

To use TypeHandler, you need to configure it in the configuration file.

# Configure the package path of custom type converters
mybatis-plus.type-handlers-package=xyz.qinfengge.handel

Then configure the handler class.

@MappedTypes({OptionDto.class})
@MappedJdbcTypes({JdbcType.VARCHAR})
public class OptionsTypeHandler extends BaseTypeHandler<OptionDto> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, OptionDto optionDto, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i, optionDto.toString());
    }

    @Override
    public OptionDto getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return resultSet.getString(s) == null ? null : str2Dto(resultSet.getString(s));
    }

    @Override
    public OptionDto getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return resultSet.getString(i) == null ? null : str2Dto(resultSet.getString(i));
    }

    @Override
    public OptionDto getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getString(i) == null ? null : str2Dto(callableStatement.getString(i));
    }

    //Convert String to OptionDto
    public static OptionDto str2Dto(String string) {
        //Convert the JSON string to OptionDto object
        return JSON.parseObject(string, OptionDto.class);
    }
}

The @MappedTypes annotation specifies the data type in our code.
The @MappedJdbcTypes annotation specifies the data type in the database.

Override the four methods inside the class. The first method is for saving data, so we directly save optionDto.toString() to the database. The next three methods are for query statements, where we need to convert the JSON to an object. If not written, it will be null.

Finally, specify the type handler used for the custom data type in the entity class.

	//Questionnaire ID
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
    //Question name
    private String name;

    private Integer typeId;
    //Option 1, corresponding to the options table
    //Specify the type handler
    @TableField(typeHandler = OptionsTypeHandler.class)
    private OptionDto op1;

That's it.

How to Configure Custom Data Types TypeHandler in MyBatis-Plus

Learning and Practical Use of MyBatis TypeHandler

An Elegant Solution for Storing and Retrieving JSON Fields in MyBatis - TypeHandler (Part 1)