java操作数据库日志表,对于日志等数据库分表处理

步骤：

1、在web.xml中添加监听器

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

3、接下来就是在定时器中完成相关操作

具体实现

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

端口管控日志表监听器

com.smartsecuri.listener.PortControlLogListener

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> 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 dropTableList = new ArrayList();

for (Map 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 pageQuery(PageModel pageModel,

PortControlLogModel portControlLogModel, String dateBefore,

String dateAfter)

{

Map resultMap = new HashMap();

List queryData = findByCondition(pageModel,

portControlLogModel, dateBefore, dateAfter);

Integer count = findCountByCondition(portControlLogModel, dateBefore,

dateAfter);

resultMap.put("data", queryData);

resultMap.put("totalCount", count);

return resultMap;

}

/**

*

* findByCondition:(分页查询数据).

* TODO(分页查询数据).

*

* @author ZangFS

* @param pageModel

* @param portControlLogModel

* @return

* @since JDK 1.7

*/

private List findByCondition(PageModel pageModel,

PortControlLogModel portControlLogModel, String dateBefore,

String dateAfter)

{

List list = new ArrayList();

Map condition = new HashMap();

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 pageQuery = portControlLogDao.pageQuery(condition);

if (pageQuery != null && !pageQuery.isEmpty())

{

list = CommonUtil.copyList(pageQuery, PortControlLogModel.class);

}

return list;

}

/**

*

* findCountByCondition:(分页查询数量).

* TODO(分页查询数量).

*

* @author ZangFS

* @param portControlLogModel

* @return

* @since JDK 1.7

*/

private Integer findCountByCondition(

PortControlLogModel portControlLogModel, String dateBefore,

String dateAfter)

{

Map condition = new HashMap();

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:(创建新表).

* TODO(创建新表).

*

* @author ZangFS

* @param tableName

* @since JDK 1.7

*/

void createNewTable(@Param("tableName") String tableName);

/**

*

* findAllTableNames:(查询所有相关表名).

* TODO(查询所有相关表名).

*

* @author ZangFS

* @return

* @since JDK 1.7

*/

List> findAllTableNames();

/**

*

* dropTable:(删除表).

* TODO(删除表).

*

* @author ZangFS

* @param tableName

* @since JDK 1.7

*/

void dropTable(@Param("tableName") String tableName);

/**

*

* pageQuery:(分页查询).

* TODO(分页查询).

*

* @author ZangFS

* @param condition

* @return

* @since JDK 1.7

*/

List pageQuery(Map condition);

/**

*

* querySize:(分页查询数量).

* TODO(分页查询数量).

*

* @author ZangFS

* @param condition

* @return

* @since JDK 1.7

*/

Integer querySize(Map condition);

}

mapper层

create table

IF NOT EXISTS

${tableName} LIKE portControlLog

SELECT

table_name

FROM information_schema.tables

WHERE

table_schema='bnsg' AND table_name LIKE 'portControlLog_%'

DROP TABLE IF EXISTS

${tableName}

select id,

from ${tableName}

and srcIp like CONCAT('%',#{srcIp},'%')

and srcPort like CONCAT('%',#{srcPort},'%')

and optTime = ]]>#{dateBefore}

and optTime #{dateAfter}

ORDER BY OPTTIME DESC

LIMIT #{start}, #{pageSize}

select count(*)

from ${tableName}

and srcIp like CONCAT('%',#{srcIp},'%')

and srcPort like CONCAT('%',#{srcPort},'%')

and optTime = ]]>#{dateBefore}

and optTime #{dateAfter}

注意：

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

* Detail: 日期工具类

* date: 2019年6月9日 下午1:16:39

*

* @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:将日期转化为时间戳.

*

* @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格式的日期字符串.

*

* @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: 将时间戳转化为日期.

*

* @author liuzibing

* @param stamp 时间戳

* @return

* @since JDK 1.7

*/

public static Date stamp2Date(long stamp)

{

Date date = new Date(stamp);

return date;

}

/**

* format:(日期格式化).

* (格式：yyyy-MM-dd HH:mm:ss).

*

* @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:(日期格式化).

* (格式：自定义pattern).

*

* @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分整

* 格式化时间为yyyy-MM-dd HH:00:00格式

*

* @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: 将时间格式化为字符串形式

* 格式化时间为yyyy-MM-dd HH:mm:ss格式

*

* @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: 将字符串时间转化为日期

* 将yyyy-MM-dd HH:mm:ss格式化为日期

*

* @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();

}

}