Comparing SQL Logging with Log4j – Default vs. JdbcPlus

I was recently playing with logging to jdbc via log4j when I discovered that it isn’t a fully elegant solution. It is fairly raw JDBC, or so it seems. I found this post on stackoverflow that described the same problem I was having. One of the solutions is to use the JDBCPlus appender.

Here is a quick comparison:

log4j.rootLogger=INFO, A1, jdbcPlusJdbcAppender, log4jJdbcAppender

# A1 is set to be a ConsoleAppender.
log4j.appender.A1=org.apache.log4j.ConsoleAppender

# A1 uses PatternLayout.
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

log4j.appender.jdbcPlusJdbcAppender=org.apache.log4j.jdbcplus.JDBCAppender
log4j.appender.jdbcPlusJdbcAppender.url=jdbc:jtds:sqlserver://serverName:1433/databaseName
log4j.appender.jdbcPlusJdbcAppender.dbclass=net.sourceforge.jtds.jdbc.Driver
log4j.appender.jdbcPlusJdbcAppender.username=userName
log4j.appender.jdbcPlusJdbcAppender.password=password
log4j.appender.jdbcPlusJdbcAppender.layout=org.apache.log4j.PatternLayout
log4j.appender.jdbcPlusJdbcAppender.sql=INSERT INTO LOG (log_date, log_level, location, message) VALUES ( '@TIMESTAMP@', '@PRIO@', '@CAT@', '@MSG@' )

log4j.appender.log4jJdbcAppender=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.log4jJdbcAppender.URL=jdbc:jtds:sqlserver://serverName:1433/databaseName
log4j.appender.log4jJdbcAppender.Driver=net.sourceforge.jtds.jdbc.Driver
log4j.appender.log4jJdbcAppender.User=userName
log4j.appender.log4jJdbcAppender.Password=password
log4j.appender.log4jJdbcAppender.layout=org.apache.log4j.PatternLayout
log4j.appender.log4jJdbcAppender.layout.ConversionPattern=INSERT INTO LOG (log_date, log_level, location, message) VALUES ( '%d{yyyy-MMM-dd HH:mm:ss.SSS}','%p', 'Class: %c', '%m')

A couple of obvious points:

1. This example is based on the JTDS driver. This means you need JTDS on your path. It also means that if you use another driver, you need to change these settings to match the requirements of your alternate driver.
2. Both appenders are attached to the root logger. This is just to show both in action at the same time.

Here are the differences that I have noticed:

1. The default JDBC appender uses a conversion pattern to set up it’s insert statement. The conversion params are just standard values used which you can find here. JDBCPlus, on the other hand uses some magic values that are defined for you. I had to look over their examples carefully to figure out which was which.
2. The properties themselves are slightly different. If you’re converting from one to the other pay attention to things such as Driver\dbclass, URL\url. It is case sensitive.