日志输出到数据库例子

日志输出到数据库例子：
log4j源码：类JDBCAppender。
如果实现日志输出到数据需要实现JDBCAppender的子类重写它的3个方法：
getConnection()；
closeConnection(Connection con)；
getLogStatement(LoggingEvent event)；

package org.apache.log4j.jdbc;

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

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

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

/**
  <p><b><font color="#FF2222">WARNING: This version of JDBCAppender
  is very likely to be completely replaced in the future. Moreoever,
  it does not log exceptions</font></b>.

  The JDBCAppender provides for sending log events to a database.
 
 
  <p>Each append call adds to an <code>ArrayList</code> buffer.  When
  the buffer is filled each log event is placed in a sql statement
  (configurable) and executed.

  <b>BufferSize</b>, <b>db URL</b>, <b>User</b>, & <b>Password</b> are
  configurable options in the standard log4j ways.

  <p>The <code>setSql(String sql)</code> sets the SQL statement to be
  used for logging -- this statement is sent to a
  <code>PatternLayout</code> (either created automaticly by the
  appender or added by the user).  Therefore by default all the
  conversion patterns in <code>PatternLayout</code> can be used
  inside of the statement.  (see the test cases for examples)

  <p>Overriding the {@link #getLogStatement} method allows more
  explicit control of the statement used for logging.

  <p>For use as a base class:

    <ul>

    <li>Override <code>getConnection()</code> to pass any connection
    you want.  Typically this is used to enable application wide
    connection pooling.

     <li>Override <code>closeConnection(Connection con)</code> -- if
     you override getConnection make sure to implement
     <code>closeConnection</code> to handle the connection you
     generated.  Typically this would return the connection to the
     pool it came from.

     <li>Override <code>getLogStatement(LoggingEvent event)</code> to
     produce specialized or dynamic statements. The default uses the
     sql option value.

    </ul>

    @author Kevin Steppe (<A HREF="mailto:[e-mail]">[e-mail]</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.
   *
   * 如果bufferSize是10,则当>=10个日志信息需要输出的时候,log4j才会把日志一次性写到
   * 数据库中;如果<10个日志信息,则日志信息不会写到数据库中,只会暂时存放到bufferSize中;
   * 怎么解决如下问题:(理解错误，不会出现这个问题，因为该类的close()方法中，在关闭连接前)
   * 当WEBSERVER关闭的时候,但是bufferSize中没有足够的日志信息,
   * 这个时候bufferSize中的日志信息是不会写到数据库中的.
   * 解决方法:
   * 1.把bufferSize设置成1,这个时候只要有一个日志,就会往数据库中写一次,拿connection
   * 频繁,资源开销大.
   * 2.在Listenner中处理,在WEBSERVER关闭前调用flushBuffer()方法.
   * 说明：close()方法的用处，是释放appender占用的资源，该方法是在finalize（）
   * 中被调用，并且在释放资源前会调用flushBuffer()方法，但是还是会出现上边的问题。
   */
  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.
   *
   * 该方法不要需要在子类中重写.
   * 不好的地方:
   * 1.connection 对象在这个方法中创建,导致了在flushBuffer()方法中,connection对象
   * 的创建和关闭出现在for循环中(有几个LoggingEvent就会创建关闭几次Connection).
   * 同理也会频繁创建和关闭statement对象
   *
   * */
  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);
      }
    }
   
    // remove from the buffer any events that were reported
    buffer.removeAll(removes);
   
    // clear the buffer of reported events
    removes.clear();
  }

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

  /**
   * JDBCAppender requires a layout.
   * */
  public boolean requiresLayout() {
    return true;
  }

  /**
   *
   */
  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);
    }
  }
}

 

自己实现的子类：
package lzh;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import org.apache.log4j.spi.ErrorCode;
import org.apache.log4j.spi.LoggingEvent;

public class MyJDBCAppender extends org.apache.log4j.jdbc.JDBCAppender {

