因为数据量庞大,某个沙雕同事写的代码,所有增删改”查“全都加了日志,导致服务器不够用,领导又不敢得罪人家,只能让我填坑,写了此功能,主要是接收数据然后保存起来,一周创建一个新表
<?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.fy.log.mapper.BusinessFyLogMapper">
<resultMap id="BaseResultMap" type="com.fy.log.domain.BusinessFyLog">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="type" jdbcType="CHAR" property="type"/>
<result column="business_info" jdbcType="VARCHAR" property="businessInfo"/>
<result column="create_by" jdbcType="VARCHAR" property="createBy"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<result column="remark" jdbcType="VARCHAR" property="remark"/>
</resultMap>
<insert id="insert" parameterType="com.fy.log.domain.BusinessFyLog">
<!--
WARNING - @mbg.generated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Tue Apr 25 09:04:10 UTC 2023.
-->
insert into business_fy_log (`id`, `type`, `create_by`,
`create_time`, `update_by`, `update_time`,
`remark`, `business_info`)
values (#{id,jdbcType=INTEGER}, #{type,jdbcType=CHAR}, #{createBy,jdbcType=VARCHAR},
#{createTime,jdbcType=TIMESTAMP}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP},
#{remark,jdbcType=VARCHAR}, #{businessInfo,jdbcType=LONGVARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.fy.log.domain.BusinessFyLog">
<!--
WARNING - @mbg.generated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Tue Apr 25 09:04:10 UTC 2023.
-->
insert into `${searchValue}`
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
`id`,
</if>
<if test="type != null">
`type`,
</if>
<if test="createBy != null">
`create_by`,
</if>
<if test="createTime != null">
`create_time`,
</if>
<if test="updateBy != null">
`update_by`,
</if>
<if test="updateTime != null">
`update_time`,
</if>
<if test="remark != null">
`remark`,
</if>
<if test="businessInfo != null">
`business_info`,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="type != null">
#{type,jdbcType=CHAR},
</if>
<if test="createBy != null">
#{createBy,jdbcType=VARCHAR},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateBy != null">
#{updateBy,jdbcType=VARCHAR},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="remark != null">
#{remark,jdbcType=VARCHAR},
</if>
<if test="businessInfo != null">
#{businessInfo,jdbcType=LONGVARCHAR},
</if>
</trim>
</insert>
<select id="selectList" resultMap="BaseResultMap">
<foreach collection="params.tableNames" close=")" item="tableName" open="(" separator="union all">
select * from ${tableName}
<where>
<if test="type != null and type != ''">
and type like concat(#{type},'%')
</if>
<if test="businessInfo != null and businessInfo != ''">
and businessInfo like concat('%',#{businessInfo},'%')
</if>
<if test="params != null and params.size >0">
<if test="params.beginTime != null">
and #{params.beginTime} <= create_time
</if>
<if test="params.endTime != null">
and create_time <= #{params.endTime}
</if>
</if>
</where>
</foreach>
order by create_time
</select>
<select id="getTableName" resultType="java.lang.String">
SELECT table_name
FROM information_schema.TABLES
WHERE table_name LIKE concat('%', #{1}, '%')
ORDER BY create_time desc
</select>
<insert id="createTable">
CREATE TABLE IF NOT EXISTS `${tableName}` (
`id` INT ( 0 ) NOT NULL AUTO_INCREMENT,
`type` CHAR ( 10 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`business_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`create_by` VARCHAR ( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`create_time` datetime ( 0 ) NULL DEFAULT NULL,
`update_by` VARCHAR ( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`update_time` datetime ( 0 ) NULL DEFAULT NULL,
`remark` VARCHAR ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY ( `id` ) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
</insert>
</mapper>
package com.fy.log.mapper;
import com.fy.log.domain.BusinessFyLog;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @ClassName BusinessFyLogMapper
* @author: maliang
* @Description TODO
* @date 2023/4/25 16:51
* @Version 1.0版本
*/
public interface BusinessFyLogMapper {
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table business_fy_log
*
* @mbg.generated Tue Apr 25 09:04:10 UTC 2023
*/
int insert(BusinessFyLog record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table business_fy_log
*
* @mbg.generated Tue Apr 25 09:04:10 UTC 2023
*/
int insertSelective(BusinessFyLog record);
List<BusinessFyLog> selectList(BusinessFyLog businessFyLog);
void createTable(@Param("tableName") String tableName);
List<String> getTableName(String s);
}
public interface BusinessFyLogService {
void add(BusinessFyLog businessFyLog);
List<BusinessFyLog> page(Integer pageNum, Integer pageSize, BusinessFyLog businessFyLog);
List<String> getTableName(String table);
void createTable(String table);
}
@Service
public class BusinessFyLogServiceImpl implements BusinessFyLogService {
@Autowired
private BusinessFyLogMapper businessFyLogMapper;
@Override
public void add(BusinessFyLog businessFyLog) {
if (Objects.isNull(businessFyLog) || StringUtils.isBlank(businessFyLog.getBusinessInfo())) {
return;
}
String businessSql = businessFyLog.getBusinessInfo();
String type;
if (StringUtils.containsIgnoreCase(businessSql, "insert")) {
type = "新增";
} else if (StringUtils.containsIgnoreCase(businessSql, "update")) {
type = "更新";
} else if (StringUtils.containsIgnoreCase(businessSql, "delete")) {
type = "删除";
} else if (StringUtils.containsIgnoreCase(businessSql, "select")) {
type = "查询";
} else {
type = "其他";
}
businessFyLog.setCreateBy("channel");
businessFyLog.setCreateTime(new Date());
businessFyLog.setType(type);
if (StrUtil.isBlank(StringUtils.lastTableName)) {
List<String> tableNamesList = getTableName("business_fy_log");
StringUtils.lastTableName = tableNamesList.get(0);
}
businessFyLog.setSearchValue(StringUtils.lastTableName);
businessFyLogMapper.insertSelective(businessFyLog);
}
@Override
public List<BusinessFyLog> page(Integer pageNum, Integer pageSize, BusinessFyLog businessFyLog) {
if (CollUtil.isEmpty(StringUtils.tableNameList)) {
StringUtils.tableNameList.addAll(getTableName("business_fy_log"));
}
businessFyLog.getParams().put("tableNames", StringUtils.tableNameList);
PageHelper.startPage(pageNum, pageSize);
return businessFyLogMapper.selectList(businessFyLog);
}
/**
* 查询表名所有衍生表
*
* @param table 表名
* @return 衍生表
*/
public List<String> getTableName(String table) {
// 查询表
List<String> tableNameList = businessFyLogMapper.getTableName(table);
// 如果不存在表,就建一个
if (CollUtil.isEmpty(tableNameList)) {
table = table + "_1";
businessFyLogMapper.createTable(table);
return Collections.singletonList(table);
} else {
// 有的话就用现成的
return tableNameList;
}
}
@Override
public void createTable(String table) {
businessFyLogMapper.createTable(table);
}
}
package com.fy.log.controller;
import com.fy.log.domain.BusinessFyLog;
import com.fy.log.domain.vo.TableDataInfo;
import com.fy.log.service.BusinessFyLogService;
import com.fy.log.utils.AjaxResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
/**
* @ClassName BusinessFyLogController
* @author: maliang
* @Description 日志控制器
* @date 2023/4/25 16:40
* @Version 1.0版本
*/
@RestController
@RequestMapping("fyLog")
public class BusinessFyLogController {
@Autowired
private BusinessFyLogService businessFyLogService;
@PostMapping("add")
public AjaxResult add(@RequestBody BusinessFyLog businessFyLog) {
businessFyLogService.add(businessFyLog);
return AjaxResult.success();
}
@GetMapping("page")
public TableDataInfo page(BusinessFyLog businessFyLog, @RequestParam(value = "page", defaultValue = "1") Integer pageNum, @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
return AjaxResult.getDataTable(businessFyLogService.page(pageNum, pageSize, businessFyLog));
}
}
@Component
public class AutoTask {
@Autowired
private BusinessFyLogService businessFyLogService;
/**
* 自动创建衍生表
*/
@Scheduled(cron = "0 0 1 * * ?")
// @Scheduled(cron = "*/30 * * * * *")
public void createTable() {
int dayOfWeek = LocalDateTime.now().getDayOfWeek().getValue();
// 每周一生成新的表
if (dayOfWeek == 1) {
// 已经使用的表名
String table = "business_fy_log";
List<String> tableNameList = businessFyLogService.getTableName(table);
String nowTableName = tableNameList.get(0);
String sub = StrUtil.sub(nowTableName, nowTableName.lastIndexOf("_") + 1, nowTableName.length());
// 如果最新一张表是数字结尾,就加一,不然就生成数字结尾的表
if (StrUtil.isNumeric(sub)) {
Long num = NumberUtil.parseLong(sub) + 1;
table = table + "_" + num;
} else {
table = table + "_1";
}
businessFyLogService.createTable(table);
try {
TimeUnit.SECONDS.sleep(2);
} catch (InterruptedException e) {
throw new RuntimeException(e);
}
StringUtils.lastTableName = table;
StringUtils.tableNameList.addAll(tableNameList);
StringUtils.tableNameList.add(table);
}
}
}
@SpringBootApplication
@MapperScan("com.fy.log.mapper")
@EnableScheduling
public class FyLogApiApplication {
public static void main(String[] args) {
SpringApplication.run(FyLogApiApplication.class, args);
}
}
public class BusinessFyLog extends BaseEntity {
private Long id;
private String type;
private String businessInfo;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getBusinessInfo() {
return businessInfo;
}
public void setBusinessInfo(String businessInfo) {
this.businessInfo = businessInfo;
}
}
@Data
public class BaseEntity implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 搜索值
*/
@JsonIgnore
private String searchValue;
/**
* 创建者
*/
private String createBy;
/**
* 创建时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
/**
* 更新者
*/
private String updateBy;
/**
* 更新时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updateTime;
/**
* 备注
*/
private String remark;
/**
* 请求参数
*/
@JsonInclude(JsonInclude.Include.NON_EMPTY)
private Map<String, Object> params = new HashMap<>();
}