HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
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 !!

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.
TwitterFacebookLinkedInRedditPocket

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

    September 13, 2019

    How can i use JNDI within same example given above

  2. stephen

    April 11, 2019

    #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

    • Lokesh Gupta

      April 11, 2019

      Thanks for sharing.

  3. Pankaj

    June 26, 2018

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

  4. karthik

    May 19, 2017

    what should i do if database is down

  5. Sandip

    November 17, 2014

    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

      November 17, 2014

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

  6. johnsi

    July 31, 2014

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

      July 31, 2014

      Absolutely yes. It’s not DB specific.

      • johnsi

        July 31, 2014

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

          July 31, 2014

          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

    July 24, 2014

    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

    July 22, 2014

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

    • Lokesh Gupta

      July 22, 2014

      I have no good idea around it.

  9. Vinay Annam

    March 28, 2014

    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

    March 3, 2014

    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

    December 6, 2013

    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

      December 7, 2013

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

  12. barış

    October 9, 2013

    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

      October 9, 2013

      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

    October 8, 2013

    how can ı send more than one parameter ?

    • Lokesh Gupta

      October 8, 2013

      Like what information?

  14. Sergiy Savruk

    September 19, 2013

    Thanks a lot!

    • Rakesh Shrestha

      December 19, 2014

      In your database table why is USER_ID field blank?

Comments are closed on this article!

Search Tutorials

Log4j2 Tutorial

  • Log4j2 – Introduction
  • Log4j2 – JSON Config
  • Log4j2 – Properties Config
  • Log4j2 – XML Config
  • Log4j2 – RollingFileAppender
  • Log4j2 – Multiple appenders
  • Log4j2 – LevelRangeFilter
  • Log4j2 – HTMLLayout
  • Log4j2 – Fish Tagging
  • Log4j2 – Conversion Patterns
  • Log4j2 – JUnit

Log4j Tutorial

  • Log4j – Introduction
  • Log4j – Properties Config
  • Log4j – XML Config
  • Log4j – Maven Config
  • Log4j – Logging Levels
  • Log4j – ConsoleAppender
  • Log4j – RollingFileAppender
  • Log4j – SocketAppender
  • Log4j – JDBCAppender
  • Log4j – XMLLayout
  • Log4j – HTMLLayout
  • Log4j – Runtime Reload
  • Log4j vs. SLF4j
  • Log4j – RESTEasy + Tomcat 7

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Sealed Classes and Interfaces