Home » Enterprise Java » Log4j » Log4j Database Appenders Example

About Mayank Gupta

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.

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.
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.javacodegeeks</groupId>
	<artifactId>log4j-database-example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>log4j-database-example</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>apache-log4j-extras</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.4</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
		</dependency>
	</dependencies>
</project>

 

3.3 Log4J Properties

Create log4j.properties for the log4j configuration.

log4j.properties

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

# Define the file appender
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.URL=jdbc:hsqldb:mem:exampleDatabase
# Set Database Driver
log4j.appender.DB.driver=org.hsqldb.jdbc.JDBCDriver
# Set database user name and password
log4j.appender.DB.user=root
log4j.appender.DB.password=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
log4j.appender.DB.layout=org.apache.log4j.EnhancedPatternLayout

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.
NOTE:

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.

NOTE:

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.

HelloLog4J.java

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

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

	@BeforeClass
	public static void setup() {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {
			Class.forName("org.hsqldb.jdbc.JDBCDriver");

			statement.execute("CREATE TABLE APP_LOGS(THREAD VARCHAR(20), DATE_OF_OCCURENCE DATETIME, CLASS VARCHAR(100),"
					+ "LINE_NUMBER INTEGER, LEVEL VARCHAR(10), MESSAGE VARCHAR(1000), STACKTRACE CLOB)");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@AfterClass
	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);
			}
			rs.close();
			statement.executeUpdate("DROP TABLE APP_LOGS");
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

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

 

Output

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

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz