How to handle null when setting date in log4j2 jdbcappender?

Question

When I set null in the date column, I get the value "1900-01-01 00: 00: 00 000" in my table, and I expect NULL in this column. Since it handles correctly 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> 

Column type startdate and enddate 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), ""); 

Same thing for String with a null value.

Answer 1

Null insert

I don’t think it is possible to get null in any column of any type using JDBCAppender with such patterns.

This thread appeared a few years ago, and then this question is Jira . None of this seemed to attract attention.

The short option is that a StringBuilder used between the template and the prepared statement, and therefore, INSERT always gets a non-empty string, even if (in your example) null is a value in ThreadContext . Thus, you can insert an empty string or the string "null" , but it is not possible to insert a null value.

January 1, 1900

When I run your example against the MySQL database, the insert crashes in front, because MySQL does not know how to build a DATETIME from an empty string.

I assume that you are using SQL Server, which will happily turn an empty string into 1900-01-01 00:00:00.000 , because of course this is what you had in mind.

Regardless of the database used, JDBCAppender is about to populate the INSERT non-empty strings. What comes from there will differ from DB to DB.

Why?

It may seem strange at first that the structure changes your value ( null ) to another value (empty string). However, in essence, logging is line-by-line.

Yes, there is an application that will write these lines to the database. And yes, your database can in turn coax these strings into types of type DATETIME or INTEGER . But to any given application all this really concerns manipulation and output of lines.

Possible workaround

If you really want to get null in the database using JDBCAppender , you can write a trigger .

Final observation

Not sure if this helps, but I can also present all my findings:

Using ColumnMapping instead of Column in your ColumnMapping configuration, you can specify the class name. The presence (or absence) and value of this class name changes 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 call the current date and time when startdate is null, not 1900-01-01. Using java.sql.Date instead will write the current date without time.