Log4JProjectPages/JDBCAppenderConfiguration

This xml configuration example was submitted by Rich Bagley (thank you!), and is compatible with v1.2.7. A version specific to your environment will vary. Please see the JDBCAppender documentation.

<?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="org.apache.log4j.jdbc.JDBCAppender"> 
        <param name="URL" value="jdbc:oracle:thin:@sd1.hbs.edu:1521:sc1" /> 
        <param name="Driver" value="oracle.jdbc.driver.OracleDriver" /> 
        <param name="User" value="user" /> 
        <param name="Password" value="password" /> 
        <layout class="org.apache.log4j.PatternLayout"> 
            <param name="ConversionPattern" 
              value="INSERT INTO LOGGING_SAMPLES_TEST (log_date, log_level, location, message) VALUES ( '%d{ISO8601}','%p', '%C;%L', '%m' )" 
            /> 
        </layout> 
    </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="logging.simple.jdbcLogger"> 
        <level value="debug"/> 
        <appender-ref ref="jdbcAppender"/> 
    </logger> 

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

</log4j:configuration>

• }}}

Here's a sample configuration excerpted from my log4j.properties file. The table VT_LOGGER_LOG is my own custom table. The log4j.properties file uses ant-like properties variables, which are in ${}. If you don't use those, just hard-code the variable values.

 1. Ant-like property variables set in build.properties:
db.login=dev_usr

 1. for tomcat, include the following line and put log4j.properties in [webapp]/web-inf/classes
log4j.debug=true
 1. set rootLogger level and appenders to use
log4j.rootLogger=INFO,stdout,db

 1. database appender
log4j.appender.db=org.apache.log4j.jdbc.JDBCAppender
 1. Using a commercial jdbc driver for ms slq server; use the URL of your driver instead
log4j.appender.db.URL=jdbc:inetdae7a:developer.internal.ourcompany.com:1433?sql7=true&appname=${db.login}&database=${db.login}
log4j.appender.db.user=${db.login}
log4j.appender.db.password=4OL32c7H
log4j.appender.db.sql=INSERT INTO VT_LOGGER_LOG (log_level, class, method, msg) values ('%p', '%C', '%M', '%m')
log4j.appender.db.layout=org.apache.log4j.PatternLayout
 

Note: While this configuration works beautifully for me, there is a catch:

The conversion pattern works beautifully, after the database is set up log entries are automagically inserted, as expected.

However: If you try to log anything containing one or more ' (single quote) characters, you get a JDBC exception.

This is because, apparently, the JDBCAppender builds an SQL String directly from your log statements, using the conversion pattern, and then sends this to the database. If the log message contains a single quote, the resulting SQL String is malformed, leading to an exception, and the message never appears in the Log. Unless you want to handle the JDBCAppender code yourself, the way around this is to replace all occurrences of single quotes with double quotes in the log message before send it out.

Programmers Note:

This could be fixed pretty easily in the JDBCAppender by using a PreparedStatement instead of a normal Statement, and processing the conversion pattern first to replace the %codes with :x SQL Statement parameters.

The book "Logging in Java with the JDK 1.4 Logging API and Apache log4j" by Samudra Gupta contains code for this, although note that he forgets to call clearParameters() on his PreparedStatement. (The book has plenty of other problems, like not telling you to put debug=true in your log4j.properties file to get log4j to work under Tomcat).

Richard Unger - richard.unger <AT> telekom.at