日志管理系统之保存日志到数据库

保存Web访问日志到数据库

Web项目可以通过log4j logback等技术实现保存访问日志到本地文件中，但是会在一些特殊的需求中会让我们保存用户访问日志到数据库中，此时我们可以通过拦截器来实现访问日志的保存。

目录

日志系统列表　：

item	desc
sys_event	数据库系统事件表
SysEventEntity	系统事件表实体类
SysEventMapper	Dao层接口
SysEventService	系统事件Service
EventInterceptor	日志拦截器

1.sys_event建表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for sys_event
-- ----------------------------
DROP TABLE IF EXISTS `sys_event`;
CREATE TABLE `sys_event` (
  `id_` int(20) NOT NULL AUTO_INCREMENT,
  `title_` varchar(500) DEFAULT NULL,
  `request_uri` text,
  `parammeters_` varchar(500) DEFAULT NULL,
  `method_` varchar(200) DEFAULT NULL,
  `client_host` varchar(500) DEFAULT NULL,
  `user_agent` varchar(300) DEFAULT NULL,
  `status_` int(3) DEFAULT NULL,
  `enable_` tinyint(1) DEFAULT NULL,
  `remark_` text,
  `create_by` varchar(200) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_by` varchar(200) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id_`)
) ENGINE=InnoDB AUTO_INCREMENT=343232 DEFAULT CHARSET=utf8;

2.实体类SysEventEntity属性：

    @Id
    @Column(name = "id_")
    private Integer id;

    @Column(name = "title_")
    private String title;

    @Column(name = "request_uri")
    private String requestUri;

    @Column(name = "parammeters_")
    private String parammeters;

    @Column(name = "method_")
    private String method;

    @Column(name = "client_host")
    private String clientHost;

    @Column(name = "user_agent")
    private String userAgent;

    @Column(name = "status_")
    private Integer status;

    @Column(name = "enable_")
    private Boolean enable;

    @Column(name = "create_by")
    private String createBy;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_by")
    private String updateBy;

    @Column(name = "update_time")
    private Date updateTime;

    @Column(name = "remark_")
    private String remark;

3.SysEventService代码

使用线程池开启线程进行插入日志记录

@Service
public class SysEventService extends BaseService<SysEvent, Integer> {
    private ExecutorService executorService = Executors.newCachedThreadPool();
    public void saveEvent(final HttpServletRequest request, final HttpServletResponse response,
            final Exception ex, final Long startTime, final Long endTime) {
        final SysEvent record = new SysEvent();
        record.setMethod(request.getMethod());
        record.setRequestUri(request.getServletPath());
        record.setClientHost(WebUtil.getHost(request));
        record.setUserAgent(request.getHeader("user-agent"));
        record.setParammeters(JSON.toJSONString(request.getParameterMap()));
//      record.setCreateBy(SessionUtils.getLoginUser()!=null?SessionUtils.getLoginUser().getPhone():"匿名用户");
        record.setStatus(response.getStatus());
        final String msg = (String) request.getAttribute("msg");

        executorService.submit(new Runnable() {
            public void run() {
                try { // 保存操作
                    if (StringUtils.isNotBlank(msg)) {
                        record.setRemark(msg);
                    } else {
                        record.setRemark(ExceptionUtil.getStackTraceAsString(ex));
                    }
                    //插入一条记录
                    insertSelective(record);
                    // 内存信息
                    if (logger.isDebugEnabled()) {
                        String message = "开始时间: {}; 结束时间: {}; 耗时: {}s; URI: {}; ";
                        // 最大内存: {}M; 已分配内存: {}M; 已分配内存中的剩余空间: {}M; 最大可用内存: {}M.
                        // long total = Runtime.getRuntime().totalMemory() / 1024 / 1024;
                        // long max = Runtime.getRuntime().maxMemory() / 1024 / 1024;
                        // long free = Runtime.getRuntime().freeMemory() / 1024 / 1024;
                        // , max, total, free, max - total + free
                        logger.debug(message, DateUtil.format(startTime, "HH:mm:ss.SSS"),
                                DateUtil.format(endTime, "HH:mm:ss.SSS"),
                                (endTime - startTime) / 1000.00, record.getRequestUri());
                    }
                } catch (Exception e) {
                    logger.error("Save event log cause error :", e);
                }
            }
        });
    }
}

4.日志拦截器EventInterceptor

public class EventInterceptor extends HandlerInterceptorAdapter {
    @Autowired
    private SysEventService sysEventService;

    private final ThreadLocal<Long> startTimeThreadLocal = new NamedThreadLocal<Long>("ThreadLocal StartTime");

    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        // 开始时间（该数据只有当前请求的线程可见）
        startTimeThreadLocal.set(System.currentTimeMillis());
        return super.preHandle(request, response, handler);
    }

    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
            throws Exception {
        // 保存日志
        sysEventService.saveEvent(request, response, ex, startTimeThreadLocal.get(), System.currentTimeMillis());
        super.afterCompletion(request, response, handler, ex);
    }

}

开始执行时：调用preHandle，此时通过ThreadLocal记录开始时间
方法执行结束后：调用afterCompletion,通过SysEventService添加一条日志记录

5.设置SpringMVC.xml 日志拦截器拦截路径

        <mvc:interceptor>
            <mvc:mapping path="/**"/>
            <!-- 静态资源不拦截 -->
            <mvc:exclude-mapping path="/resources/**"/>
            <bean class="com.qsb.commons.interceptor.EventInterceptor" />
        </mvc:interceptor>

日志流程图: