Geek Answers Handbook

How to clear log messages in Log4j to save them in a database

I am trying to save log messages in a central database. To do this, I configured the following Appender configuration in log4j xml:

<appender name="DB" class="org.apache.log4j.jdbc.JDBCAppender"> <param name="URL" value="jdbc:postgresql://localhost/logging_test" /> <param name="user" value="test_user" /> <param name="password" value="test_password" /> <param name="sql" value="INSERT INTO log_messages ( log_level, message, log_date ) VALUES ( '%p', '%m', '%d{yyyy-MM-dd HH:mm:ss}' )" /> </appender>

This works fine, except for some messages contain ', and then the application does not work.

Is there an easy way to do this?

+4
source share
9 answers

Take a look at this unofficial Log4J JDBCAppender , which fixes this problem and is distributed under the Apache 2.0 license. Quoting its functions compared to org.apache.log4j.jdbc.JDBCAppender :

  • Entering a (relational) database
  • Flexible connection processing (does not support DataSource yet)
  • Flexible sql commands to perform actual logging
  • Supported statements and stored procedures (J2SDK 1.4+) are supported
  • Enables message logging with special characters such as' (single quote) and, (comma)
  • Flexible table and column structure
  • Flexible ID Creation
  • Several PatternLayout applications are allowed; in one or more columns
  • Support for J2SDK 1.3, 1.4 and 1.5
  • Supports Log4j 1.2.9 and ongoing development

Or, and you should seriously consider this option, switch from log4j to its successor, logback (this happens somewhere) that has a DBAppender that uses PreparedStatement (see sources ) that can use the JNDI data source, pooling (it's big plus) etc. For more information about this application, see the online manual http://logback.qos.ch/manual/appenders.html#DBAppender

+3
source

I would suggest creating a custom appender and overriding the flushBuffer and execute methods, where you can escape your lines or use PreparedStatement :

 public class MyJDBCAppender extends JDBCAppender { }

To explain why you need to override flushBuffer , the appender places LogEvent objects in a buffer that is later cleared of the target (in this case, the database). Here the flushBuffer method uses getLogStatement and (via execute ) a normal Statement . You can completely replace this behavior. View current source code

Then register your appender istead JDBCAppender .

+6
source

I am not familiar with log4j or JDBC, but I know that JDBC supports prepared statements. There may be a way to use this with JDBCAppender

+3
source

I decided the following:

I copied the source code of JDBCAppender called ACMEJDBCAppender

override the getLogStatement(LoggingEvent event) method, cloning the old event and providing a new message with a shielded message.

Not the cleanest solution from an oop point of view, but it does the job. Hope this helps.

 protected String getLogStatement(LoggingEvent event) { LoggingEvent clone = new LoggingEvent( event.fqnOfCategoryClass, LogManager.getLogger(event.getLoggerName()), event.getLevel(), AidaUtils.sqlEscape(event.getMessage().toString()), event.getThrowableInformation()!=null ? event.getThrowableInformation().getThrowable() : null ); return getLayout().format(clone); }
+3
source

According to Javadocs, the official JDBCAppender is rather limited and, in particular, does not have a good way to deal with this problem.

One way is to use an alternative appender, for example this one , which should be interoperable with Log4J, except, you know, work.

+1
source

To work around this problem when registering with Oracle, you can use the Oracle quote operator.

Wrap the quote operator around% m (i.e. q # '% m' #)

For instance:

 INSERT INTO log_messages ( log_level, message, log_date ) VALUES ( '%p', q#'%m'#, '%d{yyyy-MM-dd HH:mm:ss}' )
+1
source

Joao, sorry for being late, but here it is:

 <appender name="DB" class="org.apache.log4j.db.DBAppender"> <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource"> <param name="driverClass" value="org.postgresql.Driver" /> <param name="url" value="jdbc:postgresql://localhost/database" /> <param name="user" value="user" /> <param name="password" value="password" /> </connectionSource> <layout class="org.apache.log4j.PatternLayout"> <param name="ConversionPattern" value="%d %-5p [%t] %c - %m%n" /> </layout> </appender>

Hope this helps!

0
source

If you are using a SQL server, you can use the following

 SET QUOTED_IDENTIFIER OFF; INSERT INTO log_messages ( log_level, message, log_date ) VALUES ( "%p", ":%m", "%d{yyyy-MM-dd HH:mm:ss}" )' SET QUOTED_IDENTIFIER OFF;

Which puts the problem in double quotes. If you don't have double quotes in the messages, this may fix the problem.

0
source

For postgresql use $$

Example: $$% m $$

0
source

More articles:


All Articles