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();}@Overridepublic void start() {this.insertSQL = buildInsertSQL();super.start();}@Overrideprotected Method getGeneratedKeysMethod() {return GET_GENERATED_KEYS_METHOD;}@Overrideprotected String getInsertSQL() {return this.insertSQL;}@Overrideprotected 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");}}@Overrideprotected 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-demologging:level:com:woniu:dao: DEBUGroot: INFOconfig: 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保存日志";}
}