Custom database logging with Jboss AS 6

I needed to persist some part of my application logs (deployed on jboss AS 6) into the oracle database. I started from looking into jboss-logging.xml file. There is much commented example code, using JMS, sending emails, file loggers and others, but unfortunately nothing about logging into db. Not a problem, I thought, just need to check jboss documentation. I was very surprised but on the jboss.org is nothing, no documentation, zero. For AS 5 or 7 - no problem, documentation is available but not AS 6 – unbelievable. Next I searched in the internet. Found not much, only e few posts on forums, few articles of log4j logging but with no Jboss AS 6 syntax which is different than previous versions and typical log4j configuration xml file. So, I wanted to share my result, It was not so easy than I excepted, so it could be usefull. First you need to add log4j-appender declaration

   <log4j-appender name="DEV_JDBC" 
  class="org.apache.log4j.jdbc.JDBCAppender">
      <error-manager>
         <only-once/>
      </error-manager>
      <level name="INFO"/>
      <properties>
         <property name="driver">
  oracle.jdbc.driver.OracleDriver
   </property>
         <property name="URL">
  jdbc:oracle:thin:@mydbhost.gkolpu.com:1521:dev
   </property>
         <property name="user">DEV</property>
         <property name="password">DEV</property>
         <property name="sql">
  insert into logging_table(id, tmstmp,message) 
  values(sequence.nextval,now(),'%m')
   </property>
         <property name="bufferSize">100</property>
      </properties>

   </log4j-appender>

And use it in logger

<logger category="org.gkolpu.logging.db" use-parent-handlers="false">
       <level name="INFO"/>
       <handlers>
         <handler-ref name="DEV_JDBC"/>
       </handlers>
     </logger>

Actually my goal was, calling stored procedure to save the log in db, by I couldn’t use JDBCAppender for that, it was not working. To fix this I coded my own appender, actually only extended JDBCAppender. Original code used Statement class to execute insert operation. This is java code (jar with it complied must be putted to jboss libs).

package org.gkolpu;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.log4j.jdbc.JDBCAppender;

public class CallableJDBCAppender extends JDBCAppender {
 @Override
 protected void execute(String sql) throws SQLException {

  Connection con = null;
  CallableStatement call = null;

  try {
   con = getConnection();
   call = con.prepareCall(sql);
   call.execute();
  } finally {
   if (call != null) {
    call.close();
   }
   closeConnection(con);
  }
 }
}

And a few changes into the appender declaration.

<log4j-appender name="DEV_JDBC" 
 class="org.gkolpu.CallableJDBCAppender">
      <error-manager>
         <only-once/>
      </error-manager>
      <level name="INFO"/>
      <properties>
         <property name="driver">
  oracle.jdbc.driver.OracleDriver
   </property>
         <property name="URL">
  jdbc:oracle:thin:@mydbhost.gkolpu.com:1521:dev
   </property>
         <property name="user">DEV</property>
         <property name="password">DEV</property>
         <property name="sql">
  {call MY_PACKAGE.log_message('%m')}
   </property>
         <property name="bufferSize">100</property>
      </properties>

   </log4j-appender>

;