MySQL存JSON数据

一、实体类

//留言
public class Message{
    //留言内容
    private String messageContent;

    //附件(JSON格式)
    private String attachments;

    //评论数
    private Integer commentNum;

    //点赞数
    private Integer likeNum;
}

二、VO类

//留言附件
public class MessageAttachment {
    //图片地址
    private String url;

    //名字
    private String name;
    
    //扩展名
    private String extension;
}

//发布留言
public class CreateMessageVO {

    //用户ID
    private Long memberId;

    //留言信息
    private String messageContent;

    //附件
    private List<MessageAttachment> attachmentList;
}

三、Service关键代码

3.1 对象转JSON字符串

Message message = MessageConvert.instance.voToEntity(vo);
if (CollectionUtil.isNotEmpty(vo.getAttachmentList())) {
     String attachments = JSON.toJSONString(vo.getAttachmentList());
     message.setAttachments(attachments);
}

3.2 JSON字符串转对象

if(StringUtils.isNotBlank(d.attachments())){
   List<MessageAttachment> attachmentList = JSONObject.parseArray(d.attachments(), MessageAttachment.class);
}

四、数据库字段

 `attachments` json DEFAULT NULL COMMENT '附件',

CREATE TABLE `t_message` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `message_content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '留言内容',
  `attachments` json DEFAULT NULL COMMENT '附件',
  `comment_num` int DEFAULT '0' COMMENT '评论数',
  `like_num` int DEFAULT '0' COMMENT '点赞数',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除 0未删除 1已删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `IDX_deleted` (`deleted`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1367316728150794278 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='留言'