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")));