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

技术2022-07-11 74

之前基于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">  <property name="log.file.size" value="100MB"></property> <property name="log.history.days" value="7"></property>  <springProperty scope="context" name="log.path" source="project.log.path" defaultValue="./logs"/> <springProperty scope="context" name="log.level" source="project.log.level" />  <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" />   <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <Pattern>${consoleLayoutPattern}</Pattern> <charset>UTF-8</charset> </encoder> </appender>  <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>  <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>  <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>  <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>  <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>  <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 是否提供对应的方言DB2untesteduntestedunknownNOH21.2.132-unknownYESHSQL1.8.0.7-NOYESMicrosoft SQL Server20052.0.1008.2 (sqljdbc.jar)YESYESMySQL5.0.225.0.8 (mysql-connector.jar)YESYESPostgreSQL8.x8.4-701.jdbc4NOYESOracle10g10.2.0.1 (ojdbc14.jar)YESYESSQLLite3.7.4-unknownYESSybase SQLAnywhere10.0.1-unknownYES

logback日志输出到数据库参考中文网站：http://www.logback.cn/04第四章Appenders.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中，解决了该问题。如果有大神有其他方案还请指教。

Processed: 0.013, SQL: 9