log4j2 + slf4j configuration for JDBC connection

Question

I'm new to log4j and I'm having a hard time getting started. I have decided to use log4j2 in my project and started by replacing Apache Shiro's logging implementation from JCL to log4j2.

I managed to get it to work perfectly on Console, but not on JDBC. The database connection works properly but only nulls are inserted. Literal values work, but not patterns.

pom.xml

<!-- Logging API + implementation: -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.12</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-slf4j-impl</artifactId>
        <version>2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-api</artifactId>
        <version>2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.logging.log4j</groupId>
        <artifactId>log4j-core</artifactId>
        <version>2.3</version>
    </dependency>

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="TRACE" monitorInterval="30">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
        </Console>
        <JDBC name="databaseAppender" tableName="LOG">
            <ConnectionFactory class="my.logging.LogConnectionFactory"
                method="getDatabaseConnection" />
            <!-- <Column name="id" literal="LOGGING.APPLICATION_LOG_SEQUENCE.NEXTVAL" /> -->
            <Column name="DT_CRET" isEventTimestamp="true" />
            <Column name="VL_HEADER" pattern="%c" />
            <Column name="VL_LEVEL" pattern="%l" />
            <Column name="VL_MSG" pattern="%m" />
            <Column name="NM_USER" literal="'cosme'" />
            <!-- <Column name="NM_USER" pattern="%X{username}" />-->
        </JDBC>
    </Appenders>
    <Loggers>
        <Root level="INFO">
            <AppenderRef ref="Console" level="INFO" />
            <AppenderRef ref="databaseAppender" level="INFO" />
        </Root>
    </Loggers>
</Configuration>

LogConnectionFactory.java

package my.logging;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDataSource;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;

import my.config.ConfigurationManager;


public class LogConnectionFactory {

    private static interface Singleton {
        final LogConnectionFactory INSTANCE = new LogConnectionFactory();
    }

    private final DataSource dataSource;

    private LogConnectionFactory() {
        try {
            Class.forName(ConfigurationManager.getValue("mtdt_db.connectionDriver")).newInstance();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        this.dataSource = setupDataSource(ConfigurationManager.getValue("mtdt_db.connectionString"));
    }

    public static Connection getDatabaseConnection() throws SQLException {
        return Singleton.INSTANCE.dataSource.getConnection();
    }

    public static DataSource setupDataSource(String connectURI) {
        //
        // First, we'll create a ConnectionFactory that the
        // pool will use to create Connections.
        // We'll use the DriverManagerConnectionFactory,
        // using the connect string passed in the command line
        // arguments.
        //
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);

        //
        // Next we'll create the PoolableConnectionFactory, which wraps
        // the "real" Connections created by the ConnectionFactory with
        // the classes that implement the pooling functionality.
        //
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);

        //
        // Now we'll need a ObjectPool that serves as the
        // actual pool of connections.
        //
        // We'll use a GenericObjectPool instance, although
        // any ObjectPool implementation will suffice.
        //
        ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);

        // Set the factory's pool property to the owning pool
        poolableConnectionFactory.setPool(connectionPool);

        //
        // Finally, we create the PoolingDriver itself,
        // passing in the object pool we created.
        //
        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);

        return dataSource;
    }
}

Database output:

Console output (seems correct):

Answer 1

I got it to work by using isUnicode="false" on the columns...

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="TRACE" monitorInterval="30">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
        </Console>
        <JDBC name="databaseAppender" tableName="LOG">
            <ConnectionFactory class="pt.mapidea.logging.LogConnectionFactory"
                method="getDatabaseConnection" />
            <!-- <Column name="id" literal="LOGGING.APPLICATION_LOG_SEQUENCE.NEXTVAL" /> -->
            <Column name="DT_CRET" isEventTimestamp="true" />
            <Column name="VL_HEADER" pattern="%c" isUnicode="false" />
            <Column name="VL_LEVEL" pattern="%l" isUnicode="false" />
            <Column name="VL_MSG" pattern="%m" isUnicode="false" />
            <Column name="NM_USER" literal="'cosme'" />
            <!-- <Column name="NM_USER" pattern="%X{username}" />-->
        </JDBC>
    </Appenders>
    <Loggers>
        <Root level="INFO">
            <AppenderRef ref="Console" level="INFO" />
            <AppenderRef ref="databaseAppender" level="INFO" />
        </Root>
    </Loggers>
</Configuration>