Log4j Database Appenders Example

In this example, we are going to learn how to log messages from our application into database with log4j.

1. Introduction

Log4J is one of the many logging frameworks available to use with Java, perhaps the most popular of all of them. With Log4J you can log general flow of the application as well as record exceptions.

Few points to note about the example:

  1. I wanted to make the example self contained; thus have used JUnit to do the database setup for our example’s use. The logging mechanism works from any Java application source – test cases, web applications, client applications etc.
  2. There is no code necessary to be done explicitly for logging messages and errors/exceptions into DB. Log4J handles all of that (or better call it JDBCAppender).
  3. This is a self contained example using an in-memory embedded database HSQLDB (you can just download the source code and run it without any additional steps). However, in real scenarios, you will already have a DB setup for your application. For a little tutorial on how to get started with HSQLDB, go here.
  4. This example is about JDBCAppender provided by log4j. For a quick brush up on appenders, go here. There are a lot more appenders at your disposal; click here for all the details.

2. Technologies Used

For this example, we use the following tools on a Windows 10 platform:

  • Eclipse IDE
  • Apache Maven
  • JDK 1.8
  • Log4J 1.2.17
  • JUnit 4.12
  • HSQLDB 2.3.4

3. Log4j Database Appenders Example

3.1 Create a Maven Project

We will create a bare minimum Maven project. Once you have the Maven installed and running on your machine, issue the following command from the command line.

mvn archetype:generate -DgroupId=com.javacodegeeks -DartifactId=log4j-database-example -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

In this example, Maven will manage dependencies for us and we don’t need to download any jars explicitly.

3.2 Add Maven Dependencies

Add the required dependencies for log4j, HSQLDB, JUnit and log4j extras. log4j extras provide us org.apache.log4j.EnhancedPatternLayout class for formatting logging messages. Default is org.apache.log4j.PatternLayout. We will later see it’s benefit.

Your pom.xml should look like this.

<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">






3.3 Log4J Properties

Create log4j.properties for the log4j configuration.


# Define the root logger with file appender
log4j.rootLogger = DEBUG, DB

# Define the file appender
# Set Database Driver
# Set database user name and password
# Set the SQL statement to be executed.
log4j.appender.DB.layout.ConversionPattern=INSERT INTO APP_LOGS VALUES ('%t', now() ,'%C','%L','%p','%m', '%throwable{200}')
# Define the layout for SQL appender

Here is an explanation of what is in this file:
Line 2: Lowest logging level. For details on various log levels, check this.
Line 5: We use org.apache.log4j.jdbc.JDBCAppender to log messages and exceptions into the database.

The JDBCAppender provides for sending log events to a database.

Each append call adds to an ArrayList buffer. When the buffer is filled each log event is placed in a sql statement (configurable) and executed. BufferSize, db URL, User, & Password are configurable options in the standard log4j ways.
Line 6-11: Database connection properties
Line 13: ConversionPattern – Specifies the SQL statement to be executed every time a logging event occurs. This could be INSERT, UPDATE, or DELETE.
Line 15: PatternLayout – The goal of this class is to format a LoggingEvent and return the results as a String. The results depend on the conversion pattern.


1. With org.apache.log4j.PatternLayout it is not possible to log exceptions into database. This is achieved with org.apache.log4j.EnhancedPatternLayout.
2. %throwable{short} or %throwable{1} will output the first line of stack trace. throwable{none} or throwable{0} will suppress the stack trace. %throwable{n} will output n lines of stack trace if a positive integer or omit the last -n lines if a negative integer.

3.4 Test Class

Let’s write some code to deliberately log trace calls into the database. We will later print these messages to see the output. We will also intentionally cause an exception to occur to see if it gets logged into the database.


package com.javacodegeeks.log4j.database.example;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

 * HelloLog4J!
public class HelloLog4J
	private static final Logger LOG = Logger.getLogger(HelloLog4J.class);

	public void run() {
		LOG.info("This is an example of JDBCAppender of log4j!");

		LOG.debug("Start of run()");

		try {
			// Intentionally we trigger divide by zero exception
			if(23/0 > 23) {
//				some code...
		} catch(Exception e) {
			LOG.error("Execution error", e);

		LOG.debug("End of run()");

	public static void setup() {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {

		} catch (ClassNotFoundException e) {
		} catch (SQLException e) {

	public static void destroy() {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {
			ResultSet rs = statement.executeQuery("select * from APP_LOGS");
			System.out.println("Thread | Date | Class | Line Number | Level | Message | Stacktrace");
			while(rs.next()) {
				String userId = rs.getString("THREAD");
				Date date = rs.getDate("DATE_OF_OCCURENCE");
				String logger = rs.getString("CLASS");
				int line = rs.getInt("LINE_NUMBER");
				String level = rs.getString("LEVEL");
				String message = rs.getString("MESSAGE");
				String stackTrace = rs.getString("STACKTRACE");
				System.out.println(userId + " | " + date + " | " + logger + " | " + line + " | " + level + " | " 
				+ message + " | " + stackTrace);
			statement.executeUpdate("DROP TABLE APP_LOGS");
		} catch (SQLException e) {

	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:hsqldb:mem:exampleDatabase", "root", "password");



Thread | Date | Class | Line Number | Level | Message | Stacktrace
main | 2017-04-12 | com.javacodegeeks.log4j.database.example.HelloLog4J | 33 | INFO | This is an example of JDBCAppender of log4j! | 
main | 2017-04-12 | com.javacodegeeks.log4j.database.example.HelloLog4J | 35 | DEBUG | Start of run() | 
main | 2017-04-12 | com.javacodegeeks.log4j.database.example.HelloLog4J | 42 | ERROR | Execution error | java.lang.ArithmeticException: / by zero
	at com.javacodegeeks.log4j.database.example.HelloLog4J.run(HelloLog4J.java:38)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

main | 2017-04-12 | com.javacodegeeks.log4j.database.example.HelloLog4J | 45 | DEBUG | End of run() | 

4. Summary

With JDBCAppender provided by Log4J, it is possible to log both trace calls and exceptions into the database. The database table can be customised with any number of columns of our choice provided by the patterns in the org.apache.log4j.EnhancedPatternLayout class.

5. References

  1. https://examples.javacodegeeks.com/enterprise-java/log4j/log4j-appender-example/
  2. https://logging.apache.org/log4j/2.x/manual/appenders.html
  3. https://logging.apache.org/log4j/2.x/manual/index.html

6. Download The Source Code

You can download the full source code of this example here: log4j-database-example

Mayank Gupta

Senior JEE developer with experience in large scale IT projects, especially in the telecommunications and financial services sectors. Mayank has been designing and building J2EE applications since 2007. Fascinated by all forms of software development; keen to explore upcoming areas of technology like AI, machine learning, blockchain development and AR. Lover of gadgets, apps, technology and gaming.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Back to top button