对于日志等数据库分表处理

2020-07-02  本文已影响0人  A_一只小菜鸟

步骤:

1、在web.xml中添加监听器
2、监听器中定义定时器与定时任务
3、接下来就是在定时器中完成相关操作

具体实现

1、在web.xml中配置监听器

    <listener>
        <description>端口管控日志表监听器</description>
        <listener-class>com.smartsecuri.listener.PortControlLogListener</listener-class>
    </listener>

2、在监听器中定义定时器与定时任务

package com.smartsecuri.listener;

import java.util.Timer;
import java.util.TimerTask;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.smartsecuri.common.MyLogger;
import com.smartsecuri.service.PortControlLogService;

public class PortControlLogListener extends MyLogger
        implements ServletContextListener
{

    // 间隔时间
    private static final long DAY_15 = 15 * 24 * 60 * 60 * 1000;

    @Override
    public void contextInitialized(ServletContextEvent sce)
    {
        WebApplicationContext wac = WebApplicationContextUtils
                .getRequiredWebApplicationContext(sce.getServletContext());
//从spring容器中取到相关实例
        final PortControlLogService portControlLogService = (PortControlLogService) wac
                .getBean("portControlLogService");
        try
        {
            Timer timer = new Timer();
            timer.scheduleAtFixedRate(new TimerTask()
            {

                @Override
                public void run()
                {
                    // 创建防护端口日志表
                    portControlLogService.createNewTable();

                    /* 删除6个月前的表 */
                    portControlLogService.dropTable();
                    logger.info("端口管控日志启动");
                }
            }, 0, DAY_15);

        }
        catch (Exception e)
        {
            logger.error("定时器异常,定时建表失败!", e);
        }
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce)
    {
        // TODO Auto-generated method stub

    }

}

该监听器主要是在工程启动时,从容器中取出对应的service实例,并且创建定时器去定时创建新表与删除旧表,定时器(Timer)中第一个参数是定时任务(TimerTask),第二个参数是第一次的启动时间(以毫秒为单位),第三个参数是之后每次启动的间隔时间。

3、接下来去servive层看具体实现

package com.smartsecuri.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.smartsecuri.common.CommonUtil;
import com.smartsecuri.common.MyLogger;
import com.smartsecuri.common.model.PageModel;
import com.smartsecuri.common.util.DateUtil;
import com.smartsecuri.dao.PortControlLogDao;
import com.smartsecuri.dao.model.PortControlLog;
import com.smartsecuri.service.PortControlLogService;
import com.smartsecuri.service.model.PortControlLogModel;

