操作日志保留6个月,其余存年度表

whycxzp

2023-02-16 e7c8018af7c38165f58db57bce13ac904bd7aa6c

操作日志保留6个月,其余存年度表

 src/main/java/com/whyc/listener/TaskListener.java

@@ -1,10 +1,13 @@
package com.whyc.listener;

import com.whyc.constant.YamlProperties;
import com.whyc.service.HistoryDataArchivingService;
import com.whyc.task.AccountLockStrategyTask;
import com.whyc.task.AccountScanTask;
import com.whyc.task.HistoryDataArchivingTask;
import com.whyc.task.PreventTryTask;
import org.apache.commons.lang.time.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
@@ -23,6 +26,9 @@
    //public static final Long ONE_MONTH_DEBUG = DateUtils.MILLIS_PER_SECOND*10;

    private Timer timer;

    @Autowired
    private HistoryDataArchivingTask historyDataArchivingTask;

    @Override
    public void contextInitialized(ServletContextEvent servletContextEvent) {
@@ -62,6 +68,7 @@
            //防重放功能随机码清除,频率为2秒
            timer.schedule(new PreventTryTask(servletContextEvent.getServletContext()), 1000, 2000);
        }
        timer.schedule(historyDataArchivingTask,0,1000*60*60*24*30L);
    }

    @Override

 src/main/java/com/whyc/mapper/CommonMapper.java

@@ -1,5 +1,10 @@
package com.whyc.mapper;

import com.whyc.pojo.UserLog;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 通用mapper,用于通用数据的查询
 */
@@ -21,4 +26,8 @@

    void createTable4AlarmVoiceSet();

    void createTable4UserLogByYear(@Param("year") String year);

    void insertBatch4UserLogByYear(@Param("year")String year, List<UserLog> userLogs);

}

 src/main/java/com/whyc/mapper/UserLogMapper.java

@@ -8,4 +8,6 @@

    List<UserLog> getList(UserLog userLog);

    List<UserLog> getDataBeforeRetentionMonth(int retentionMonth);

}

 src/main/java/com/whyc/pojo/UserLog.java

@@ -26,6 +26,7 @@
    @TableField("uOprateMsg")
    private String operationMsg;
    private String operationDetail;
    private Integer readFlag;

    @TableField(exist = false)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
@@ -36,6 +37,8 @@
    private String uName;
    @TableField(exist = false)
    private Integer eventType;
    @TableField(exist = false)
    private String recordYear;

    public String getOperationTypeStr() {
        return operationTypeStr;
@@ -124,4 +127,20 @@
    public void setEventType(Integer eventType) {
        this.eventType = eventType;
    }

    public Integer getReadFlag() {
        return readFlag;
    }

    public void setReadFlag(Integer readFlag) {
        this.readFlag = readFlag;
    }

    public String getRecordYear() {
        return recordYear;
    }

    public void setRecordYear(String recordYear) {
        this.recordYear = recordYear;
    }
}

 src/main/java/com/whyc/service/HistoryDataArchivingService.java

New file
@@ -0,0 +1,51 @@
package com.whyc.service;

import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.UserLog;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

@Service
public class HistoryDataArchivingService {

    @Resource
    private UserLogService userLogService;

    @Resource
    private CommonMapper commonMapper;

    @Transactional
    public void historyDataArchiving(String dbName,String tableName,int retentionMonth){
        //查询指定retentionMonth之前的数据
        List<UserLog> dataList = userLogService.getDataBeforeRetentionMonth(retentionMonth);
        //对这些数据按照年份进行划分
        Map<String, List<UserLog>> userLogMap = dataList.stream().collect(Collectors.groupingBy(UserLog::getRecordYear));
        //每个年份的存一张表
        Set<String> yearKeySet = userLogMap.keySet();
        for (String year : yearKeySet){
            String tableNameExists = commonMapper.existTable("db_user", "tb_user_log_" + year);
            if(tableNameExists == null) {
                commonMapper.createTable4UserLogByYear(year);
            }
            List<UserLog> userLogs = userLogMap.get(year);
            List<UserLog> temp = new LinkedList<>();
            for (int i = 0; i < userLogs.size(); i++) {
                temp.add(userLogs.get(i));
                if(i!=0 && i%1000 == 0){ //1000一比
                    commonMapper.insertBatch4UserLogByYear(year,temp);
                    temp.clear();
                }
            }
            commonMapper.insertBatch4UserLogByYear(year,temp);
        }
        //删除总表中已经另外存档的数据
        userLogService.deleteBeforeRetentionMonth(retentionMonth);
    }
}

 src/main/java/com/whyc/service/UserLogService.java

