Log4j JDBCAppender – Create Logs in Database

Log4j is a simple and flexible logging framework. Logging equips the developer with detailed context for application failures. With log4j it is possible to enable logging at runtime without modifying the application binary. The log4j package is designed so that these statements can remain in shipped code without incurring a heavy performance cost.

Log4j comes with multiple options to format log files created by framework. It can create simple log files, html log files or xml log files also. It also insert log statements into database also, using mysql statements.

This this post, I am showing the example code for configuring log4j to produce logs in database table.

Step 1) Create a maven java project and update log4j dependencies

Follow the steps given in this post related to configuring log4j with maven.

Step 2) Configure JDBCAppender in log4j.properties file

The JDBCAppender provides mechanism for sending log events to a database tables. 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.

WARNING: This version of JDBCAppender is very likely to be completely replaced in the future. Moreoever, it does not log exceptions.

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

# Define the file appender
log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.sql.URL=jdbc:mysql://localhost/test
# Set Database Driver
log4j.appender.sql.driver=com.mysql.jdbc.Driver
# Set database user name and password
log4j.appender.sql.user=root
log4j.appender.sql.password=password
# Set the SQL statement to be executed.
log4j.appender.sql.sql=INSERT INTO LOGS VALUES ('%x', now() ,'%C','%p','%m')
# Define the xml layout for file appender
log4j.appender.sql.layout=org.apache.log4j.PatternLayout

Step 3) Create the table in database and test the application

Create the database table LOGS, in schema test.

CREATE TABLE LOGS
(
	USER_ID VARCHAR(20) NOT NULL,
	DATED   DATETIME NOT NULL,
	LOGGER  VARCHAR(50) NOT NULL,
	LEVEL   VARCHAR(10) NOT NULL,
	MESSAGE VARCHAR(1000) NOT NULL
);

Now, configure the log4j.properties file using PropertyConfigurator and call some log events.

package com.howtodoinjava;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class Log4jJDBCExample
{
	static Logger log = Logger.getLogger(Log4jJDBCExample.class);

	public static void main(String[] args)
	{
		PropertyConfigurator.configure("log4j.properties");

		log.debug("Sample debug message");
		log.info("Sample info message");
		log.error("Sample error message");
		log.fatal("Sample fatal message");
	}
}

Log statements will be inserted in database using sql statement.
log4j-jdbc-example-9919319

Let me know if any question.

Happy Learning !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

24 thoughts on “Log4j JDBCAppender – Create Logs in Database”

  1. #using mysql server :

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

    # Define the DB appender
    log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender

    # Set JDBC URL
    log4j.appender.DB.URL=jdbc:mysql://localhost:3306/db name

    # Set Database Driver
    log4j.appender.DB.driver=com.mysql.jdbc.Driver

    # Set database user name and password
    log4j.appender.DB.user=root
    log4j.appender.DB.password=

    # Set the SQL statement to be executed.
    log4j.appender.DB.sql=INSERT INTO LOGS VALUES (‘%x’, now() ,’%C’,’%p’,’%m’)
    # Define the layout for file appender
    log4j.appender.DB.layout=org.apache.log4j.PatternLayout

    Reply
  2. Hi,
    I tried your example and worked fine . Thanks for that. In the same way I configured my project log.proporties which is in struts 2 Jboss 4.0.3 SP1 . But for unknown reason am not able to execute insert. I tried with even hardcoded insert query in properties file. Can you please tell me where I may be wrong ?

    In extended class I have used
    protected final Logger logger = Logger.getLogger(getClass());
    logger.info(“Inserting info”);

    Reply
        • JDBCAppender Configuration for MSSQL Server; Not tested but it should work fine.

          # Define the root logger with file appender
          log4j.rootLogger = DEBUG, sql
          # Define the file appender
          log4j.appender.sql=org.apache.log4j.jdbc.JDBCAppender
          log4j.appender.sql.URL=jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=LOG
          # Set Database Driver
          log4j.appender.sql.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
          # Set database user name and password
          log4j.appender.sql.user=sa
          log4j.appender.sql.password=sa
          # Set the SQL statement to be executed.
          log4j.appender.sql.sql=INSERT INTO LOGS VALUES ('%x', getDate() ,'%C','%p','%m')
          # Define the xml layout for file appender
          log4j.appender.sql.layout=org.apache.log4j.PatternLayout
          
          Reply
  3. looks like this has completely changed in log4j 2 as you mentioned. I think need to use Annotations but haven’t found a good example

    Reply
  4. Hi I tried to Append the logs to a DB using JDBC Appender, but i couldn’t achieve it.. for the specified level of logger . Below is the appender i’m using for this, can anyone help me with this regard,.

    Reply
  5. Is it possible to write log4j logs into Oracle AQ (advanced queue) using any of the log4j appenders ?
    We are trying to write the log to queue and then using weblogic bridge we want to write those db logs to a weblogic queue or topic and then write into to files on weblogic server. Is it possible?

    Reply
  6. We can’t store the database user name & password in the property files. We have to use the JNDI datasource configured in the app server. Is there a way to do that ?

    Reply
  7. for ex : I want to send five different message to database. And I want to log all messages to different column.

    You log only one message. But ı want to send a few different parameter to log database columns

    Reply

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.