Log4j2 JDBCAppender详解

学习配置Log4j2提供的JDBCAppender，该appender使用标准JDBC将日志记录写入关系型数据库表。

JDBCAppender从配置的连接工厂或JNDI数据源获取数据库连接。然后，它使用提供的列配置每次执行日志语句时在日志表中插入新行。

1.简介

在本教程中，我们使用的是H2内存数据库。

当演示运行时，它将创建内存数据库并执行schema.sql文件以创建一个新表EVENT_LOGS。此表将用于存储日志事件。在您的应用程序中，您可能有一个持久性数据库，因此可以跳过模式文件部分。
然后我们执行几个日志语句。这些语句被记录在控制台和数据库中。
我们可以在控制台日志中验证日志语句和插入查询，因为我们启用了H2数据库的跟踪日志记录。您必须在生产环境中禁用它。
不要忘记使用数据库连接池，否则日志记录在性能方面将非常糟糕。

2. 依赖项

对于这个简单的演示应用程序，我们只需要必要的maven依赖项。

<dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-core</artifactId> <version>2.14.1</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>2.0.202</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.9.0</version> </dependency>

3. 连接工厂

让我们从连接工厂开始，连接工厂将提供当我们想要将事件插入数据库时所需的连接。

请注意以下属性的使用：

TRACE_LEVEL_SYSTEM_OUT–用于启用H2的跟踪日志记录。
INIT=RUNSCRIPT FROM–用于在应用程序启动之初执行模式文件。

连接工厂类：

import java.sql.Connection; import java.sql.SQLException; import org.apache.commons.dbcp2.BasicDataSource; public class LogsStoreConnectionFactory { private static BasicDataSource dataSource; private LogsStoreConnectionFactory() { } public static Connection getConnection() throws SQLException { if (dataSource == null) { dataSource = new BasicDataSource(); dataSource.setUrl("jdbc:h2:mem:testdb;TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;"); dataSource.setDriverClassName("org.h2.Driver"); dataSource.setUsername("sa"); dataSource.setPassword(""); } return dataSource.getConnection(); } }

schema.sql 创建EVENT_LOGS表：

create table EVENT_LOGS ( ID varchar(50) primary key, DATE_TIME timestamp, CLASS varchar(100), LEVEL varchar(10), MESSAGE TEXT, EXCEPTION TEXT );

4. Log4j2配置

现在是映射数据库中的表名和列的时候了。log4j2.xml配置：

<?xml version="1.0" encoding="UTF-8"?> <Configuration> <Appenders> <Console name="Console" target="SYSTEM_OUT"> <PatternLayout pattern="%d{yyyy-MMM-dd hh:mm:ss a} %level %c - %m %n" /> </Console> <JDBC name="dbLogsStore" tableName="EVENT_LOGS"> <!-- 如果使用 JNDI 数据源--> <!-- <DataSource jndiName="java:/comp/env/jdbc/LoggingDataSource" /> --> <ConnectionFactory class="com.howtodoinjava.demo.jdbcappender.LogsStoreConnectionFactory" method="getConnection" /> <Column name="ID" pattern="%u" /> <Column name="DATE_TIME" isEventTimestamp="true" /> <Column name="CLASS" pattern="%logger" /> <Column name="LEVEL" pattern="%level" /> <Column name="MESSAGE" pattern="%message" /> <Column name="EXCEPTION" pattern="%ex{full}" isClob="true"/> </JDBC> </Appenders> <Loggers> <Root level="trace"> <AppenderRef ref="Console" /> <AppenderRef ref="dbLogsStore" /> </Root> </Loggers> </Configuration>

5. 演示

让我们运行这个测试案例。

import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; public class JDBCAppenderExample { private static final Logger logger = LogManager.getLogger(JDBCAppenderExample.class); public static void main(String[] args) { logger.info("JDBCAppender Example "); try { logger.debug("Demo Statement"); int i = 1/0; } catch (Exception e) { logger.error("Runtime error", e); } } }

请检查控制台日志：

Console Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zeroConsole Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zero\000d\000a\0009at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15)\000d\000a'}; 0d Console Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zero\000d\000a\0009at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15)\000d\000a'}; 0a Console Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zero\000d\000a\0009at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15)\000d\000a'}; 09at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) Console Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zero\000d\000a\0009at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15)\000d\000a'}; 0d Console Logs/*SQL #:1 t:14*/RUNSCRIPT FROM 'classpath:schema.sql'; 2021-12-07 23:39:21 jdbc[3]: /**/Connection conn0 = DriverManager.getConnection("jdbc:h2:mem:testdb; TRACE_LEVEL_SYSTEM_OUT=2;INIT=RUNSCRIPT FROM 'classpath:schema.sql'\;", "SA", ""); ... ... 2021-Dec-07 11:39:21 pm DEBUG com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Demo Statement 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS,LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd4e6b02-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.328', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'DEBUG', 5: 'Demo Statement', 6: ''}; ... ... 2021-Dec-07 11:39:21 pm ERROR com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample - Runtime error java.lang.ArithmeticException: / by zero at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15) [classes/:?] 2021-12-07 23:39:21 jdbc[3]: /*SQL l:88 #:1*/insert into EVENT_LOGS (ID,DATE_TIME,CLASS, LEVEL,MESSAGE,EXCEPTION) values (?,?,?,?,?,?) {1: 'cd5018b3-5788-11ec-a719-70cf49366217', 2: TIMESTAMP '2021-12-07 23:39:21.329', 3: 'com.howtodoinjava.demo.jdbcappender.JDBCAppenderExample', 4: 'ERROR', 5: 'Runtime error', 6: U&'java.lang.ArithmeticException: / by zero\000d\000a\0009at com.howtodoinjava.demo.jdbcappender. JDBCAppenderExample.main(JDBCAppenderExample.java:15)\000d\000a'};

0a'};