JDBCAppender

[Kevin Steppe <ks...@pacbell.net>]

Ceki,
    I finally tested and debugged the new JDBCAppender.
This should now be ready for inclusion.   I've attached both
the source file and an example config file.  Code follows:
-------------------------------------------------------------------------------------



package org.apache.log4j;

import org.apache.log4j.*;
import org.apache.log4j.spi.*;
import org.apache.log4j.PatternLayout;
import org.apache.log4j.helpers.OptionConverter;

import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;


/**
 * The JDBCAppender provides for sendinhg log messages to a
database.
 *
 * Each append call adds to an ArrayList buffer.  When the
buffer is filled
 * each log event is placed in a sql statement
(configurable) and executed.
 *
 * BufferSize, db URL, User, & Password are configurable
options in
 * the standard Log4J ways.
 *
 * The setSql(String sql) sets the SQL statement to be used
for logging --
 * this statement is sent to a PatternLayout (either created
automaticly
 * by the appender or added by the user).  Therefore by
default all the
 * conversion patterns in PatternLayout can be used inside
of the statement.
 * (see the test cases for examples)
 *
 * Overriding the getLogStatement method allows more
explicit control of the
 * statement used for logging.
 *
 * For use as a base class:
 *
 *    Override getConnection() to pass any connection you
want.
 *       Typically this is used to enable application wide
connection pooling.
 *    Override closeConnection(Connection con) -- if you
override getConnection
 *       make sure to implement closeConnection to handle
the connection you
 *       generated.  Typically this would return the
connection to the pool it
 *       came from.
 *
 *    Override getLogStatement(LoggingEvent event) to
produce specialized or
 *       dynamic statements The default uses the sql option
value
 *
 * @author: Kevin Steppe (<A
HREF="mailto:ksteppe@pacbell.net">ksteppe@pacbell.net</A>)
*/


