给log4j配置数据库连接

 我们都知道log4j是一个优秀的开源日志记录项目，我们不仅可以对输出的日志的格式自定义，还可以自己定义日志输出的目的地，比如：屏幕，文本文件，数据库，甚至能通过socket输出。
        现在让我们对日志输出到数据库来进行配置
         配置如下：
#---JDBC ---输出到数据库
# JDBCAppender log4j.properties file
#log4j.rootCategory=WARN,JDBC
# APPENDER JDBC
log4j.appender.JDBC=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.JDBC.driver=com.mysql.jdbc.Driver
log4j.appender.JDBC.URL=jdbc:mysql://localhost:3306/test
log4j.appender.JDBC.user=use
log4j.appender.JDBC.password=password
log4j.appender.JDBC.layout=org.apache.log4j.PatternLayout
log4j.appender.JDBC.sql=INSERT INTO LOGGING (log_date, log_level, location, message) VALUES ('%d{ISO8601}', '%-5p', '%C,%L', '%m')

表结构如下：
log_date   varchar2(50)
log_level  varchar2(5)
location   varchar2(100) 
message    varchar2(1000)
笔者照做，但没有运行成功，而且此种方法是利用传统的数据库连接方法，对于数据库的管理和效率严重不足，在现在这个连接池横行的时代,为什么我们不能给给Log4j配上连接池，让Log4j利用数据连接池的连接和数据库进行通讯。现查看Log4j的Api,发现JDBCAppender这个类有以下几段话：WARNING: This version of JDBCAppender is very likely to be completely replaced in the future. Moreoever, it does not log exceptions. The JDBCAppender provides for sending log events to a database.

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.

原来log4j建议我们把其提供的JDBCAppender作为基类来使用，然后Override三个父类的方法：getConnection(),closeConnection(Connection con)和getLogStatement(LoggingEvent event)。
原来如此，那就写一个子类JDBCPoolAppender来替代这个JDBCAppender
JDBCPoolAppender代码和其相关代码如下：

JDBCPoolAppender.java:

package common.log;
import java.sql.Connection;
import org.apache.log4j.spi.LoggingEvent;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import org.apache.log4j.spi.ErrorCode;
import org.apache.log4j.PatternLayout;
import common.sql.MyDB;
import common.sql.GeneralDb;

public class JDBCPoolAppender extends org.apache.log4j.jdbc.JDBCAppender {
   
    private MyDB mydb = null;  
    protected String sqlname=""; //增加一个数据库jndiName的属性
    protected Connection connection = null;
    protected String sqlStatement = "";
    /**
     * size of LoggingEvent buffer before writting to the database.
     * Default is 1.
     */
    protected int bufferSize = 1;

    public JDBCPoolAppender() {
        super();
    }

    /**
     * ArrayList holding the buffer of Logging Events.
     */
    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) {
        mydb=null; 
        try {
           if (connection != null && !connection.isClosed())
               connection.close();
       } catch (SQLException e) {
           errorHandler.error("Error closing connection", e,
                              ErrorCode.GENERIC_FAILURE);
       }

    }

    /**
     * Override 此函数来利用连接池返回一个Connetion对象
     *    
     */
    protected Connection getConnection() throws SQLException { 
        try {
            mydb = GeneralDb.getInstance(sqlname);  
            connection = mydb.getConnection();     
       } catch (Exception e) {
            errorHandler.error("Error opening connection", e, ErrorCode.GENERIC_FAILURE);
        }
        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 setSqlname(String sqlname){
        sqlname=sqlname;
    }
   
    public String getSqlname(){
        return sqlname;
    }   

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

  
    public int getBufferSize() {
        return bufferSize;
    }
}

MyDB.java:
package common.sql;
import java.sql.*;
import com.codestudio.sql.*;  //引入开源项目Poolman数据库连接池的包

public class MyDB {
    public static final String module = MyDB.class.getName();
    private String dbName = "";
    private PoolMan plmn = null;

    public MyDB(String dbName) {
        try {
            if (plmn == null) {
                plmn = (PoolMan) Class.forName("com.codestudio.sql.PoolMan").
                        newInstance();
            }
        } catch (Exception ec) {
           System.out.println(ec.toString()+module);
        }
        this.dbName = dbName;
    }

    private Connection getNewConnection() {
        Connection conn = null;
        try {
            conn = plmn.connect("jdbc:poolman://" + dbName);  
            conn.setAutoCommit(true);
        } catch (Exception ec) {
            System.out.println(ec.toString()+"First:Connect sqlsever failed"+module);
            try {
                Thread.sleep(1000);
                conn = plmn.connect("jdbc:poolman://" + dbName);
                conn.setAutoCommit(true);
            } catch (Exception ecs) {
               System.out.println(ecs.toString()+"Again:Connect sqlsever faile"+module);
            }
        }
        return conn;
    }

    public Connection getConnection() {
        return getNewConnection();
    }
}
GeneralDb.java:

package common.sql;

package common.sql;

import java.util.*;

public class GeneralDb {
    private static Hashtable dbPool;
    public static MyDB getInstance(String dbname) {
        if (dbPool == null) {
            dbPool = new Hashtable();
        }
        MyDB db = (MyDB) dbPool.get(dbname);
        if (db == null) {
            db = new MyDB(dbname);
            dbPool.put(dbname, db);
        }
        return db;
    }
}

Log4j数据库连接池的配置如下：
log4j.appender.JDBC=common.log.JDBCPoolAppender
log4j.appender.JDBC.sqlname=log
log4j.appender.JDBC.layout=org.apache.log4j.PatternLayout
log4j.appender.JDBC.sql=INSERT INTO LOGGING (log_date, log_level, location, message) VALUES ('%d{ISO8601}', '%-5p', '%C,%L', '%m')


poolman.xml配置如下：

〈?xml version="1.0" encoding="UTF-8"?>
〈poolman>
  〈management-mode>local〈/management-mode>
  〈datasource>
    〈dbname>log〈/dbname>
    〈jndiName>log〈/jndiName>
    〈driver>com.mysql.jdbc.Driver〈/driver>
    〈url>jdbc:mysql://localhost:3306/test〈/url>
    〈username>use〈/username>
    〈password>password〈/password>
    〈minimumSize>0〈/minimumSize>
    〈maximumSize>10〈/maximumSize>
    〈logFile>logs/mysql.log〈/logFile>
  〈/datasource>

〈/poolman>
   

运行成功！对于JDBCPoolAppender的属性（比如sqlname属性）我们可以利用Log4j的反射机制随便添加，只要在配置文件给其附上值即可应用，而原来的父类里面的一些属性（username什么的)和其get,set方法由于在连接池中不需要，所以删除。而在JDBCPoolAppender类中，我也只是将getConnection 方法Override ，在这个方法中我们可以根据需要生成我们的Connection对象，另外两个方法大家可以根据需求来决定怎样Override。：）