Log4j2 JDBCAppender

Learn to configure the Log4j2 provided JDBCAppender that writes log events to a relational database table using standard JDBC.

The JDBCAppender obtains the database connection from a configured connection factory or JNDI datasource. Then it uses the provided column configuration to insert a new row in the log table, everytime a log statement is executed.

1. Introduction

In this tutorial, we are using the in-memory database H2.

• When the demo runs, it creates the in-memory database and executes a schema file to create a new table EVENT_LOGS. This table will be used to store the log events. In your application, you may have a persistent database, so you can skip the schema file part.
• Then we execute a few log statements. These statements are logged into the console as well as are recorded into the datase.
• We can verify the log statements and insert queries in the console logs because we have enabled the trace logging for H2 database. You must disable it in your production environment.
• Do not forget to use the database connection pooling, else the logging will be very costly in terms of performance.

2. Depedencies

For this simple demo application, we have only absolutely needed dependencies.

<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. Connection Factory

Let us start with the connection factory which will provide the connection when we want to insert the events into the database.

Notice the use of the following properties;

• TRACE_LEVEL_SYSTEM_OUT – is used to enable trace logging in H2.
• INIT=RUNSCRIPT FROM – is used to execute schema file in the beginning of application start.

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();
	}
}

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

4. Log4j2 Configuration

Its time to map the table name and columns in the database,

<?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">
		
			<!-- If Using then JNDI DateSource -->
			<!-- <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. Demo

Let’s run this demo.

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);
		}
	}
}

Check the 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'};

6. Conclusion

The above JDBCAppender example is only intended to give you a very high level of understanding that how things work overall. Please do not use this configuration in production.

Read more for related configuration in Log4J2 docs.

Happy Learning !!