SpringBoot Logback日志记录到数据库的实现方法

    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-web</artifactId>    </dependency>    <!-- 这个依赖必须存在，否则会报java.lang.ClassNotFoundException: org.apache.commons.dbcp.BasicDataSource-->    <dependency>      <groupId>commons-dbcp</groupId>      <artifactId>commons-dbcp</artifactId>      <version>1.4</version>    </dependency>    <dependency>      <groupId>mysql</groupId>      <artifactId>mysql-connector-java</artifactId>      <scope>runtime</scope>    </dependency>

<?xml version="1.0" encoding="UTF-8"?><configuration debug="false">  <!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->  <property name="LOG_HOME" value="/home/admin" />  <!-- 控制台输出 -->  <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender">    <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">      <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->      <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>    </encoder>  </appender>  <!-- 按照每天生成日志文件 -->  <appender name="application_file" class="ch.qos.logback.core.rolling.RollingFileAppender">    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">      <!--日志文件输出的文件名-->      <FileNamePattern>${LOG_HOME}/info/info.log.%d{yyyy-MM-dd}.log</FileNamePattern>      <!--日志文件保留天数-->      <MaxHistory>30</MaxHistory>    </rollingPolicy>    <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">      <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->      <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>    </encoder>    <!--日志文件最大的大小-->    <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">      <MaxFileSize>500MB</MaxFileSize>    </triggeringPolicy>  </appender>  <!-- 异常日志文件 -->  <appender name="error_file" class="ch.qos.logback.core.rolling.RollingFileAppender">    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">      <!--日志文件输出的文件名-->      <FileNamePattern>${LOG_HOME}/error/error.log.%d{yyyy-MM-dd}.log</FileNamePattern>      <!--日志文件保留天数-->      <MaxHistory>30</MaxHistory>    </rollingPolicy>    <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">      <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->      <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>    </encoder>    <!--日志文件最大的大小-->    <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">      <MaxFileSize>500MB</MaxFileSize>    </triggeringPolicy>    <!-- 只打印错误日志 -->    <filter class="ch.qos.logback.classic.filter.LevelFilter">      <level>error</level>      <onMatch>ACCEPT</onMatch>      <onMismatch>DENY</onMismatch>    </filter>  </appender>  <!--连接数据库配置-->  <appender name="db_classic_mysql_pool" class="ch.qos.logback.classic.db.DBAppender">    <connectionSource class="ch.qos.logback.core.db.DataSourceConnectionSource">      <dataSource class="org.apache.commons.dbcp.BasicDataSource">        <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>        <url>jdbc:mysql://127.0.0.1:3306/logdb?serverTimezone=Asia/Shanghai</url>        <username>root</username>        <password>123456</password>      </dataSource>    </connectionSource>  </appender>  <!--myibatis log configure-->  <logger name="com.apache.ibatis" level="TRACE"/>  <logger name="java.sql.Connection" level="DEBUG" />  <logger name="java.sql.Statement" level="DEBUG"/>  <logger name="java.sql.PreparedStatement" level="DEBUG"/>  <!-- 日志输出级别 -->  <root level="INFO">    <appender-ref ref="stdout" />    <appender-ref ref="application_file" />    <appender-ref ref="error_file"/>    <appender-ref ref="db_classic_mysql_pool" />  </root></configuration>

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;

@RunWith(SpringRunner.class)@SpringBootTestpublic class Springboot02MybatisApplicationTests {  private final Logger logger = LoggerFactory.getLogger(Springboot02MybatisApplicationTests.class);  @Test  public void contextLoads() {    logger.info("数据库日志info");    logger.error("数据库日志error");  }}

DROP TABLE IF EXISTS `logging`;CREATE TABLE `logging` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `message` VARCHAR(300) NOT NULL COMMENT '内容', `level_string` VARCHAR(254) NOT NULL COMMENT '级别', `created_time` DATETIME NOT NULL COMMENT '时间', `logger_name` VARCHAR(300) NOT NULL COMMENT '全类名', PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='自定义日志记录表'