@Service("portControlLogService")
public class PortControlLogServiceImpl extends MyLogger
        implements PortControlLogService
{
    private final static String TABLE_PORTCONTROLLOG = "portControlLog_";

    @Autowired
    private PortControlLogDao portControlLogDao;

    @Override
    public void createNewTable()
    {
        // 获取现在时间
        Date now = new Date();
        // 获得想要的时间格式
        String nowDateFormat = DateUtil.format(now, "yyyyMM");
        // 获得下个月时间
        Date nextDate = DateUtil.getMonthAfter(now, 1);
        // 获得想要的时间格式
        String nextDateFormat = DateUtil.format(nextDate, "yyyyMM");
        // 第一个表名
        String tableName1 = TABLE_PORTCONTROLLOG + nowDateFormat;
        // 第二个表名
        String tableName2 = TABLE_PORTCONTROLLOG + nextDateFormat;

        portControlLogDao.createNewTable(tableName1);
        portControlLogDao.createNewTable(tableName2);
    }

    @Override
    public void dropTable()
    {
        logger.info("进入删除表");
        //查询相关表
        List<Map<String, Object>> tableNames = portControlLogDao
                .findAllTableNames();
        
        if (tableNames != null && !tableNames.isEmpty())
        {
            // 获取现在时间
            Date now = new Date();
            // 获得前6个月时间
            Date nextDate = DateUtil.getMonthAfter(now, -6);
            // 获得想要的时间格式
            String nextDateFormat = DateUtil.format(nextDate, "yyyyMM");

            // 要删除的表名
            List<String> dropTableList = new ArrayList<String>();

            for (Map<String, Object> map : tableNames)
            {
                String tableName = map.get("table_name").toString();

                String tableDateStr = tableName
                        .substring(TABLE_PORTCONTROLLOG.length());
                if (tableDateStr.equals(nextDateFormat))
                {
                    dropTableList.add(tableName);
                }
            }
            try
            {
                if (dropTableList != null && !dropTableList.isEmpty())
                {
                    for (String tableName : dropTableList)
                    {

                        portControlLogDao.dropTable(tableName);
                    }

                }
            }
            catch (Exception e)
            {
                logger.error("删除表出错,删除失败", e);
            }
        }
    }

    @Override
    public Map<String, Object> pageQuery(PageModel pageModel,
            PortControlLogModel portControlLogModel, String dateBefore,
            String dateAfter)
    {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        List<PortControlLogModel> queryData = findByCondition(pageModel,
                portControlLogModel, dateBefore, dateAfter);
        Integer count = findCountByCondition(portControlLogModel, dateBefore,
                dateAfter);

        resultMap.put("data", queryData);
        resultMap.put("totalCount", count);

        return resultMap;
    }

    /**
     * 
     * findByCondition:(分页查询数据). <br/>
     * TODO(分页查询数据).<br/>
     *
     * @author ZangFS
     * @param pageModel
     * @param portControlLogModel
     * @return
     * @since JDK 1.7
     */
    private List<PortControlLogModel> findByCondition(PageModel pageModel,
            PortControlLogModel portControlLogModel, String dateBefore,
            String dateAfter)
    {
        List<PortControlLogModel> list = new ArrayList<PortControlLogModel>();

        Map<String, Object> condition = new HashMap<String, Object>();

        if (pageModel != null)
        {
            Integer pageSize = pageModel.getLimit();
            Integer pageNum = pageModel.getPage();
            Integer start = pageModel.getStartPage();
            condition.put("pageSize", pageSize);
            condition.put("pageNum", pageNum);
            condition.put("start", start);
        }

        if (portControlLogModel != null)
        {
            String srcIp = portControlLogModel.getSrcIp();
            Integer srcPort = portControlLogModel.getSrcPort();
            condition.put("srcIp", srcIp);
            condition.put("srcPort", srcPort);
            condition.put("dateBefore", dateBefore);
            condition.put("dateAfter", dateAfter);
        }

        Date now =new Date();
        String nowDateFormat=DateUtil.format(now, "yyyyMM");
        String tableName = TABLE_PORTCONTROLLOG + nowDateFormat;
        condition.put("tableName", tableName);
        
        List<PortControlLog> pageQuery = portControlLogDao.pageQuery(condition);
        if (pageQuery != null && !pageQuery.isEmpty())
        {
            list = CommonUtil.copyList(pageQuery, PortControlLogModel.class);
        }

        return list;
    }

    /**
     * 
     * findCountByCondition:(分页查询数量). <br/>
     * TODO(分页查询数量).<br/>
     *
     * @author ZangFS
     * @param portControlLogModel
     * @return
     * @since JDK 1.7
     */
    private Integer findCountByCondition(
            PortControlLogModel portControlLogModel, String dateBefore,
            String dateAfter)
    {
        Map<String, Object> condition = new HashMap<String, Object>();
        if (portControlLogModel != null)
        {
            String srcIp = portControlLogModel.getSrcIp();
            Integer srcPort = portControlLogModel.getSrcPort();
            condition.put("srcIp", srcIp);
            condition.put("srcPort", srcPort);
            condition.put("dateBefore", dateBefore);
            condition.put("dateAfter", dateAfter);
        }

        Date now = new Date();
        String nowDateFormat = DateUtil.format(now, "yyyyMM");
        String tableName = TABLE_PORTCONTROLLOG + nowDateFormat;
        condition.put("tableName", tableName);

        Integer size = portControlLogDao.querySize(condition);
        return size;
    }

}

查询时就需要根据当前时间拼接表名去查询。

接下来看dao层的接口

package com.smartsecuri.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.smartsecuri.dao.model.PortControlLog;

