Logback自定义DBAppender保存系统日志到数据库

在系统中采用了spring boot + logback＋slf4j的日志框架，将系统日志记录到数据库。
在这里插入图片描述

1. 添加依赖：

从 logback 版本 1.2.8 开始，DBAppender 不再随 logback-classic 一起提供，所以需要单独引入

<dependency><groupId>ch.qos.logback.db</groupId><artifactId>logback-classic-db</artifactId><version>1.2.11.1</version>
</dependency>

2. 自定义表结构:

可以根据实际情况增加或减少和修改字段

CREATE TABLE t_log_logback (`id` VARCHAR ( 64 ) NOT NULL COMMENT '主键',`create_time` VARCHAR ( 32 ) NOT NULL COMMENT '创建时间',`message` TEXT NOT NULL COMMENT '内容',`level_string` VARCHAR ( 254 ) NOT NULL COMMENT '日志等级：TRACE,DEBUG,INFO,WARNING,ERROR,FATAL',`logger_name` VARCHAR ( 254 ) NOT NULL COMMENT '发出日志记录请求的记录器的名称',`thread_name` VARCHAR ( 254 ) COMMENT '线程名称',`reference_flag` INT ( 11 ) COMMENT 'MDC属性',`caller_filename` VARCHAR ( 254 ) NOT NULL COMMENT '发出日志记录请求的文件名',`caller_class` VARCHAR ( 254 ) NOT NULL COMMENT '发出日志记录请求的类',`caller_method` VARCHAR ( 254 ) NOT NULL COMMENT '发出日志记录请求的方法的名称',`caller_line` VARCHAR ( 4 ) NOT NULL COMMENT '发出日志记录请求的行号',
PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMMENT = 'logback系统日志记录表';

3. 自定义追加器DbLogbackAppender

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.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;/*** <p>* 	自定义DB日志追加器，参考实现类 {@link ch.qos.logback.classic.db.DBAppender}*  参考来源：https://blog.csdn.net/qq_20914913/article/details/92830914* </p>*/
public class DbLogbackAppender extends DBAppenderBase<ILoggingEvent> {private String insertSQL;private static final Method GET_GENERATED_KEYS_METHOD;// 对应于数据库字段的插入数据序号private static final int ID_INDEX = 1;private static final int CREATE_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;private static final StackTraceElement EMPTY_CALLER_DATA = CallerData.naInstance();// 处理主键的自动生成，这里我们使用手工生成，因此下面代码可忽略static {// PreparedStatement.getGeneratedKeys() method was added in JDK 1.4Method getGeneratedKeysMethod;try {// thegetGeneratedKeysMethod = PreparedStatement.class.getMethod("getGeneratedKeys", (Class[]) null);} catch (Exception ex) {getGeneratedKeysMethod = null;}GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;}@Overridepublic void start() {insertSQL = buildInsertSQL();cnxSupportsBatchUpdates = connectionSource.supportsBatchUpdates();// super.start();super.started = true;}// 核心代码，构建插入语句，并对应数据库字段private static String buildInsertSQL() {return "INSERT INTO t_log_logback " +"(id, create_time, message, level_string, logger_name, thread_name, " +"reference_flag, caller_filename, caller_class, caller_method, caller_line) "+"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";}// 管理每个字段插入的数据private void bindLoggingEventWithInsertStatement(PreparedStatement stmt, ILoggingEvent event) throws SQLException {// TODO 手工处理ID的生成stmt.setString(ID_INDEX, IdUtils.simpleUUID());stmt.setString(CREATE_TIME_INDEX, LocalDateTime.ofInstant(Instant.ofEpochMilli(event.getTimeStamp()),ZoneId.systemDefault()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));stmt.setString(MESSAGE_INDEX, event.getFormattedMessage());stmt.setString(LEVEL_STRING_INDEX, event.getLevel().toString());stmt.setString(LOGGER_NAME_INDEX, event.getLoggerName());stmt.setString(THREAD_NAME_INDEX, event.getThreadName());}// 管理每个字段插入的数据private void bindCallerDataWithPreparedStatement(PreparedStatement stmt, StackTraceElement[] callerDataArray) throws SQLException {StackTraceElement caller = extractFirstCaller(callerDataArray);stmt.setInt(REFERENCE_FLAG_INDEX, 0);stmt.setString(CALLER_FILENAME_INDEX, caller.getFileName());stmt.setString(CALLER_CLASS_INDEX, caller.getClassName());stmt.setString(CALLER_METHOD_INDEX, caller.getMethodName());stmt.setString(CALLER_LINE_INDEX, Integer.toString(caller.getLineNumber()));}// 核心方法，插入具体日志数据@Overrideprotected void subAppend(ILoggingEvent event, Connection connection, PreparedStatement insertStatement) throws Throwable {bindLoggingEventWithInsertStatement(insertStatement, event);// This is expensive... should we do it every time?bindCallerDataWithPreparedStatement(insertStatement, event.getCallerData());int updateCount = insertStatement.executeUpdate();if (updateCount != 1) {addWarn("Failed to insert loggingEvent");}}private StackTraceElement extractFirstCaller(StackTraceElement[] callerDataArray) {StackTraceElement caller = EMPTY_CALLER_DATA;if (hasAtLeastOneNonNullElement(callerDataArray))caller = callerDataArray[0];return caller;}private boolean hasAtLeastOneNonNullElement(StackTraceElement[] callerDataArray) {return callerDataArray != null && callerDataArray.length > 0 && callerDataArray[0] != null;}@Overrideprotected Method getGeneratedKeysMethod() {return GET_GENERATED_KEYS_METHOD;}@Overrideprotected String getInsertSQL() {return insertSQL;}protected void secondarySubAppend(ILoggingEvent event, Connection connection, long eventId) throws Throwable {}@Overrideprotected long selectEventId(PreparedStatement insertStatement, Connection connection) throws SQLException, InvocationTargetException {return 0;}
}

4. logback-spring.xml配置关联

添加自定义的追加器DbLogbackAppender

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false"><!-- 获取springboot中的数据库配置 --><springProperty scope="context" name="driverClassName" source="spring.datasource.master.driverClassName" defaultValue="driverClassName"/><springProperty scope="context" name="url" source="spring.datasource.master.url" defaultValue="url"/><springProperty scope="context" name="username" source="spring.datasource.master.username" defaultValue="username"/><springProperty scope="context" name="encryptPassword" source="spring.datasource.master.encryptPassword" defaultValue="encryptPassword"/><!-- 添加自定义DbLogbackAppender --><appender name="DB" class="com.xxx.DbLogbackAppender"><connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource"><driverClass>${driverClassName}</driverClass><url>${url}</url><user>${username}</user><password>${password}</password></connectionSource><!--临界值过滤。只记录指定级别以及高于该级别的日志--><filter class="ch.qos.logback.classic.filter.ThresholdFilter"><level>WARN</level></filter></appender><root level="DEBUGE"><appender-ref ref="DB" /></root>
</configuration>

到这里，就配置完成了，重启系统，访问日志，会将warn级别以上的日志记录到数据库表。

由于数据源是使用logback的LogbackConnectionSource，如果要自定义数据源，请往下看

5.自定义数据源

创建类LogbackConnectionSource.java，继承ConnectionSourceBase


import ch.qos.logback.core.db.ConnectionSourceBase;import java.sql.Connection;
import java.sql.SQLException;/*** <p>*  获取数据库连接用于logback，并提供DbLogbackAppender使用*  参考类：ch.qos.logback.core.db.DriverManagerConnectionSource* </p>*/
public class LogbackConnectionSource extends ConnectionSourceBase {public Connection getConnection() {获取数据库连接Connection connection = null;// 自行构建连接// ...return connection;}
}

修改logback-spring.xml配置为如下

	<!-- 添加自定义DbLogbackAppender --><appender name="DB" class="com.xxx.DbLogbackAppender"><!-- 添加自定义的数据源 --><connectionSource class="com.xxx.LogbackConnectionSource"><!-- <driverClass>${driverClassName}</driverClass><url>${url}</url><user>${username}</user><password>${password}</password>--></connectionSource><!--临界值过滤。只记录指定级别以及高于该级别的日志--><filter class="ch.qos.logback.classic.filter.ThresholdFilter"><level>WARN</level></filter></appender>