public class JDBCAppender extends
org.apache.log4j.AppenderSkeleton
    implements org.apache.log4j.Appender {

  /**
   * URL of the DB for default connection handling
   */
  protected String databaseURL = "jdbc:odbc:myDB";

  /**
   * User to connect as for default connection handling
   */
  protected String databaseUser = "me";

  /**
   * User to use for default connection handling
   */
  protected String databasePassword = "mypassword";

  /**
   * Connection used by default.  The connection is opened
the first time it
   * is needed and then held open until the appender is
closed (usually at
   * garbage collection).  This behavior is best modified by
creating a
   * sub-class and overriding the <code>getConnection</code>
and
   * <code>closeConnection</code> methods.
   */
  protected Connection connection = null;

  /**
   * Stores the string given to the pattern layout for
conversion into a SQL
   * statement, eg: insert into LogTable (Thread, Class,
Message) values
   * ("%t", "%c", "%m")
   *
   * Be careful of quotes in your messages!
   *
   * Also see PatternLayout.
   */
  protected String sqlStatement = "";

  /**
   * size of LoggingEvent buffer before writting to the
database.
   * Default is 1.
   */
  protected int bufferSize = 1;

  /**
   * ArrayList holding the buffer of Logging Events.
   */
  protected ArrayList buffer;

  /**
   * Helper object for clearing out the buffer
   */
  protected ArrayList removes;

  public JDBCAppender() {
    super();
    buffer = new ArrayList(bufferSize);
    removes = new ArrayList(bufferSize);
  }

  /**
   * Adds the event to the buffer.  When full the buffer is
flushed.
   */
  public void append(LoggingEvent event) {
    buffer.add(event);

    if (buffer.size() >= bufferSize)
      flushBuffer();
  }

  /**
   * By default getLogStatement sends the event to the
required Layout object.
   * The layout will format the given pattern into a
workable SQL string.
   *
   * Overriding this provides direct access to the
LoggingEvent
   * when constructing the logging statement.
   *
   */
  protected String getLogStatement(LoggingEvent event) {
    return getLayout().format(event);
  }

  /**
   *
   * Override this to provide an alertnate method of getting

   * connections (such as caching).  One method to fix this
is to open
   * connections at the start of flushBuffer() and close
them at the
   * end.  I use a connection pool outside of JDBCAppender
which is
   * accessed in an override of this method.
   * */
  protected void execute(String sql) throws SQLException {

    Connection con = null;
    Statement stmt = null;

    try {
        con = getConnection();

        stmt = con.createStatement();
        stmt.executeUpdate(sql);
    } catch (SQLException e) {
       if (stmt != null)
      stmt.close();
       throw e;
    }
    stmt.close();
    closeConnection(con);

    System.out.println("Execute: " + sql);
  }


  /**
   * Override this to return the connection to a pool, or to
clean up the
   * resource.
   *
   * The default behavior holds a single connection open
until the appender
   * is closed (typically when garbage collected).
   */
  protected void closeConnection(Connection con) {
  }

  /**
   * Override this to link with your connection pooling
system.
   *
   * By default this creates a single connection which is
held open
   * until the object is garbage collected.
   */
  protected Connection getConnection() throws SQLException {

      if (!DriverManager.getDrivers().hasMoreElements())
      setDriver("sun.jdbc.odbc.JdbcOdbcDriver");

      if (connection == null) {
        connection =
DriverManager.getConnection(databaseURL, databaseUser,
     databasePassword);
      }

      return connection;
  }

  /**
   * Closes the appender, flushing the buffer first then
closing the default
   * connection if it is open.
   */
  public void close()
  {
    flushBuffer();

    try {
      if (connection != null && !connection.isClosed())
          connection.close();
    } catch (SQLException e) {
        errorHandler.error("Error closing connection", e,
ErrorCode.GENERIC_FAILURE);
    }
    this.closed = true;
  }

  /**
   * loops through the buffer of LoggingEvents, gets a
   * sql string from getLogStatement() and sends it to
execute().
   * Errors are sent to the errorHandler.
   *
   * If a statement fails the LoggingEvent stays in the
buffer!
   */
  public void flushBuffer() {
    //Do the actual logging
    removes.ensureCapacity(buffer.size());
    for (Iterator i = buffer.iterator(); i.hasNext();) {
      try {
        LoggingEvent logEvent = (LoggingEvent)i.next();
     String sql = getLogStatement(logEvent);
     execute(sql);
        removes.add(logEvent);
      }
      catch (SQLException e) {
     errorHandler.error("Failed to excute sql", e,
      ErrorCode.FLUSH_FAILURE);
      }
    }
    buffer.removeAll(removes);
    //buffer.clear();
  }


  /** closes the appender before disposal */
  public void finalize() {
    close();
  }


  /**
   * JDBCAppender builds a layout internally if one is not
provided.
   */
  public boolean requiresLayout() {
    return false;
  }


  /**
   *
   */
  public void setSql(String s) {
    sqlStatement = s;
    if (getLayout() == null) {
        this.setLayout(new PatternLayout(s));
    }
    else {

((PatternLayout)getLayout()).setConversionPattern(s);
    }
  }


  /**
   * Returns pre-formated statement eg: insert into LogTable
(msg) values ("%m")
   */
  public String getSql() {
    return sqlStatement;
  }


  public void setUser(String user) {
    databaseUser = user;
  }


  public void setURL(String url) {
    databaseURL = url;
  }


  public void setPassword(String password) {
    databasePassword = password;
  }


  public void setBufferSize(int newBufferSize) {
    bufferSize = newBufferSize;
    buffer.ensureCapacity(bufferSize);
    removes.ensureCapacity(bufferSize);
  }


  public String getUser() {
    return databaseUser;
  }


  public String getURL() {
    return databaseURL;
  }


  public String getPassword() {
    return databasePassword;
  }


  public int getBufferSize() {
    return bufferSize;
  }


  /**
   * Ensures that the given driver class has been loaded for
sql connection
   * creation.
   */
  public void setDriver(String driverClass) {
    try {
      Class.forName(driverClass);
    } catch (Exception e) {
      errorHandler.error("Failed to load driver", e,
    ErrorCode.GENERIC_FAILURE);
    }
  }

}

Re: JDBCAppender

[Ceki Gülcü <ce...@qos.ch>]

Thanks Kevin. I just committed under the jdbc package.

