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.PatternLayoutStep 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.
Let me know if any question.
Happy Learning !!
Yusuf
How can i use JNDI within same example given above
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
Lokesh Gupta
Thanks for sharing.
Pankaj
Hi Is it possible to add more columns in table and insert through log4j?
karthik
what should i do if database is down
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
johnsi
Lokesh Gupta
Absolutely yes. It’s not DB specific.
johnsi
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.PatternLayouthurric
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
bhanu
i am trying to print log in sqlite DB is it possible?
Lokesh Gupta
I have no good idea around it.
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,.
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?
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.
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.
Barış Taşkend
how can ı send more than one parameter ?
Lokesh Gupta
Like what information?
Sergiy Savruk
Thanks a lot!
Rakesh Shrestha
In your database table why is USER_ID field blank?