public interface PortControlLogDao
{
    /**
     * 
     * createNewTable:(创建新表). <br/>
     * TODO(创建新表).<br/>
     *
     * @author ZangFS
     * @param tableName
     * @since JDK 1.7
     */
    void createNewTable(@Param("tableName") String tableName);

    /**
     * 
     * findAllTableNames:(查询所有相关表名). <br/>
     * TODO(查询所有相关表名).<br/>
     *
     * @author ZangFS
     * @return
     * @since JDK 1.7
     */
    List<Map<String, Object>> findAllTableNames();

    /**
     * 
     * dropTable:(删除表). <br/>
     * TODO(删除表).<br/>
     *
     * @author ZangFS
     * @param tableName
     * @since JDK 1.7
     */
    void dropTable(@Param("tableName") String tableName);


    /**
     * 
     * pageQuery:(分页查询). <br/>
     * TODO(分页查询).<br/>
     *
     * @author ZangFS
     * @param condition
     * @return
     * @since JDK 1.7
     */
    List<PortControlLog> pageQuery(Map<String, Object> condition);

    /**
     * 
     * querySize:(分页查询数量). <br/>
     * TODO(分页查询数量).<br/>
     *
     * @author ZangFS
     * @param condition
     * @return
     * @since JDK 1.7
     */
    Integer querySize(Map<String, Object> condition);
}

mapper层

