SpringBoot中Logback常用配置以及自定义输出到MySql数据库

之前基于SpringBoot开发的项目运行一段时间后，客户使用网站偶尔会出现接口调用失败的情况，每次产品经理询问是怎么回事的时候，都需要让运维提下最近的日志才能分析具体原因，这样时效性和便利性不能满足当前的要求。项目之前的日志框架使用的是Logback，日志直接存入服务器上的文件中，现在计划将日志存入MySql数据库中，然后开发系统日志查询功能，这样可以通过项目直接分析日志，不用每次需要分析日志都需要麻烦运维人员。

SpringBoot是默认支持logback日志框架的，所以SpringBoot项目是不需要额外引入其他依赖的，如果要启用数据库日志功能，需要引入数据库驱动依赖，pom.xml示例如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
    </parent>

    <groupId>com.flyduck</groupId>
    <artifactId>logback</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>logback</name>
    <description>logback默认数据库配置以及自定义MySql日志表Demo</description>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

</project>

 

Logback输出日志到控制台和文件

项目之前是将Logback的日志输出到文件中，logback-spring.xml配置如下:

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="30 seconds" debug="false">

    <!-- KV property-->
    <property name="log.file.size" value="100MB"></property>
    <property name="log.history.days" value="7"></property>

    <!--use spring config kv. -->
    <springProperty scope="context" name="log.path" source="project.log.path" defaultValue="./logs"/>
    <springProperty scope="context" name="log.level" source="project.log.level" />


    <!-- output format -->
    <property name="consoleLayoutPattern"
              value="%-20(%d{yyyyMMdd_HH:mm:ss.SSS} [%logger{10}][%F:%L])[%level] %msg%n" />
    <property name="fileLayoutPattern"
              value="%-20(%d{yyyyMMdd_HH:mm:ss.SSS} [%logger{10}]) [%level] %msg%n" />
    <!--<contextName>${project.name}</contextName>-->

    <!--output to console -->
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <Pattern>${consoleLayoutPattern}</Pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <!--output to debug file -->
    <appender name="DEBUG_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/log_debug.log</file>
        <encoder>
            <pattern>${fileLayoutPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <!-- rolling policy -->
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/debug/log-debug-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>${log.file.size}</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>${log.history.days}</maxHistory>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>debug</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!-- output info file -->
    <appender name="INFO_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/log_info.log</file>
        <encoder>
            <pattern>${fileLayoutPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <!-- 日志记录器的滚动策略，按日期，按大小记录 -->
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/info/log-info-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>${log.file.size}</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>${log.history.days}</maxHistory>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>info</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!-- output warn file -->
    <appender name="WARN_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/log_warn.log</file>
        <encoder>
            <pattern>${fileLayoutPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <!-- 日志记录器的滚动策略，按日期，按大小记录 -->
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/warn/log-warn-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>${log.file.size}</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>${log.history.days}</maxHistory>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>warn</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!-- output error file -->
    <appender name="ERROR_FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/log_error.log</file>
        <encoder>
            <pattern>${fileLayoutPattern}</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/error/log-error-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <maxFileSize>${log.file.size}</maxFileSize>
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>${log.history.days}</maxHistory>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>ERROR</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>


    <!-- non assign profile model  -->
    <root level="${log.level}">
        <appender-ref ref="CONSOLE" />
        <appender-ref ref="DEBUG_FILE" />
        <appender-ref ref="INFO_FILE" />
        <appender-ref ref="WARN_FILE" />
        <appender-ref ref="ERROR_FILE" />
    </root>

</configuration>

logback配置详情可以参考中文网站：http://www.logback.cn 或者英文网站：http://logback.qos.ch/manual/index.html，网站中对于logback的配置都做了详细的介绍。

 

Logback输出日志到数据库

根据项目目前的需求，需要将日志输出到数据库。Logback提供了将日志输出到数据库的功能，在logback-spring.xml做简单的配置即可实现。

1.首先在数据库中新建内置的三张数据库表：logging_event, logging_event_property 与 logging_event_exception。这些创建数据库脚本在 logback-classic/src/main/java/ch/qos/logback/classic/db/script 文件夹下。

mysql的数据库脚本：

# Logback: the reliable, generic, fast and flexible logging framework.
# Copyright (C) 1999-2010, QOS.ch. All rights reserved.
#
# See http://logback.qos.ch/license.html for the applicable licensing 
# conditions.

# This SQL script creates the required tables by ch.qos.logback.classic.db.DBAppender.
#
# It is intended for MySQL databases. It has been tested on MySQL 5.1.37 
# on Linux


BEGIN;
DROP TABLE IF EXISTS logging_event_property;
DROP TABLE IF EXISTS logging_event_exception;
DROP TABLE IF EXISTS logging_event;
COMMIT;


BEGIN;
CREATE TABLE logging_event 
  (
    timestmp         BIGINT NOT NULL,
    formatted_message  TEXT NOT NULL,
    logger_name       VARCHAR(254) NOT NULL,
    level_string      VARCHAR(254) NOT NULL,
    thread_name       VARCHAR(254),
    reference_flag    SMALLINT,
    arg0              VARCHAR(254),
    arg1              VARCHAR(254),
    arg2              VARCHAR(254),
    arg3              VARCHAR(254),
    caller_filename   VARCHAR(254) NOT NULL,
    caller_class      VARCHAR(254) NOT NULL,
    caller_method     VARCHAR(254) NOT NULL,
    caller_line       CHAR(4) NOT NULL,
    event_id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
  );
COMMIT;

BEGIN;
CREATE TABLE logging_event_property
  (
    event_id	      BIGINT NOT NULL,
    mapped_key        VARCHAR(254) NOT NULL,
    mapped_value      TEXT,
    PRIMARY KEY(event_id, mapped_key),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
  );
COMMIT;

BEGIN;
CREATE TABLE logging_event_exception
  (
    event_id         BIGINT NOT NULL,
    i                SMALLINT NOT NULL,
    trace_line       VARCHAR(254) NOT NULL,
    PRIMARY KEY(event_id, i),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
  );
COMMIT;

2. 在logback-spring.xml中配置DBAppender，设置数据库连接。

    <appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
        <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
                <driverClass>com.mysql.cj.jdbc.Driver</driverClass>
                <url>jdbc:mysql://localhost:3306/log?serverTimezone=Asia/Shanghai</url>
                <user>root</user>
                <password>123456</password>
        </connectionSource>
    </appender>

    <root level="${log.level}">
        <appender-ref ref="DB" />
    </root>

完成上面两个步骤即可将日志输出到数据库中，MySql数据库logging_event表示例：

logback内置的支持的数据库如下： 

RDBMS	测试版本	JDBC 驱动的测试版本	是否支持 getGeneratedKeys()	logback 是否提供对应的方言
DB2	untested	untested	unknown	NO
H2	1.2.132	-	unknown	YES
HSQL	1.8.0.7	-	NO	YES
Microsoft SQL Server	2005	2.0.1008.2 (sqljdbc.jar)	YES	YES
MySQL	5.0.22	5.0.8 (mysql-connector.jar)	YES	YES
PostgreSQL	8.x	8.4-701.jdbc4	NO	YES
Oracle	10g	10.2.0.1 (ojdbc14.jar)	YES	YES
SQLLite	3.7.4	-	unknown	YES
Sybase SQLAnywhere	10.0.1	-	unknown	YES

logback日志输出到数据库参考中文网站：http://www.logback.cn/04%E7%AC%AC%E5%9B%9B%E7%AB%A0Appenders.html，英文网站：http://logback.qos.ch/manual/appenders.html。 

 

Logback输出日志到自定义数据库

logback内置提供的数据库表一共有三张，通过日志记录我们发现我们只需要一部分日志信息就足够了，这时我们可以通过自定义appender将日志输出到我们自定义的数据库表中。

1.自定义数据库表，logback内置提供了三张表，我们自定义将日志输出到一张表即可，自定义表脚本如下：

BEGIN;
DROP TABLE IF EXISTS `system_log`;
COMMIT;

BEGIN;
CREATE TABLE `system_log` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `code` varchar(200) NOT NULL DEFAULT '' COMMENT '编码',
    `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
    `is_delete` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除',
    `message` text NOT NULL COMMENT '详情',
    `level_string` varchar(254) NOT NULL COMMENT '等级',
    `logger_name` varchar(254) NOT NULL COMMENT '名称',
    `thread_name` varchar(254) COMMENT '线程名',
    `reference_flag` smallint COMMENT '参考标志',
    `caller_filename` varchar(254) NOT NULL COMMENT '文件名',
    `caller_class` varchar(254) NOT NULL COMMENT '类',
    `caller_method` varchar(254) NOT NULL COMMENT '方法',
    `caller_line` char(4) NOT NULL COMMENT '行数',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT '系统日志';
COMMIT;

2. 自定义LoggerDBAppender类，该类主要将日志输出到我们自定义的表中，源码如下：

package com.log.config;

import ch.qos.logback.classic.db.DBHelper;
import ch.qos.logback.classic.spi.CallerData;
import ch.qos.logback.classic.spi.ILoggingEvent;
import ch.qos.logback.core.db.DBAppenderBase;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;

/**
 * 日志类
 *
 * @author Author 2020/7/1
 * @version V1.0
 * @modificationHistory=========================逻辑或功能性重大变更记录
 * @modify by user: Author 2020/7/1
 **/
public class LoggerDBAppender extends DBAppenderBase<ILoggingEvent> {

    private static final int CREATE_TIME_INDEX = 1;
    private static final int UPDATE_TIME_INDEX = 2;
    private static final int MESSAGE_INDEX = 3;
    private static final int LEVEL_STRING_INDEX = 4;
    private static final int LOGGER_NAME_INDEX = 5;
    private static final int THREAD_NAME_INDEX = 6;
    private static final int REFERENCE_FLAG_INDEX = 7;
    private static final int CALLER_FILENAME_INDEX = 8;
    private static final int CALLER_CLASS_INDEX = 9;
    private static final int CALLER_METHOD_INDEX = 10;
    private static final int CALLER_LINE_INDEX = 11;

    protected String insertSQL;
    protected static final Method GET_GENERATED_KEYS_METHOD;
    protected static final StackTraceElement EMPTY_CALLER_DATA = CallerData.naInstance();

    private static String buildInsertSQL() {
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO system_log ");
        sqlBuilder.append("(create_time, update_time, message, level_string, logger_name, thread_name, reference_flag, caller_filename, caller_class, caller_method, caller_line) ");
        sqlBuilder.append("VALUES (?, ?, ? ,?, ?, ?, ?, ?, ?, ?, ?)");
        return sqlBuilder.toString();
    }

    @Override
    public void start() {
        this.insertSQL = buildInsertSQL();
        super.start();
    }

    @Override
    protected Method getGeneratedKeysMethod() {
        return GET_GENERATED_KEYS_METHOD;
    }

    @Override
    protected String getInsertSQL() {
        return this.insertSQL;
    }

    @Override
    protected void subAppend(ILoggingEvent iLoggingEvent, Connection connection, PreparedStatement preparedStatement) throws Throwable {
        this.bindLoggingEventWithInsertStatement(preparedStatement, iLoggingEvent);
        this.bindCallerDataWithPreparedStatement(preparedStatement, iLoggingEvent.getCallerData());
        int updateCount = preparedStatement.executeUpdate();
        if (updateCount != 1) {
            this.addWarn("Failed to insert loggingEvent");
        }
    }

    private void bindCallerDataWithPreparedStatement(PreparedStatement preparedStatement, StackTraceElement[] callerDataArray) throws SQLException {
        StackTraceElement caller = this.extractFirstCaller(callerDataArray);
        preparedStatement.setString(CALLER_FILENAME_INDEX, caller.getFileName());
        preparedStatement.setString(CALLER_CLASS_INDEX, caller.getClassName());
        preparedStatement.setString(CALLER_METHOD_INDEX, caller.getMethodName());
        preparedStatement.setString(CALLER_LINE_INDEX, Integer.toString(caller.getLineNumber()));
    }

    private StackTraceElement extractFirstCaller(StackTraceElement[] callerDataArray) {
        StackTraceElement caller = EMPTY_CALLER_DATA;
        if (this.hasAtLeastOneNonNullElement(callerDataArray)) {
            caller = callerDataArray[0];
        }

        return caller;
    }

    private boolean hasAtLeastOneNonNullElement(StackTraceElement[] callerDataArray) {
        return callerDataArray != null && callerDataArray.length > 0 && callerDataArray[0] != null;
    }

    private void bindLoggingEventWithInsertStatement(PreparedStatement preparedStatement, ILoggingEvent iLoggingEvent) throws SQLException {
        Date date = new Date(iLoggingEvent.getTimeStamp());
        preparedStatement.setDate(CREATE_TIME_INDEX, date);
        preparedStatement.setDate(UPDATE_TIME_INDEX, date);

        preparedStatement.setString(MESSAGE_INDEX, iLoggingEvent.getFormattedMessage());
        preparedStatement.setString(LEVEL_STRING_INDEX, iLoggingEvent.getLevel().toString());
        preparedStatement.setString(LOGGER_NAME_INDEX, iLoggingEvent.getLoggerName());
        preparedStatement.setString(THREAD_NAME_INDEX, iLoggingEvent.getThreadName());
        preparedStatement.setShort(REFERENCE_FLAG_INDEX, DBHelper.computeReferenceMask(iLoggingEvent));
    }

    @Override
    protected void secondarySubAppend(ILoggingEvent iLoggingEvent, Connection connection, long l) throws Throwable {

    }

    static {
        Method getGeneratedKeysMethod;
        try {
            getGeneratedKeysMethod = PreparedStatement.class.getMethod("getGeneratedKeys", (Class[])null);
        } catch (Exception var2) {
            getGeneratedKeysMethod = null;
        }

        GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;
    }
}

3. 在logback-spring.xml中配置自定义的LoggerDBAppender，设置数据库连接。

    <appender name="DB" class="com.log.config.LoggerDBAppender">
        <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
                <driverClass>com.mysql.cj.jdbc.Driver</driverClass>
                <url>jdbc:mysql://localhost:3306/log?serverTimezone=Asia/Shanghai</url>
                <user>root</user>
                <password>123456</password>
        </connectionSource>
    </appender>

    <root level="${log.level}">
        <appender-ref ref="DB" />
    </root>

运行项目，日志成功的输出到自定义数据库表中。至此logback日志输出到自定义数据表已经完成，随后开发想要的查询接口完成项目系统日志的查询功能。自定义数据库表参考博客：https://blog.csdn.net/qq_20914913/article/details/92830914。

对于SpringBoot中logback的配置以及数据库日志的启用配置的示例源码地址：https://gitee.com/flyduck128/springboot-demo/tree/master/flyduck-logback

 

遇到的问题

1.根据文档描述，在配置logback日志输出到数据库中时，使用连接池性能会有很大的提高，博主使用SpringBoot自带的连接池HikariDataSource，无法正常运行项目，暂时还未找到原因，所以目前暂未使用连接池，如果有大神指导还请指教。

2.项目是将配置文件和第三方包打包到jar外面的，自定义LoggerDBAppender类在开发环境IDEA中正常运行，但是发布到测试环境就无法运行提示“Could not create an Appender of type [com.log.config.LoggerDBAppender].”，通过查看源码和分析，推测应该是类加载的问题，我们自定义的LoggerDBAppender在项目的包中，由AppClassLoader系统类加载。而第三方的logback-core包是通过-Djava.ext.dirs设置由ExtensionClassLoader扩展类加载。所以导致logback-core中无法加载自定义的LoggerDBAppender类。目前的方案是将第三方包按照SpringBoot的方式打包到jar中，解决了该问题。如果有大神有其他方案还请指教。