Log4j2: JDBCAppender: Write log messages to a table

By using JDBCAppender, you can write log records to RDBMS tables. Following are the JDBCAppender Parameters.

Parameter	Type	Description
name	String	Name of the appender.
ignoreExceptions	boolean	If ignoreExceptions set to false exceptions will be propagated to the caller. If ignoreExceptions is set to true, exceptions encountered while appending events to be internally logged and then ignored. Default value is true.
filter	Filter	Filters are used to filter the log events. Filters take the decision, whether to log the event (or) not. More than one Filter may be used by using a CompositeFilter.
bufferSize	int	Size of buffer. Buffer is used to log events and flush whenever the buffer reaches this size.
connectionSource	ConnectionSource	The connections source from which database connections should be retrieved.
tableName	String	Name of the database table.
columnConfigs	ColumnConfig[]	Information about the columns that log event data should be inserted into and how to insert that data. This is represented with multiple <Column> elements.

While configuring, you must specify ConnectionSource implementation from which the Appender gets JDBC connections. You must use exactly one of the <DataSource> or <ConnectionFactory> nested elements.

Following are the DataSource Parameters.

Parameter	Type	Description
jndiName	String	The full, prefixed JNDI name that the javax.sql.DataSource is bound to.

        

Following are the ConnectionFactory Parameters.

Parameter	Type	Description
class	Class	Name of the class, that contains a static factory method for obtaining JDBC connections.
method	Method	The name of a static factory method for obtaining JDBC connections. Method return type must be either java.sql.Connection or DataSource.

As I said, Column element is used to specify the columns of table, to written data. Following are the parameters for the column element.

Parameter	Type	Description
name	String	Name of the database column.
pattern	String	Used to insert values using PatternLayout pattern. Either this attribute, literal, or isEventTimestamp="true" must be specified, but not more than one of these.
literal	String	Use this attribute to insert a literal value in this column.
isEventTimestamp	boolean	Use this attribute to insert the event timestamp in this column, which should be a SQL datetime.
isUnicode	boolean	This attribute is ignored unless pattern is specified. If true or omitted (default), the value will be inserted as unicode (setNString or setNClob). Otherwise, the value will be inserted non-unicode (setString or setClob).
isClob	Boolean	This attribute is ignored unless pattern is specified. Use this attribute to indicate that the column stores Character Large Objects (CLOBs).

Following is the step-by-step procedure for simple application.

Step 1: Create database logging and create table ‘application_log’.

mysql> create database logging;
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> 
mysql> CREATE TABLE logging.application_log (iD INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, EVENT_DATE varchar(100), LEVEL varchar(100), LOGGER  varchar(100), MESSAGE  varchar(100), THROWABLE  varchar(100)  );
Query OK, 0 rows affected (0.01 sec)

Step 2: Define ConnectionFactory class to get a connection object.

package log4j_tutorial;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class ConnectionFactory {
 private static interface Singleton {
  final ConnectionFactory INSTANCE = new ConnectionFactory();
 }

 private final DataSource dataSource;

 private ConnectionFactory() {
  Properties properties = new Properties();
  properties.setProperty("user", "root");
  properties.setProperty("password", "tiger");

  GenericObjectPool pool = new GenericObjectPool();
  DriverManagerConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
    "jdbc:mysql://127.0.0.1/logging", properties);
  new PoolableConnectionFactory(connectionFactory, pool, null,
    "SELECT 1", 3, false, false,
    Connection.TRANSACTION_READ_COMMITTED);

  this.dataSource = new PoolingDataSource(pool);
 }

 public static Connection getDatabaseConnection() throws SQLException {
  return Singleton.INSTANCE.dataSource.getConnection();
 }
}

Step 3: Update log4j2.xml like below.

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="error">
 <Appenders>
  <JDBC name="databaseAppender" tableName="LOGGING.APPLICATION_LOG">
   <ConnectionFactory class="log4j_tutorial.ConnectionFactory"
    method="getDatabaseConnection" />
   <Column name="EVENT_DATE" isEventTimestamp="true" />
   <Column name="LEVEL" pattern="%level" />
   <Column name="LOGGER" pattern="%logger" />
   <Column name="MESSAGE" pattern="%message" />
   <Column name="THROWABLE" pattern="%ex{full}" />
  </JDBC>
 </Appenders>
 <Loggers>
  <Root level="warn">
   <AppenderRef ref="databaseAppender" />
  </Root>
 </Loggers>
</Configuration>

Step 4: Following is the simple HelloWorld application.

package log4j_tutorial;

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

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

 public static void main(String args[]) throws InterruptedException {
  logger.trace("Trace Message!");
  logger.debug("Debug Message!");
  logger.info("Info Message!");
  logger.warn("Warn Message!");
  logger.error("Error Message!");
  logger.fatal("Fatal Message!");
 }
}

Run HelloWorld application, you will get data populated in application_log table.

mysql> select * from application_log;
+----+-------------------------+-------+---------------------------+----------------+-----------+
| iD | EVENT_DATE              | LEVEL | LOGGER                    | MESSAGE        | THROWABLE |
+----+-------------------------+-------+---------------------------+----------------+-----------+
|  1 | 2015-10-04 21:29:59.879 | WARN  | log4j_tutorial.HelloWorld | Warn Message!  |           |
|  2 | 2015-10-04 21:29:59.898 | ERROR | log4j_tutorial.HelloWorld | Error Message! |           |
|  3 | 2015-10-04 21:29:59.905 | FATAL | log4j_tutorial.HelloWorld | Fatal Message! |           |
+----+-------------------------+-------+---------------------------+----------------+-----------+
3 rows in set (0.00 sec)

Note:

Use database connection pool, to get better performance.

Prevoius                                                 Next                                                 Home