Springboot3保存日志到数据库

保存日志到数据库

请求日志几乎是所有大型企业级项目的必要的模块，请求日志对于我们来说后期在项目运行上线一段时间用于排除异常、请求分流处理、限制流量等。请求日志一般都会记录请求参数、请求地址、请求状态（Status Code）、SessionId、请求方法方式（Method）、请求时间、客户端IP地址、请求返回内容、耗时等等。如果你得系统还有其他个性化的配置，也可以完成记录。

在实际的项目中，特别是管理系统中，对于那些重要的操作我们通常都会记录操作日志。比如对数据库的CRUD操作，我们都会对每一次重要的操作进行记录，通常的做法是向数据库指定的日志表中插入一条记录。这里就产生了一个问题，难道要我们每次在 CRUD的时候都手动的插入日志记录吗？这肯定是不合适的，这样的操作无疑是加大了开发量，而且不易维护，所以实际项目中总是利用AOP(Aspect Oriented Programming)即面向切面编程这一技术来记录系统中的操作日志。Logback也提供了保存日志到数据库的功能。

1、添加依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--mysql数据源-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
            <scope>provided</scope>
        </dependency>
        <!--日志相关-->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.4.5</version>
        </dependency>
        <!-- 自动依赖 slf4j-api -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.4.5</version>
        </dependency>
     <!-- logback操作数据库的包 -->
        <dependency>
            <groupId>ch.qos.logback.db</groupId>
            <artifactId>logback-classic-db</artifactId>
            <version>1.2.11.1</version>
        </dependency>
        <!--这个依赖必须存在，否则会报java.lang.ClassNotFoundException.org.apache.commons.dbcp.BasicDataSource-->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
    </dependencies>

2、创建日志数据库

创建一个数据库logs_db，该库中创建如下表

BEGIN;
DROP TABLE IF EXISTS `system_log`;
COMMIT;
 
BEGIN;
CREATE TABLE `system_log` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
    `ip_addr` varchar(154) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '客户端ip地址',
    `message` text NOT NULL COMMENT '详情',
    `level_string` varchar(254) NOT NULL COMMENT '等级',
    `logger_name` varchar(254) NOT NULL 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;

3、创建LogDBAppender类

该类是用来操作日志数据库的类

/**
 * 自定义日志保存类
 */
@Configuration
@Slf4j
public class LogDBAppender extends DBAppenderBase<ILoggingEvent> {
    private static final int CREATE_TIME_INDEX = 1;
    private static final int UPDATE_TIME_INDEX = 2;
    private static final int IP_ADDR=3;
    private static final int MESSAGE_INDEX = 4;
    private static final int LEVEL_STRING_INDEX = 5;
    private static final int LOGGER_NAME_INDEX = 6;
    private static final int CALLER_FILENAME_INDEX = 7;
    private static final int CALLER_CLASS_INDEX = 8;
    private static final int CALLER_METHOD_INDEX = 9;
    private static final int CALLER_LINE_INDEX = 10;

    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,ip_addr, message, level_string, logger_name, 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");
        }
    }

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

    }

    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(IP_ADDR,getUserIP());
        preparedStatement.setString(MESSAGE_INDEX, iLoggingEvent.getFormattedMessage());
        preparedStatement.setString(LEVEL_STRING_INDEX, iLoggingEvent.getLevel().toString());
        preparedStatement.setString(LOGGER_NAME_INDEX, iLoggingEvent.getLoggerName());
    }

    public String getUserIP() {
        ServletRequestAttributes requestAttributes = ServletRequestAttributes.class.
                cast(RequestContextHolder.getRequestAttributes());
        HttpServletRequest contextRequest = requestAttributes.getRequest();

        String remoteAddr = "";
        if (contextRequest != null) {
            remoteAddr = contextRequest.getHeader("X-FORWARDED-FOR");
            if (remoteAddr == null || "".equals(remoteAddr)) {
                remoteAddr = contextRequest.getRemoteAddr();
            }
        }
        return remoteAddr;
    }


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

        GET_GENERATED_KEYS_METHOD = getGeneratedKeysMethod;
    }
}

4、创建配置文件logback-spring.xml

新版的logback中去除了DBAppender类，如查要保存到数据库需要重写该类，参考步骤6

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">

    <!--控制台日志格式：彩色日志-->
    <!-- magenta:洋红 -->
    <!-- boldMagenta:粗红-->
    <!-- green:绿色-->
    <!-- boldGreen:深绿色-->
    <!-- cyan:青色 -->
    <!-- white:白色 -->
    <conversionRule conversionWord="clr" converterClass="org.springframework.boot.logging.logback.ColorConverter" />
    <conversionRule conversionWord="wex" converterClass="org.springframework.boot.logging.logback.WhitespaceThrowableProxyConverter" />
    <conversionRule conversionWord="wEx" converterClass="org.springframework.boot.logging.logback.ExtendedWhitespaceThrowableProxyConverter" />
    <!-- 彩色日志格式 -->
    <property name="CONSOLE_LOG_PATTERN" value="${CONSOLE_LOG_PATTERN:-%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(${PID:- }){magenta} %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:-%wEx}}"/>

    <!--编码-->
    <property name="ENCODING" value="UTF-8"/>

    <!--输出到控制台-->
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <!--日志级别-->
            <level>DEBUG</level>
        </filter>
        <encoder>
            <!--日志格式-->
            <Pattern>${CONSOLE_LOG_PATTERN}</Pattern>
            <!--日志字符集-->
            <charset>${ENCODING}</charset>
        </encoder>
    </appender>


   <!--连接数据库配置-->
    <appender name="db_classic_mysql_pool" class="com.woniu.logs.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/logs_db?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="CONSOLE"/>
        <appender-ref ref="db_classic_mysql_pool"/>
    </root>
</configuration>

5、修改yml

server:
    port: 8080
spring:
    application:
        name: log-demo


logging:
  level:
    com:
      woniu:
        dao: DEBUG
    root: INFO
  config: classpath:logback-spring.xml

6、测试

编写测试代码

@RestController
@Slf4j
public class UserController {

    @GetMapping("login")
    public String getUser(String account,String password){
        if(account.equals("tom") && password.equals("123")){
            log.info("用户"+account+"登录成功");
        }else{
            log.warn("用户名或密码错误");
        }
        return "测试lomback保存日志";
    }
}