At 11:31 25.03.2002 -0800, you wrote:
>Ceki,
>     I finally tested and debugged the new JDBCAppender.
>This should now be ready for inclusion.   I've attached both
>the source file and an example config file.  Code follows:
>-------------------------------------------------------------------------------------
>
>
>
>package org.apache.log4j;
>
>import org.apache.log4j.*;
>import org.apache.log4j.spi.*;
>import org.apache.log4j.PatternLayout;
>import org.apache.log4j.helpers.OptionConverter;
>
>import java.util.List;
>import java.util.ArrayList;
>import java.util.Iterator;
>
>import java.sql.DriverManager;
>import java.sql.Connection;
>import java.sql.Statement;
>import java.sql.SQLException;
>
>
>/**
>  * The JDBCAppender provides for sendinhg log messages to a
>database.
>  *
>  * Each append call adds to an ArrayList buffer.  When the
>buffer is filled
>  * each log event is placed in a sql statement
>(configurable) and executed.
>  *
>  * BufferSize, db URL, User, & Password are configurable
>options in
>  * the standard Log4J ways.
>  *
>  * The setSql(String sql) sets the SQL statement to be used
>for logging --
>  * this statement is sent to a PatternLayout (either created
>automaticly
>  * by the appender or added by the user).  Therefore by
>default all the
>  * conversion patterns in PatternLayout can be used inside
>of the statement.
>  * (see the test cases for examples)
>  *
>  * Overriding the getLogStatement method allows more
>explicit control of the
>  * statement used for logging.
>  *
>  * For use as a base class:
>  *
>  *    Override getConnection() to pass any connection you
>want.
>  *       Typically this is used to enable application wide
>connection pooling.
>  *    Override closeConnection(Connection con) -- if you
>override getConnection
>  *       make sure to implement closeConnection to handle
>the connection you
>  *       generated.  Typically this would return the
>connection to the pool it
>  *       came from.
>  *
>  *    Override getLogStatement(LoggingEvent event) to
>produce specialized or
>  *       dynamic statements The default uses the sql option
>value
>  *
>  * @author: Kevin Steppe (<A
>HREF="mailto:ksteppe@pacbell.net">ksteppe@pacbell.net</A>)
>*/
>
>
>public class JDBCAppender extends
>org.apache.log4j.AppenderSkeleton
>     implements org.apache.log4j.Appender {
>
>   /**
>    * URL of the DB for default connection handling
>    */
>   protected String databaseURL = "jdbc:odbc:myDB";
>
>   /**
>    * User to connect as for default connection handling
>    */
>   protected String databaseUser = "me";
>
>   /**
>    * User to use for default connection handling
>    */
>   protected String databasePassword = "mypassword";
>
>   /**
>    * Connection used by default.  The connection is opened
>the first time it
>    * is needed and then held open until the appender is
>closed (usually at
>    * garbage collection).  This behavior is best modified by
>creating a
>    * sub-class and overriding the <code>getConnection</code>
>and
>    * <code>closeConnection</code> methods.
>    */
>   protected Connection connection = null;
>
>   /**
>    * Stores the string given to the pattern layout for
>conversion into a SQL
>    * statement, eg: insert into LogTable (Thread, Class,
>Message) values
>    * ("%t", "%c", "%m")
>    *
>    * Be careful of quotes in your messages!
>    *
>    * Also see PatternLayout.
>    */
>   protected String sqlStatement = "";
>
>   /**
>    * size of LoggingEvent buffer before writting to the
>database.
>    * Default is 1.
>    */
>   protected int bufferSize = 1;
>
>   /**
>    * ArrayList holding the buffer of Logging Events.
>    */
>   protected ArrayList buffer;
>
>   /**
>    * Helper object for clearing out the buffer
>    */
>   protected ArrayList removes;
>
>   public JDBCAppender() {
>     super();
>     buffer = new ArrayList(bufferSize);
>     removes = new ArrayList(bufferSize);
>   }
>
>   /**
>    * Adds the event to the buffer.  When full the buffer is
>flushed.
>    */
>   public void append(LoggingEvent event) {
>     buffer.add(event);
>
>     if (buffer.size() >= bufferSize)
>       flushBuffer();
>   }
>
>   /**
>    * By default getLogStatement sends the event to the
>required Layout object.
>    * The layout will format the given pattern into a
>workable SQL string.
>    *
>    * Overriding this provides direct access to the
>LoggingEvent
>    * when constructing the logging statement.
>    *
>    */
>   protected String getLogStatement(LoggingEvent event) {
>     return getLayout().format(event);
>   }
>
>   /**
>    *
>    * Override this to provide an alertnate method of getting
>
>    * connections (such as caching).  One method to fix this
>is to open
>    * connections at the start of flushBuffer() and close
>them at the
>    * end.  I use a connection pool outside of JDBCAppender
>which is
>    * accessed in an override of this method.
>    * */
>   protected void execute(String sql) throws SQLException {
>
>     Connection con = null;
>     Statement stmt = null;
>
>     try {
>         con = getConnection();
>
>         stmt = con.createStatement();
>         stmt.executeUpdate(sql);
>     } catch (SQLException e) {
>        if (stmt != null)
>       stmt.close();
>        throw e;
>     }
>     stmt.close();
>     closeConnection(con);
>
>     System.out.println("Execute: " + sql);
>   }
>
>
>   /**
>    * Override this to return the connection to a pool, or to
>clean up the
>    * resource.
>    *
>    * The default behavior holds a single connection open
>until the appender
>    * is closed (typically when garbage collected).
>    */
>   protected void closeConnection(Connection con) {
>   }
>
>   /**
>    * Override this to link with your connection pooling
>system.
>    *
>    * By default this creates a single connection which is
>held open
>    * until the object is garbage collected.
>    */
>   protected Connection getConnection() throws SQLException {
>
>       if (!DriverManager.getDrivers().hasMoreElements())
>       setDriver("sun.jdbc.odbc.JdbcOdbcDriver");
>
>       if (connection == null) {
>         connection =
>DriverManager.getConnection(databaseURL, databaseUser,
>      databasePassword);
>       }
>
>       return connection;
>   }
>
>   /**
>    * Closes the appender, flushing the buffer first then
>closing the default
>    * connection if it is open.
>    */
>   public void close()
>   {
>     flushBuffer();
>
>     try {
>       if (connection != null && !connection.isClosed())
>           connection.close();
>     } catch (SQLException e) {
>         errorHandler.error("Error closing connection", e,
>ErrorCode.GENERIC_FAILURE);
>     }
>     this.closed = true;
>   }
>
>   /**
>    * loops through the buffer of LoggingEvents, gets a
>    * sql string from getLogStatement() and sends it to
>execute().
>    * Errors are sent to the errorHandler.
>    *
>    * If a statement fails the LoggingEvent stays in the
>buffer!
>    */
>   public void flushBuffer() {
>     //Do the actual logging
>     removes.ensureCapacity(buffer.size());
>     for (Iterator i = buffer.iterator(); i.hasNext();) {
>       try {
>         LoggingEvent logEvent = (LoggingEvent)i.next();
>      String sql = getLogStatement(logEvent);
>      execute(sql);
>         removes.add(logEvent);
>       }
>       catch (SQLException e) {
>      errorHandler.error("Failed to excute sql", e,
>       ErrorCode.FLUSH_FAILURE);
>       }
>     }
>     buffer.removeAll(removes);
>     //buffer.clear();
>   }
>
>
>   /** closes the appender before disposal */
>   public void finalize() {
>     close();
>   }
>
>
>   /**
>    * JDBCAppender builds a layout internally if one is not
>provided.
>    */
>   public boolean requiresLayout() {
>     return false;
>   }
>
>
>   /**
>    *
>    */
>   public void setSql(String s) {
>     sqlStatement = s;
>     if (getLayout() == null) {
>         this.setLayout(new PatternLayout(s));
>     }
>     else {
>
>((PatternLayout)getLayout()).setConversionPattern(s);
>     }
>   }
>
>
>   /**
>    * Returns pre-formated statement eg: insert into LogTable
>(msg) values ("%m")
>    */
>   public String getSql() {
>     return sqlStatement;
>   }
>
>
>   public void setUser(String user) {
>     databaseUser = user;
>   }
>
>
>   public void setURL(String url) {
>     databaseURL = url;
>   }
>
>
>   public void setPassword(String password) {
>     databasePassword = password;
>   }
>
>
>   public void setBufferSize(int newBufferSize) {
>     bufferSize = newBufferSize;
>     buffer.ensureCapacity(bufferSize);
>     removes.ensureCapacity(bufferSize);
>   }
>
>
>   public String getUser() {
>     return databaseUser;
>   }
>
>
>   public String getURL() {
>     return databaseURL;
>   }
>
>
>   public String getPassword() {
>     return databasePassword;
>   }
>
>
>   public int getBufferSize() {
>     return bufferSize;
>   }
>
>
>   /**
>    * Ensures that the given driver class has been loaded for
>sql connection
>    * creation.
>    */
>   public void setDriver(String driverClass) {
>     try {
>       Class.forName(driverClass);
>     } catch (Exception e) {
>       errorHandler.error("Failed to load driver", e,
>     ErrorCode.GENERIC_FAILURE);
>     }
>   }
>
>}
>
>
># An example config file for JDBCAppender:
>
>log4j.rootCategory=DEBUG, jdbc
>
># JDBCAppender writes messages into the database
>log4j.appender.jdbc=org.apache.log4j.JDBCAppender
>
># DB Options
>log4j.appender.jdbc.URL=jdbc:odbc:myDB
>log4j.appender.jdbc.user=me
>log4j.appender.jdbc.password=password
>log4j.appender.jdbc.driver=sun.jdbc.odbc.JdbcOdbcDriver
>
>#SQL statement to be used (with multiple columns formated)
>log4j.appender.jdbc.sql=insert into logTable (message, class, priority, 
>log_date) values ('%m', '%c', '%p', '%d')
>
>#set the buffer size
>log4j.appender.JDBC.buffersize=2
>
>
>--
>To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>

--
Ceki

My link of the month: http://java.sun.com/aboutJava/standardization/


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>