HowToDoInJava

Home / Log4j / Log4j JDBCAppender – Create Logs in Database

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

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Yusuf

    How can i use JNDI within same example given above

  2. stephen

    #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

  3. Pankaj

    Hi Is it possible to add more columns in table and insert through log4j?

  4. karthik

    what should i do if database is down

  5. Sandip

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

    • Lokesh Gupta

      1) Run your insert query directly into SQL console and verify that it is correct.
      2) Verify root logger level

  6. johnsi

     is it possible to use ms sql instead of myssql (jdbc:mysql://localhost/test)?

    • Lokesh Gupta

      Absolutely yes. It’s not DB specific.

      • johnsi

         may i get the sample configuration for ms sql with sample coding please?

        • Lokesh Gupta

          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

  7. hurric

    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

  8. bhanu

    i am trying to print log in sqlite DB is it possible?

  9. Vinay Annam

    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,.

  10. dhana

    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?

  11. Tony

    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 ?

    • Lokesh Gupta

      Nothing is impossible. Just search hard. I do not have any specific solution in mind right now.

  12. barış

    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

    • Lokesh Gupta

      I believe that log4j would not help in such cases. You need to write an LoggingInterceptor (or something like that) and insert the data using ORM APIs e.g. hibernate.

  13. Barış Taşkend

    how can ı send more than one parameter ?

  14. Sergiy Savruk

    Thanks a lot!

    • Rakesh Shrestha

      In your database table why is USER_ID field blank?

Comments are closed on this article!