美文网首页
对于日志等数据库分表处理

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

作者: A_一只小菜鸟 | 来源:发表于2020-07-02 10:51 被阅读0次

    步骤:

    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();
        }
    
    }
    
    

    相关文章

      网友评论

          本文标题:对于日志等数据库分表处理

          本文链接:https://www.haomeiwen.com/subject/eedhqktx.html