使用mybatisplus接收mysql字段为json类型的数据_mybatisplus查询json字段用对象接收

一.数据库设计

CREATE TABLE `inv_learning_examination_questions`  (
  `id` bigint(20) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '题目',
  `options` json NULL COMMENT '选项',
  `standard_answer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标准答案',
  `answer_analysis` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '答案解析',
  `open_range` tinyint(4) NULL DEFAULT NULL COMMENT '开放范围',
  `business_area` tinyint(4) NULL DEFAULT NULL COMMENT '业务领域',
  `difficulty_level` tinyint(4) NULL DEFAULT NULL COMMENT '难度等级',
  `topic_type` tinyint(4) NULL DEFAULT NULL COMMENT '选题类型',
  `views` int(11) NULL DEFAULT NULL COMMENT '浏览量',
  `collect` int(11) NULL DEFAULT NULL COMMENT '收藏量',
  `status` tinyint(4) NULL DEFAULT NULL COMMENT '发布状态',
  `release_time` datetime(0) NULL DEFAULT NULL COMMENT '发布时间'
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

二.实体类(切记实体类@TableName一定要加上autoResultMap = true属性,否则查不出来该属性的值)

package com.innovation.desk.domain;
 
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.innovation.common.base.BaseLLEntity;
import com.innovation.common.utils.DateUtil;
import com.innovation.desk.handler.OptionHandler;
import lombok.Data;
import lombok.EqualsAndHashCode;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.format.annotation.DateTimeFormat;
 
import java.util.Date;
import java.util.List;
 
/**
 * LearningExaminationQuestions实体类
 *
 * @author admin
 * @since 2023/03/03
 */
@Data
@TableName(value = "inv_learning_examination_questions",autoResultMap = true)
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "LearningExaminationQuestions对象", description = "LearningExaminationQuestions对象")
public class LearningExaminationQuestions extends BaseLLEntity {
 
    private static final long serialVersionUID = 1L;
 
    @ApiModelProperty(value = "题目")
    private String title;
 
    @TableField(typeHandler = OptionHandler.class)
    @ApiModelProperty(value = "选项")
    private List<Option> options;
 
    @TableField(typeHandler = FastjsonTypeHandler.class)
    @ApiModelProperty(value = "标准答案")
    private List<String> standardAnswer;
 
    @ApiModelProperty(value = "答案解析")
    private String answerAnalysis;
 
    @ApiModelProperty(value = "开放范围")
    private Integer openRange;
 
    @ApiModelProperty(value = "业务领域")
    private Integer businessArea;
 
    @ApiModelProperty(value = "难度等级")
    private Integer difficultyLevel;
 
    @ApiModelProperty(value = "选题类型")
    private Integer topicType;
 
    @ApiModelProperty(value = "浏览量")
    private Integer views;
 
    @ApiModelProperty(value = "收藏量")
    private Integer collect;
 
    @ApiModelProperty(value = "发布状态 0.待发布 1.已发布")
    private Integer status;
 
    @ApiModelProperty(value = "发布状态 0.待发布 1.已发布")
    @DateTimeFormat(pattern = DateUtil.PATTERN_DATETIME)
    @JsonFormat(pattern = DateUtil.PATTERN_DATETIME, timezone = "GMT+8")
    private Date releaseTime;
}

1.如果接收参数是实体类或List<String>,可以直接使用mybatisplus自带的解析处理器即可,例如在属性上添加

@TableField(typeHandler = FastjsonTypeHandler.class)

此处FastjsonTypeHandler有多个类型可供选择:比如AbstractJsonTypeHandler,AbstractSqlParserHandler,FastjsonTypeHandler,GsonTypeHandler,JacksonTypeHandler,MybatisEnumTypeHandler

2.如果接收参数是个List<实体类>,这里需要注意:以上提供的解析器不能提供完全解析,这里你需要自定义解析器做定制化解析,以下是解析器的源码

原因:type属性被注入进来的只是List的字节码文件,通过parse方法只能将json转化为List<JsonObject>对象,而JsonObject不能强转为相应的实体类,所以在获取到解析后的对象遍历的时候会报类型转换错误异常,这时可以重写此handler的parse方法来实现自己的目的,以下是将json转化为List<Option>的处理器

/**
 *  自定义CardContent转换处理类
 * @author Administrator
 */
public class OptionHandler extends FastjsonTypeHandler {
 
    public OptionHandler(Class<?> type) {
        super(type);
    }
 
    @Override
    protected Object parse(String json) {
        return JSON.parseArray(json, Option.class);
    }
 
    @Override
    protected String toJson(Object obj) {
        return super.toJson(obj);
    }
}

 添加完成后只需在实体类的对应属性上添加注解

@TableField(typeHandler = OptionHandler.class)即可实现解析

如果使用了xml文件可参考以下方式

<result column="options" property="options" jdbcType="VARCHAR"
                typeHandler="com.innovation.desk.handler.OptionHandler"/>