package com.me.study.springboot02mybatis.config;import ch.qos.logback.classic.spi.CallerData;import ch.qos.logback.classic.spi.ILoggingEvent;import ch.qos.logback.core.db.DBAppenderBase;import org.springframework.context.annotation.Configuration;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Timestamp;@Configurationpublic class LogDBAppender extends DBAppenderBase<ILoggingEvent> {  protected static final Method GET_GENERATED_KEYS_METHOD;  //插入sql  protected String insertSQL;  // message 日志内容  static final int MESSAGE = 1;  // level_string  static final int LEVEL_STRING = 2;  // created_time 时间  static final int CREATE_TIME = 3;  // logger_name 全类名  static final int LOGGER_NAME = 4;  static final StackTraceElement EMPTY_CALLER_DATA = CallerData.naInstance();  static {    // PreparedStatement.getGeneratedKeys() method was added in JDK 1.4    Method getGeneratedKeysMethod;    try {      // the      getGeneratedKeysMethod = PreparedStatement.class.getMethod("getGeneratedKeys", (Class[]) null);    } catch (Exception ex) {      getGeneratedKeysMethod = null;    }    GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;  }  @Override  public void start() {    // 将写好的sql语句赋值给insertSQL    insertSQL = buildInsertSQL();    super.start();  }  // 自己写新增sql语句  private static String buildInsertSQL() {    return "INSERT INTO `logging`(`message`,`level_string`,`created_time`,`logger_name`)" +        "VALUES (?,?,?,?)";  }  @Override  protected Method getGeneratedKeysMethod() {    return GET_GENERATED_KEYS_METHOD;  }  @Override  protected String getInsertSQL() {    return insertSQL;  }  /**   * 主要修改的方法   *   * @param stmt   * @param event   * @throws SQLException   */  private void bindLoggingEventWithInsertStatement(PreparedStatement stmt, ILoggingEvent event) throws SQLException {    // event.getFormattedMessage() 日志打印内容    String message = event.getFormattedMessage();    // 如果只想存储自己打印的日志，可以这样写日志：logger.info("- XXXX")    if(message.startsWith("-")){ // 判断日志消息首字母为 - 的日志，记录到数据库表      stmt.setString(MESSAGE, message);      // event.getLevel().toString() 日志级别      stmt.setString(LEVEL_STRING, event.getLevel().toString());      // new Timestamp(event.getTimeStamp()) 时间      stmt.setTimestamp(CREATE_TIME, new Timestamp(event.getTimeStamp()));      // event.getLoggerName() 全类名      stmt.setString(LOGGER_NAME, event.getLoggerName());    }  }  @Override  protected void subAppend(ILoggingEvent eventObject, Connection connection, PreparedStatement statement) throws Throwable {    bindLoggingEventWithInsertStatement(statement, eventObject);    // This is expensive... should we do it every time?    int updateCount = statement.executeUpdate();    if (updateCount != 1) {      addWarn("Failed to insert loggingEvent");    }  }  @Override  protected void secondarySubAppend(ILoggingEvent eventObject, Connection connection, long eventId) throws Throwable {  }}

  <!--连接数据库配置-->  <appender name="db_classic_mysql_pool" class="com.me.study.springboot02mybatis.config.LogDBAppender">    <connectionSource class="ch.qos.logback.core.db.DataSourceConnectionSource">      <dataSource class="org.apache.commons.dbcp.BasicDataSource">        <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>        <url>jdbc:mysql://127.0.0.1:3306/logdb?serverTimezone=Asia/Shanghai</url>        <username>root</username>        <password>admin</password>      </dataSource>    </connectionSource>  </appender>

  @Test  public void contextLoads() {    logger.info("- 数据库日志info");    logger.error("- 数据库日志error");    logger.info("一条不带‘-'的日志，看会不会记录如数据库");  }