HowToDoInJava

Log4j JDBCAppender – Create Logs in Database

By | Filed Under: Log4j

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

Let me know if any question.

Happy Learning !!

LinkedinRedditPocket

Feedback, Discussion and Comments

  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 Is it possible to add more columns in table and insert through log4j?

    Reply

  3. what should i do if database is down

    Reply

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

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

      Reply

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

    • Absolutely yes. It’s not DB specific.

      Reply

      •  may i get the sample configuration for ms sql with sample coding please?
        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

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

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

    Reply

    • I have no good idea around it.

      Reply

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

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

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

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

      Reply

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

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

      Reply

  12. how can ı send more than one parameter ?

    Reply

  13. Thanks a lot!

    Reply

    • In your database table why is USER_ID field blank?

      Reply

Ask Questions & Share Feedback

Please do not submit a comment only to say "Thank you".

*Want to Post Code Snippets or XML content? Please use [java] ... [/java] tags otherwise code may not appear partially or even fully. e.g.
[java] 
public static void main (String[] args) {
...
} 
[/java]