Log into Database

Log Table Structure:

CREATE TABLE LOGS
  (
    USER_ID   VARCHAR2(20 BYTE) ,
    DATED     TIMESTAMP(6) NOT NULL ,
    LOG_LEVEL VARCHAR2(10 BYTE) NOT NULL ,
    LOGGER    VARCHAR2(100 BYTE) NOT NULL ,
    METHOD    VARCHAR2(100 BYTE) NOT NULL ,
    LINE      NUMBER NOT NULL ,
    MESSAGE   VARCHAR2(1000 BYTE) NOT NULL
  );

Log4j Configuration File:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

    <appender name="jdbcAppender"
class="com.company.project.package.logger.Log4JJDBCAppender">
<!-- threshold fatal ensures only fatal (and above level if any) msgs only
will logged -->
<param name="threshold" value="FATAL" />
<!-- If you want the raw connection then uncoment this segment, otherwise datasource will be used
<param name="URL" value="jdbc:mysql://localhost/test" />
        <param name="user" value="root" />
        <param name="password" value="admin" />
        <param name="driver" value="com.mysql.jdbc.Driver" />
-->
<param name="dataSource" value="jdbc/ekp_refactor" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern"
value="INSERT INTO LOGS (USER_ID, DATED, LOG_LEVEL, LOGGER, METHOD, LINE, MESSAGE) VALUES('%X{username}','%d{dd MMM yyyy hh:mm:ss a}','%p','%C','%5M','%L','%m')" />
</layout>
<filter class="com.company.project.package.logger.DBLoggingFilter">
</filter>
</appender>

    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern"
              value="%d{ISO8601} %p (%C;%L) %m%n"
            />
        </layout>
    </appender>

    <logger name="company.com.package">
        <level value="debug"/>
        <appender-ref ref="jdbcAppender"/>
    </logger>

    <root>
        <level value="error"/>
        <appender-ref ref="STDOUT"/>
    </root>

</log4j:configuration>

Java Files for customization:

Log4JJDBCAppender.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.jdbc.JDBCAppender;
import org.apache.log4j.spi.ErrorCode;

/**
 *
 * JDBC Appender to get the connection to log messages into DB via Log4j. Extension of the log4j {@link JDBCAppender} to allow using a JNDI data source rather
 * than a direct connection.
 *
 *
 * Example configuration:
 *
 *

 * <appender name="database" class="com.company.project.package.Log4JJDBCAppender">
 * <param name="dataSource" value="java:comp/jdbc/datasource" />
 * <layout class="org.apache.log4j.PatternLayout">
 * <param name="ConversionPattern" value="INSERT INTO LOGS (USER_ID, DATED, LOG_LEVEL, LOGGER, METHOD, LINE, MESSAGE) 
 * VALUES('%X{username}','%d{dd MMM yyyy hh:mm:ss a}','%p','%C','%5M','%L','%m')" />
 * </layout>
 * </appender>
 * 

 *
 * @author Amit
 */
public class Log4JJDBCAppender extends JDBCAppender {

    private static final Pattern SQL_VALUE_PATTERN = Pattern.compile("'(.*?)'(?=\\s*[,)])", Pattern.MULTILINE);
    private String dataSource;

    /** {@inheritDoc} */
    @Override
    protected Connection getConnection() throws SQLException {
        if (getDataSource() == null) {
            return super.getConnection();
        } else {
            return lookupDataSource().getConnection();
        }
    }

    /**
     * Looks up the datasource in the naming context specified by the {@link #dataSource}.
     *
     * @return the datasource.
     */
    private DataSource lookupDataSource() {
        try {
            Context context = new InitialContext();
            return (DataSource)context.lookup(getDataSource());
        } catch (NamingException e) {
            throw new RuntimeException("Cannot find JNDI DataSource: " + getDataSource(), e);
        }
    }

    /** {@inheritDoc} */
    @Override
    protected void closeConnection(Connection con) {
        try {
            con.close();
        } catch (SQLException e) {
            errorHandler.error("Failed to close connection", e, ErrorCode.CLOSE_FAILURE);
        }
    }

    /**
     * Executes the specified SQL statement, by parsing its values and turning them into parameters, so that characters that must be escaped in a SQL statement
     * are supported.
     */
    @Override
    protected void execute(String sql) throws SQLException {
        String statement = sql;
        ArrayList args = new ArrayList();
        Matcher m = SQL_VALUE_PATTERN.matcher(sql);
        while (m.find()) {
            args.add(m.group(1));
            statement = statement.replace(m.group(), "?");
        }

        executeStatement(statement, args.toArray(new String[args.size()]));
    }

    /**
     * Executes the statement settings its parameters to the specified arguments.
     *
     * @param statement
     *            the statement to execute.
     * @param args
     *            the parameter values.
     */
    protected void executeStatement(String statement, String[] args) throws SQLException {
        Connection con = getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = con.prepareStatement(statement);
            for (int i = 0; i < args.length; i++) {
                stmt.setString(i + 1, args[i]);
            }
            stmt.executeUpdate();
        } catch (SQLException e) {
            if (stmt != null) {
                stmt.close();
            }
            throw e;
        }
        stmt.close();
        closeConnection(con);
    }

    public void setDataSource(String dataSource) {
        this.dataSource = dataSource;
    }

    public String getDataSource() {
        return dataSource;
    }
}

DBLoggingFilter.java

import org.apache.log4j.spi.Filter;
import org.apache.log4j.spi.LoggingEvent;

/**
 *
 * This is Filter executes before entry of message into the database. Here, the
 * method to decide whether messages should me logged into the Database or Not.
 *
 * @author Amit
 *
 */
public class DBLoggingFilter extends Filter {

    @Override
    public int decide(LoggingEvent le) {

        Object loggingEnabledObject = le.getMDC("loggingEnabled");

        if (loggingEnabledObject == null) {
            return DENY;
        }

        boolean loggingEnabled = (Boolean)loggingEnabledObject;
        /*System.out.println("LOGGING " + (loggingEnabled ? "" : "NOT ")
                + "ALLOWED FOR USER: " + le.getMDC("username"));*/
        if (loggingEnabled) {
            return ACCEPT;
        } else {
            return DENY;
        }
    }
}

LoggerFilter.java

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.log4j.MDC;

import com.company.project.package.bean.User;

/**
 *
 * Filter for the logger, which sets the required information used to log
 * messages into the database..
 *
 * @author Amit
 *
 */
public class LoggerFilter implements Filter {

    @Override
    public void destroy() {
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response,
            FilterChain chain) throws IOException, ServletException {
        HttpSession session = ((HttpServletRequest)request).getSession(false);
        if (session != null) {
            Object object = session.getAttribute("USER");
            if (object instanceof User) {
                User user = (User)object;

                if (user != null) {
                    String username = user.getUsername() != null ? user
                            .getUsername() : "";
                    MDC.put("username", username);
                    MDC.put("loggingEnabled", true);
                }
            }
        }
        chain.doFilter(request, response);

        MDC.remove("username");
        MDC.remove("loggingEnabled");
    }

    @Override
    public void init(FilterConfig arg0) throws ServletException {
    }
}

Note: LoggerFilter is a normal servlet, could be placed inside web.xml or spring-applicationSecurity.xml.