mysql数据库，SpringBoot的maven项目实现一个包含id，json的value数据，数据生成时间按照insert时间增查的功能

mysql数据库，SpringBoot的maven项目实现一个包含id，json的value数据，数据生成时间按照insert时间增查的功能

id实现自增长若在navicat中配置，需要将id字段的属性配置为自动递增勾选上

并且在pojo文件中将id字段设置

1.创建表的语句

这是实现一个请求接口时保存请求接口数据的功能，表的名字和列名都依照此创建

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for interface_data
-- ----------------------------
DROP TABLE IF EXISTS `interface_data`;
CREATE TABLE `interface_data`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `interfaceName` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '接口名',
  `createTime` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间',
  `interfaceValues` json NULL COMMENT '接口数据',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.mapper.xml文件

<?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.atwisdom.star.service.mapper.InterfaceDataMapper">

    <resultMap id="BaseResultMap" type="com.atwisdom.star.service.pojo.InterfaceData">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="interfaceName" column="interfaceName" jdbcType="VARCHAR"/>
            <result property="interfaceValues" column="interfaceValues" jdbcType="JAVA_OBJECT"
                    typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
            <result property="createTime" column="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,interfaceName,interfaceValues,createTime
    </sql>
</mapper>

3.mapper文件

package com.atwisdom.star.service.mapper;


import com.atwisdom.star.service.pojo.InterfaceData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author 
 * @description 针对表【interface_data】的数据库操作Mapper
 * @createDate 2024-04-18 00:51:14
 * @Entity com.atwisdom.star.service.pojo.InterfaceData
 */
public interface InterfaceDataMapper extends BaseMapper<InterfaceData> {
}

4.pojo文件

package com.atwisdom.star.service.pojo;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.*;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 *
 */
@TableName(value ="interface_data",autoResultMap = true)
@Data
public class InterfaceData implements Serializable {
    /**
     *
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 接口名
     */
    @TableField(value = "interfaceName")
    private String interfaceName;

    /**
     * 时间
     */
    @TableField(value = "createTime",fill = FieldFill.INSERT)
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    /**
     * 接口数据
     */
    @TableField(value = "interfaceValues",typeHandler = FastjsonTypeHandler.class)
    private JSONObject interfaceValues;


    @TableField(exist = false)
    private static final long serialVersionUID = 1L;

    public InterfaceData(String interfaceName, JSONObject interfaceValues) {
        this.interfaceName = interfaceName;
        this.interfaceValues = interfaceValues;
    }

    public InterfaceData(Integer id, String interfaceName, JSONObject interfaceValues, LocalDateTime createTime) {
        this.id = id;
        this.interfaceName = interfaceName;
        this.interfaceValues = interfaceValues;
        this.createTime = createTime;
    }

    public InterfaceData() {
    }

}

5.service文件

package com.atwisdom.star.service.service;

import cn.hutool.core.date.DateTime;
import com.atwisdom.star.service.pojo.InterfaceData;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

public interface InterfaceDataService extends IService<InterfaceData> {
    /**
     * 根据开始和结束时间查询接口数据
     * @param beginTime
     * @param endTime
     * @return
     */
    List<InterfaceData> queryByTime(DateTime beginTime, DateTime endTime);

    int insert (InterfaceData interfaceData);
}

6.service的Impl文件

package com.atwisdom.star.service.service.impl;

import cn.hutool.core.date.DateTime;
import com.atwisdom.star.service.mapper.InterfaceDataMapper;
import com.atwisdom.star.service.pojo.InterfaceData;
import com.atwisdom.star.service.service.InterfaceDataService;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @ClassName: InterfaceDataServiceImpl
 * @ Description:
 * @author:王红艳
 * @date:
 */
@Service
public class InterfaceDataServiceImpl extends ServiceImpl<InterfaceDataMapper, InterfaceData>
    implements InterfaceDataService {

    @Autowired
    InterfaceDataMapper interfaceDataMapper;

    /**
     * 插入接口数据
     * @param interfaceData
     * @return
     */
    @Override
    public int insert (InterfaceData interfaceData) {
        return interfaceDataMapper.insert(interfaceData);
    }

    /**
     * 根据开始和结束时间查询接口数据
     * @param beginTime
     * @param endTime
     * @return
     */
    @Override
    public List<InterfaceData> queryByTime(DateTime beginTime, DateTime endTime){
        LambdaQueryWrapper<InterfaceData> wrapper = new LambdaQueryWrapper<>();
        wrapper.ge(InterfaceData::getCreateTime,beginTime);
        wrapper.le(InterfaceData::getCreateTime,endTime);
        wrapper.orderByDesc(InterfaceData::getCreateTime);
        return interfaceDataMapper.selectList(wrapper);
    }
}

7.使用示例

插入时：

//data接口的json数据
interfaceDataService.insert(new InterfaceData("name",data));

查询时：

        List<InterfaceData> list = interfaceDataService.queryByTime(DateTime.of(data.getDate("startTime")),DateTime.of(data.getDate("endTime")));