How to Log Log4J’s Message Logs Into Database

Today im going to do a simple log4j logging into mysql database, the only reason i want to log into database instead of into file is so that i could query the logs i have.

This is my java class that i use to do my testcase,

package com.edw.main;

import java.io.IOException;
import java.sql.SQLException;
import org.apache.log4j.Logger;

public class Main {

    private static Logger logger = Logger.getLogger(Main.class);

    private void doSomething() {
        logger.debug("im doing something");
        logger.error("im doing something - error -");
    }

    public static void main(String[] args) {
        Main main = new Main();
        main.doSomething();
    }
}

and my simple table, to store all my log messages

CREATE TABLE `logs` (
  `thread_id` varchar(20) NOT NULL,
  `tanggal` datetime NOT NULL,
  `kelas` varchar(50) NOT NULL,
  `level` varchar(10) NOT NULL,
  `pesan` varchar(1000) NOT NULL,
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

This is my log4j.properties,

# 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/test

# 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=password

# Set the SQL statement to be executed.
log4j.appender.DB.sql=INSERT INTO LOGS VALUES('%x',now(),'%C:%L','%p','%m', null)

# Define the layout for file appender
log4j.appender.DB.layout=org.apache.log4j.PatternLayout

And this is what the contents of my database

This is my Netbeans; Project structure, as you can see, i only need 2 jars, mysql driver and log4j jar.