Log4j 2 - JDBCAppender example

Posted on January 2, 2018


JDBCAppender is used to write the log events to relational database table using the JDBC connections. This appender obtains the connection from JNDI DataSource or a custom factory method.

This post shows you how to use the JDBCAppender in java application to write the logs into a database table.

Tools and technologies used for this application are -

  • Log4j 2.10.0
  • Apache Common DBCP 2.1.1
  • Java SE 9
  • Maven 3.5.2
  • Eclipse Oxygen.2 Release (4.7.2)
  • MySQL Server 5.7

 Jar dependencies

To use JDBCAppender in your application, you need to add the JDBC driver to your classpath. 

In this example, we will use the MySQL Connector/J (official JDBC driver for MySQL).

Open pom.xml file of your application and add the following dependencies in it.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>com.boraji.tutorial.log4j2</groupId>
   <artifactId>log4j2-jdbc-appender-example</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>Log4j 2 - JDBC Appender Example</name>

   <dependencies>
      <dependency>
         <groupId>org.apache.logging.log4j</groupId>
         <artifactId>log4j-core</artifactId>
         <version>2.10.0</version>
      </dependency>
      <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-dbcp2</artifactId>
         <version>2.1.1</version>
      </dependency>
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.8-dmr</version>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.7.0</version>
            <configuration>
               <source>9</source>
               <target>9</target>
            </configuration>
         </plugin>
      </plugins>
   </build>

</project>

Database table creation

You can use the following DDL statement for table creation in MySQL database.

create table APP_LOGS(
    LOG_ID varchar(100) primary key,
    ENTRY_DATE timestamp,
    LOGGER varchar(100),
    LOG_LEVEL varchar(100),
    MESSAGE TEXT,
    EXCEPTION TEXT
);

 

Creating Connection factory class

JDBCAppender gets the connection from a JNDI DataSource or a factory method. The following example shows you how to create a factory class to obtain JDBC connection for JDBCAppender.

ConnectionFactory.java

package com.boraji.tutorial.log4j2;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;

public class ConnectionFactory {

   private static BasicDataSource dataSource;

   private ConnectionFactory() {
   }

   public static Connection getConnection() throws SQLException {
      if (dataSource == null) {
         dataSource = new BasicDataSource();
         dataSource.setUrl("jdbc:mysql://localhost:3306/BORAJI?useSSL=false");
         dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
         dataSource.setUsername("root");
         dataSource.setPassword("admin");
      }
      return dataSource.getConnection();
   }
}

Log4j 2 configuration

The <JDBC> element is used to configure the JDBCAppender. You can use either <DataSource> or <ConnectionFactory> element to specify the connection sources.

The <Column> element specifies which columns in the table should be written to and how to write to them.

The following is the complete log4j2.xml file configured with Console and JDBCAppender appenders. 

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="MySQLDatabase" tableName="APP_LOGS">
         <ConnectionFactory
            class="com.boraji.tutorial.log4j2.ConnectionFactory" method="getConnection" />
         <Column name="LOG_ID" pattern="%u" />
         <Column name="ENTRY_DATE" isEventTimestamp="true" />
         <Column name="LOGGER" pattern="%logger" />
         <Column name="LOG_LEVEL" pattern="%level" />
         <Column name="MESSAGE" pattern="%m" />
         <Column name="EXCEPTION" pattern="%throwable " />
      </JDBC>
   </Appenders>

   <Loggers>
      <Logger name="com.boraji.tutorial" level="debug"
         additivity="false">
         <AppenderRef ref="Console" />
         <AppenderRef ref="MySQLDatabase" />
      </Logger>

      <Root level="trace">
         <AppenderRef ref="Console" />
         <AppenderRef ref="MySQLDatabase" />
      </Root>
   </Loggers>

</Configuration>

To view all available options for JDBCAppender click here.

Run Application

The following is a simple java program to test the above log4j2.xml configuration.

MainApp.java

package com.boraji.tutorial.log4j2;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class MainApp {
   private static final Logger logger = LogManager.getLogger(MainApp.class);

   public static void main(String[] args) {
      logger.info("Log4j 2 Example ");

      int a, b, c;
      try {
         a = 5;
         b = 0;
         c = a / b;
         logger.info("Value of c = " + c);
      } catch (Exception e) {
         logger.error("Runtime error..", e);
      }

   }
}

After executing the MainApp.java, console output will look like as follows.

2018-Jan-02 10:31:53 PM  INFO com.boraji.tutorial.log4j2.MainApp - Log4j 2 Example  
2018-Jan-02 10:31:54 PM  ERROR com.boraji.tutorial.log4j2.MainApp - Runtime error.. 
java.lang.ArithmeticException: / by zero
	at com.boraji.tutorial.log4j2.MainApp.main(MainApp.java:16) [classes/:?]

Log events in database table will look like as follows -

mysql> select * from app_logs;
+--------------------------------------+---------------------+------------------------------------+-----------+------------------+-----------------------------------------------------------------------------------------------------------+
| LOG_ID                               | ENTRY_DATE          | LOGGER                             | LOG_LEVEL | MESSAGE          | EXCEPTION                                                                                                 |
+--------------------------------------+---------------------+------------------------------------+-----------+------------------+-----------------------------------------------------------------------------------------------------------+
| a218c541-efde-11e7-8a33-bc8556b308f1 | 2018-01-02 22:31:54 | com.boraji.tutorial.log4j2.MainApp | INFO      | Log4j 2 Example  |                                                                                                           |
| a2298e22-efde-11e7-8a33-bc8556b308f1 | 2018-01-02 22:31:54 | com.boraji.tutorial.log4j2.MainApp | ERROR     | Runtime error..  | java.lang.ArithmeticException: / by zero
        at com.boraji.tutorial.log4j2.MainApp.main(MainApp.java:16)
  |
+--------------------------------------+---------------------+------------------------------------+-----------+------------------+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)