How to handle null while setting date in log4j2 jdbcappender?

Question

When I set null value in the date column I get "1900-01-01 00:00:00.000" value in my table and I am expecting NULL in that column. As this is handled properly in jdbc if I put like this

preparedStatement.setBindParam(Types.TIMESTAMP, 12, startdate);

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="debug">

    <CustomLevels>
        <CustomLevel name="TESTLOG" intLevel="552" />
    </CustomLevels>

    <Appenders>

        <JDBC name="myAppender" tableName="MYTABLE">
            <DataSource jndiName="java:MyDataSource" />
            <Column name="ID" pattern="%X{ID}" isUnicode="false"/>
            <Column name="startdate" pattern="%X{startdate}" isUnicode="false"/>
            <Column name="enddate" pattern="%X{enddate}" isUnicode="false"/>
        </JDBC>

    </Appenders>

    <Loggers>
        <Root level="trace"  includeLocation="false">
            <AppenderRef ref="myAppender" level="TESTLOG" />
        </Root>
    </Loggers>

</Configuration>

Datatype of columns startdate and enddate is datetime

ThreadContext.put("startdate", startdate != null ? startdate.toString() : null);
ThreadContext.put("enddate", a_reportEndDate != null ? enddate.toString() : null);
final Logger LOGGER = LogManager.getLogger();
LOGGER.log(Level.forName("TESTLOG", 552), "");

The same is for String with null value.

Answer 1

Inserting null

I don't think it's possible to get null into any column of any type using JDBCAppender with patterns like this.

This thread popped up a couple of years ago, followed by this Jira issue. None of it seems to have gotten any attention.

The short version is that a StringBuilder is used between pattern and prepared statement, and thus the INSERT always gets a non-null string, even if (in your example) null is the value in the ThreadContext. So it's possible to insert an empty string, or the string "null", but it doesn't appear to be possible to insert the value null.

January 1, 1900

When I run your example against a MySQL database, the insert fails up front, because MySQL doesn't know how to construct a DATETIME from an empty string.

I'm guessing you're using SQL Server, which will happily turn an empty string into 1900-01-01 00:00:00.000, because of course that's what you meant.

Regardless of the database you use, JDBCAppender is going to fill the INSERT statement with non-null strings. What happens from there will vary from DB to DB.

Why?

At first it may seem strange that the framework is changing your value (null) to another value (empty string). However, at its core, logging is about strings of text.

Yes, there's an appender that will write these strings to a database. And yes, your database can in turn coax those strings into types like DATETIME or INTEGER. But to any given appender, it's all really just about manipulating and outputting strings.

Possible work-around

If you really want to get null into the database using JDBCAppender, you may be able to write a trigger.

Final Observation

Not sure if this helps any, but I may as well present all my findings:

Using ColumnMapping instead of Column in your log4j config allows you to specify a class name. The presence (or absence) and value of this class name does change the value that is inserted into the database. For example, using SQL Server Express 2014:

<ColumnMapping name="startdate" pattern="%X{startdate}" type="java.sql.Timestamp"/>

Will cause the current date and time to be logged when startdate is null, instead of 1900-01-01. Using java.sql.Date instead will log the current date without time.