Working and studying at the same time is fast-paced, with various strange requirements overwhelming me. 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.
Option 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 extensions 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 option 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 and SQL are correct, but the value 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 the value 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 the TypeHandler, you need to configure it in the configuration file.
# Configure the package path for 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
@MappedTypesannotation specifies the data type in our code. - The
@MappedJdbcTypesannotation 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 option table
//Specify the type handler
@TableField(typeHandler = OptionsTypeHandler.class)
private OptionDto op1;
That's it.