@@ -17,7 +17,7 @@
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@@ -93,4 +93,21 @@
        userLog.setOperationDetail(msgDetail);
        mapper.insert(userLog);
    }

    public List<UserLog> getDataBeforeRetentionMonth(int retentionMonth) {
        return mapper.getDataBeforeRetentionMonth(retentionMonth);
    }

    public void insertBatch(List<UserLog> userLogs) {
        mapper.insertBatchSomeColumn(userLogs);
    }

    public void deleteBeforeRetentionMonth(int retentionMonth) {
        UpdateWrapper<UserLog> update = Wrappers.update();
        Calendar instance = Calendar.getInstance();
        instance.add(Calendar.MONTH,-6);

        update.lt("uOprateDay",instance.getTime());
        mapper.delete(update);
    }
}

 src/main/java/com/whyc/task/HistoryDataArchivingTask.java

New file
@@ -0,0 +1,31 @@
package com.whyc.task;

import com.whyc.service.HistoryDataArchivingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.TimerTask;

/**
 * 暂定保存六个月的数据,六个月之前的数据存档到年分表中存档
 *
 * 电源告警历史
 * 电池告警历史
 * 设备告警历史
 * 操作事件 db_user.tb_user_log
 */
@Component
public class HistoryDataArchivingTask extends TimerTask {

    @Autowired
    private HistoryDataArchivingService service;

    @Override
    public void run() {
        service.historyDataArchiving("db_user","tb_user_log",6);
    }


}

 src/main/resources/mapper/CommonMapper.xml

@@ -15,6 +15,28 @@
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='告警语音设置'
    </insert>
    <insert id="createTable4UserLogByYear">
        CREATE TABLE `tb_user_log_${year}` (
        `num` bigint(20) NOT NULL AUTO_INCREMENT,
        `uId` int(11) NOT NULL DEFAULT '0',
        `uOprateType` int(11) NOT NULL DEFAULT '0',
        `uOprateDay` datetime NOT NULL DEFAULT '1980-01-01 00:00:00',
        `uTerminalIp` varchar(20) NOT NULL DEFAULT ' ',
        `uOprateMsg` text,
        `operation_detail` text COMMENT '操作的具体数据',
        `read_flag` tinyint(1) DEFAULT '1' COMMENT '是否已阅读,此功能针对异常信息',
        PRIMARY KEY (`num`),
        KEY `user_id` (`uId`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
    </insert>
    <insert id="insertBatch4UserLogByYear">
        insert into `tb_user_log_${year}`(uId,uOprateType,uOprateDay,uTerminalIp,uOprateMsg,operation_detail,read_flag)
        values
        <foreach collection="userLogs" item="item" separator=",">
        (#{item.uId},#{item.operationType},#{item.operationTime},#{item.terminalIp},
         #{item.operationMsg},#{item.operationDetail},#{item.readFlag})
        </foreach>
    </insert>
    <delete id="truncate">
        TRUNCATE ${dbName}.`${tableName}`
    </delete>

 src/main/resources/mapper/UserLogMapper.xml

@@ -11,6 +11,8 @@
        <result property="operationMsg" column="uOprateMsg"/>
        <result property="operationDetail" column="operation_detail"/>
        <result property="uName" column="uName"/>
        <result property="recordYear" column="recordYear"/>
        <result property="readFlag" column="read_flag"/>
    </resultMap>
    <select id="getList" resultMap="Map_UserLog">
        select l.*,u.uName from db_user.tb_user_log l,db_user.tb_user_inf u
@@ -91,4 +93,7 @@
        </if>
        ORDER BY uOprateDay DESC
    </select>
    <select id="getDataBeforeRetentionMonth" resultMap="Map_UserLog">
        select *,DATE_FORMAT(uOprateDay,"%Y")  as recordYear from db_user.tb_user_log where uOprateDay &lt; DATE_ADD(CURDATE(),INTERVAL -6 MONTH);
    </select>
</mapper>