 public MyJDBCAppender() {
  super();
  //this.bufferSize = 10;
 }

 protected Connection getConnection() throws SQLException {
  if (connection == null || connection.isClosed()) {
   connection = BaseBean.getConn("log");
//   System.out.println("create conn...");
  }
  return connection;
 }

 /**
  * 重新写个关闭statement对象的方法，避免和BaseBean耦合太紧。
  *
  * @param stmt
  */
 public void closeStmt(Statement stmt) {
  if (stmt != null) {
   try {
    stmt.close();
//    System.out.println("close stmt...");
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }

 /**
  * 覆盖父类方法
  */
 protected void closeConnection(Connection con) {
  try {
   if (con != null && !con.isClosed()){
    con.close();
//    System.out.println("conn close...");
   }
  } catch (SQLException e) {
   errorHandler.error("Error closing connection", e,
     ErrorCode.GENERIC_FAILURE);
  }

 }

 /**
  * 覆盖父类方法
  */
 public void flushBuffer() {
  removes.ensureCapacity(buffer.size());
  Connection con = null;
  Statement stmt = null;// 不能用PreparedStatement,因为拿不到具体要插到库中的值。
  try {
   con = this.getConnection();
   stmt = con.createStatement();

   for (Iterator i = buffer.iterator(); i.hasNext();) {
    try {
     LoggingEvent logEvent = (LoggingEvent) i.next();
     String sql = getLogStatement(logEvent);
     // execute(sql);
     stmt.addBatch(sql);
     removes.add(logEvent);
    } catch (SQLException e) {
     errorHandler.error("Failed to addBatch aql to statament",
       e, ErrorCode.FLUSH_FAILURE);
    }
   }
   stmt.executeBatch();
  } catch (SQLException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
   this.closeStmt(stmt);
   this.closeConnection(con);
  } finally {
   this.closeStmt(stmt);
   this.closeConnection(con);
  }
  // remove from the buffer any events that were reported
  buffer.removeAll(removes);

  // clear the buffer of reported events
  removes.clear();
 }

 /**
  * 覆盖父类方法，因为准备不用这个方法，所以该方法什么都不做。
  */
 protected void execute(String sql) throws SQLException {
  // do nothing
 }

}

log4j.properties:
# For JBoss: Avoid to setup Log4J outside $JBOSS_HOME/server/default/deploy/log4j.xml!
# For all other servers: Comment out the Log4J listener in web.xml to activate Log4J.

#第一中写法:INFO级别的输出找
log4j.rootLogger=INFO,ERROR,JDBC

log4j.appender.INFO=org.apache.log4j.ConsoleAppender
log4j.appender.INFO.layout=org.apache.log4j.PatternLayout
log4j.appender.INFO.layout.ConversionPattern= %p(%F:%L)%d{yyyy-MM-dd HH:mm} - %m%n
log4j.appender.INFO.Target=System.out

log4j.appender.ERROR=org.apache.log4j.ConsoleAppender
log4j.appender.ERROR.layout=org.apache.log4j.PatternLayout
log4j.appender.ERROR.layout.ConversionPattern= %p(%F:%L)%d{yyyy-MM-dd HH:mm:ss} - %m%n
log4j.appender.ERROR.Target=System.out

log4j.appender.JDBC=lzh.MyJDBCAppender
#log4j.appender.JDBC=org.apache.log4j.jdbc.JDBCAppender
#log4j.appender.JDBC=com.nbw.tsde.bean.JDBCPoolAppender
#缓冲多少个日志才拿数据库连接往数据库里写日志。
log4j.appender.JDBC.bufferSize=10
log4j.appender.JDBC.layout=org.apache.log4j.PatternLayout
log4j.appender.JDBC.sql=INSERT INTO EXEC_LOG (log_date, log_level, location, message) VALUES ('%d{yyyy-MM-dd HH:mm}', '%-5p', '%C,%L', '%m')