Log4j jdbc appender with multiple columns

Logging is great. No question, every one is happy about having logfiles to follow application’s flow. But in case you are confronted with a large bunch of logfiles, let’s say thousands of requests/responses of your adapter, you need an option for fast filtering or better: querying. So it would be nice to have the logged content stored in a relational database for executing SQL statements on it.

project

First: prepare the database. In this case I use SQLite db that will be fed by Log4j framework:

create table logtable(
    DATED   				DATE           NOT NULL,
    LOGGER  				VARCHAR(100)   NOT NULL,
    LEVEL   				VARCHAR(100)   NOT NULL,
    PROCESS  				VARCHAR(1000)  NOT NULL,
    INTERNALRUNTIME  		        VARCHAR(1000)  NOT NULL,
    MESSAGE 				VARCHAR(1000)  NOT NULL
);

The easiest way to prepare the sqlite database is:

  1. sqlite3.exe testdb
  2. sqlite> .read prep.sql
  3. sqlite> .quit
  4. sqlitebrowser.exe testdb (SQLiteBrowser is a nice tool for having a GUI interface.)

The Java class representing the adapter:

package eu.christophburmeister.logj4jdbcappender;

import org.apache.log4j.Logger;
import org.apache.log4j.MDC;

public class Start {
	
	private static final Logger REQ_LOGGER = Logger.getLogger("req-resp.logger");
	
	public static void main(String[] args) {
		for (int i = 0; i < 10; i++) {
			String request = "<request id=\"" + i + "\" />";
			String process = "process_" + i;
			String internalRuntime = "23sec";
						
			MDC.put("process", process);
			MDC.put("internalRuntime", internalRuntime);
			REQ_LOGGER.info(request);
		}
	}
}

The log4j config xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration>

	<appender name="DB" class="org.apache.log4j.jdbc.JDBCAppender">
		<param name="URL"
			value="jdbc:sqlite:C:/dev/eclipse-jee-luna-R-win32-x86_64-ws/log4jDbAppenderExample/database/testdb" />
		<param name="driver" value="org.sqlite.JDBC" />
		<!--param name="user" value="user_id"/ -->
		<!--param name="password" value="password"/ -->

		<!-- 
			%d =>DATED  
			%C =>LOGGER 
			%p =>LEVEL
			%X{process} =>PROCESS
			%X{internalRuntime} =>INTERNALRUNTIME
			%m =>MESSAGE 
		-->
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern"
				value="
				INSERT 
					INTO logtable (DATED, LOGGER, LEVEL, MESSAGE, PROCESS, INTERNALRUNTIME, MESSAGE)
					VALUES ('%d','%C','%p','%m','%X{process}','%X{internalRuntime}','%m')" />
		</layout>
	</appender>

	<logger name="req-resp.logger">
		<level value="INFO" />
		<appender-ref ref="DB" />
	</logger>

</log4j:configuration>

The result can be easily obtained from sqliteBrowser:
result

As you can see, the message from the logger call is retrieved in config.xml via %m while the process and the internalRuntime values are retrieved from k-v-map in %X via MDC.
From log4j’s javadoc:

A Mapped Diagnostic Context, or MDC in short, is an instrument for distinguishing interleaved log output from different sources.

So basically this MDC approach is going to be used within multithreaded applications (e.g. webservices) as it is described here: http://veerasundar.com/blog/2009/11/log4j-mdc-mapped-diagnostic-context-example-code/
But in the end, it is a quite good option to pass multiple pieces of information to our appender.