log4j mysql 单引号_如何给Log4j配上数据库连接池

#---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,

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>