<?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.smartsecuri.dao.PortControlLogDao">

    <update id="createNewTable" parameterType="String">
        create table
        IF NOT EXISTS 
        ${tableName} LIKE portControlLog
    </update>

    <select id="findAllTableNames" resultType="java.util.Map">
        SELECT 
        table_name 
        FROM information_schema.tables 
        WHERE 
        table_schema='bnsg' AND table_name LIKE 'portControlLog_%'
    </select>
    
    <update id="dropTable" parameterType="String">
        DROP TABLE IF EXISTS 
        ${tableName}
    </update>

    <select id="pageQuery" parameterType="java.util.Map" resultType="com.smartsecuri.dao.model.PortControlLog">
    select id,
    <include refid="Base_Column_Name"></include>
    from ${tableName}
    <where>
        <if test="srcIp!=null and srcIp!=''">
            and srcIp like CONCAT('%',#{srcIp},'%')
        </if>
        <if test="srcPort!=null">
            and srcPort like CONCAT('%',#{srcPort},'%')
        </if>
        <if test="dateBefore != null and dateBefore != ''">
            and optTime <![CDATA[ >= ]]>#{dateBefore}<![CDATA[]]>
        </if>
        <if test="dateAfter != null and dateAfter != ''">
            and optTime <![CDATA[ <= ]]>#{dateAfter}<![CDATA[]]>
        </if>
    </where>
            ORDER BY OPTTIME DESC
        LIMIT #{start}, #{pageSize}
    </select>
    
    <select id="querySize" parameterType="java.util.Map" resultType="java.lang.Integer">
        select count(*)
        from ${tableName}
        <where>
        <if test="srcIp!=null and srcIp!=''">
            and srcIp like CONCAT('%',#{srcIp},'%')
        </if>
        <if test="srcPort!=null">
            and srcPort like CONCAT('%',#{srcPort},'%')
        </if>
        <if test="dateBefore != null and dateBefore != ''">
            and optTime <![CDATA[ >= ]]>#{dateBefore}<![CDATA[]]>
        </if>
        <if test="dateAfter != null and dateAfter != ''">
            and optTime <![CDATA[ <= ]]>#{dateAfter}<![CDATA[]]>
        </if>
    </where>
    </select>

</mapper>

注意:
table_schema中的是数据库名。
${tableName} LIKE portControlLog 中的LIKE是参照的意思,参照portControlLog表去创建。
${tableName}必须使用$符号。

{}是预编译处理,$ {}是字符串替换。

所用到的DateUtil.java工具类


package com.smartsecuri.common.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * ClassName: DateUtil <br/>
 * Detail: 日期工具类 <br/>
 * date: 2019年6月9日 下午1:16:39 <br/>
 *
 * @author liuzibing
 * @version
 * @since JDK 1.7
 */
public class DateUtil
{
    private static final Logger logger = LogManager.getLogger(DateUtil.class);

    /**
     * sdf: (yyyy-MM-dd HH:mm:ss).
     */
    private static final String COMMON_DATE = "yyyy-MM-dd HH:mm:ss";

    /**
     * dateToStamp:将日期转化为时间戳. <br/>
     *
     * @author liuzibing
     * @param time 格式为yyyy-MM-dd HH:mm:ss的字符串日期
     * @return
     * @since JDK 1.7
     */
    public static long dateToStamp(String time)
    {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long ts = 0;
        try
        {
            Date date = simpleDateFormat.parse(time);
            ts = date.getTime();// 获取时间的时间戳
        }
        catch (ParseException e)
        {
            logger.error("dateToStamp error :", e);
        }
        return ts;
    }

    /**
     * stamp2DateString:将时间戳转化为yyyy-MM-dd HH:mm:ss格式的日期字符串. <br/>
     *
     * @author liuzibing
     * @param stamp 时间戳
     * @return
     * @since JDK 1.7
     */
    public static String stamp2DateString(long stamp)
    {
        String time;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date(stamp);
        time = simpleDateFormat.format(date);
        return time;
    }

    /**
     * stamp2Date: 将时间戳转化为日期. <br/>
     *
     * @author liuzibing
     * @param stamp 时间戳
     * @return
     * @since JDK 1.7
     */
    public static Date stamp2Date(long stamp)
    {
        Date date = new Date(stamp);
        return date;
    }

    /**
     * format:(日期格式化). <br/>
     * (格式:yyyy-MM-dd HH:mm:ss).<br/>
     *
     * @author zhangheng
     * @param date 日期
     * @return 格式化的日期字符串
     * @since JDK 1.7
     */
    public static String format(Date date)
    {
        SimpleDateFormat sdf = new SimpleDateFormat(COMMON_DATE);
        return sdf.format(date);
    }

    /**
     * format:(日期格式化). <br/>
     * (格式:自定义pattern).<br/>
     *
     * @author zhangheng
     * @param date 日期
     * @param pattern 格式
     * @return 格式化的日期字符串
     * @since JDK 1.7
     */
    public static String format(Date date, String pattern)
    {
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
        return sdf.format(date);
    }

    /**
     * 
     * formatDateForHalfHour: 将时间格式化为当前小时的0分整 <br/>
     * 格式化时间为yyyy-MM-dd HH:00:00格式 <br/>
     *
     * @author Qzx
     * @param date Date日期
     * @return yyyy-MM-dd HH:00:00格式字符串
     * @since JDK 1.7
     */
    public static String formatDateOnTheHour(Date date)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:00:00");

        String formatTime = sdf.format(date);

        return formatTime;
    }

    /**
     * 
     * DateToDateString: 将时间格式化为字符串形式 <br/>
     * 格式化时间为yyyy-MM-dd HH:mm:ss格式 <br/>
     *
     * @author Qzx
     * @param date Date日期
     * @return yyyy-MM-dd HH:mm:ss格式字符串
     * @since JDK 1.7
     */
    public static String DateToDateString(Date date)
    {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        String formatTime = sdf.format(date);

        return formatTime;
    }

    /**
     * 
     * dateStringToDate: 将字符串时间转化为日期 <br/>
     * 将yyyy-MM-dd HH:mm:ss格式化为日期 <br/>
     *
     * @author Qzx
     * @param dateString 字符串时间
     * @return Date日期
     * @since JDK 1.7
     */
    public static Date dateStringToDate(String dateString)
    {
        Date date = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try
        {
            date = simpleDateFormat.parse(dateString);
        }
        catch (ParseException e)
        {
            logger.error("dateStringToDate error :", e);
        }
        return date;
    }

    /**
     * 获取指定日期的(上)下几个月
     * 
     * @param date
     * @param after
     * @return
     */
    public static Date getMonthAfter(Date date, int after)
    {
        Calendar caleandar = Calendar.getInstance();
        caleandar.setTime(date);
        caleandar.add(Calendar.MONTH, after);
        return caleandar.getTime();
    }

}

上一篇下一篇

猜你喜欢

热点阅读