MySql-4-查询字段类型是JSON的结果映射-直接上代码

1、构建实体

public class PortalNewsTitleListRespVO implements Serializable {
    private static final long serialVersionUID = 1735287780771L;

    @ApiModelProperty(value = "id（雪花）")
    private String id;

    @ApiModelProperty(value = "标题")
    private String title;

    @ApiModelProperty(value = "配图")
    private UploadVO entryPicture;

    @ApiModelProperty(value = "创建时间")
    @JSONField(format = "yyyy-MM-dd")
    private Date createdTime;
}

public class UploadVO implements Serializable {
    private static final long serialVersionUID = 1735287763708L;
 
    private String fileId;

    private String fileUrl;

    private String fileName;

    private String busibessJoin;

    private String contentType;
}

2、构建Mapper.xml

     entryPicture 字段类型是JSON格式，映射如下：

     SQL查询语句如下：

<?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.e6yun.project.portal.mapper.ProtalMenuMapper">
 
    <resultMap id="getTitleListResultMap" 
                    type="com.e6yun.project.portal.vo.client.PortalNewsTitleListRespVO">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="created_time" property="createdTime" jdbcType="TIMESTAMP"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="entry_picture" property="entryPicture" jdbcType="OTHER"
                typeHandler="com.e6yun.project.tms.common.framework.support.mp.handler.TmsBaseJsonObjTypeHandler"/>
    </resultMap>
 
     <select id="getTitleListByMenuIds" resultMap="getTitleListResultMap">
        SELECT
            pn.id,
            pn.menu_id,
            pn.title,
            pn.entry_picture,
            pn.created_time
        FROM
            portal_news pn
        ORDER BY
            pn.created_timeDESC
            LIMIT #{count}
    </select>

</mapper>

3、数据库表设计 

CREATE TABLE `news` (
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'id（雪花）',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '标题',
  `entry_picture` json DEFAULT NULL COMMENT '入口配图',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='信息';