log4j 写入数据库遇到问题

Question

Hi
向通过log4j将日志写入指定资料库中，但报出错误，麻烦看看哪有问题？！先谢过

log4j:去apache下的最新的(log4j-1.2.15.jar)
DB:oralce 10g，driver使用的是ojdbc14.jar (换过两个版本，2005年和2008年的)

程式代码：
Java code

import sun.jdbc.odbc.*;
import java.sql.*;
import org.apache.log4j.Category;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.apache.log4j.jdbc.*;

public class Test {
public static void main(String[] args) {
final Category cat = Category.getInstance(Test.class.getName());
PropertyConfigurator.configure ( "resx/property/log4j.properties" );
cat.debug ("just test") ;
}
}

配置文件log4j.properties内容：
BatchFile code

log4j.rootLogger=DEBUG,DATABASE
log4j.appender.DATABASE=org.apache.log4j.jdbc.JDBCAppender
#connstring
log4j.appender.DATABASE.URL=jdbc:oracle:thin:@localhost:1521:dbname
log4j.appender.DATABASE.BufferSize=40

log4j.appender.DATABASE.driver=oracle.jdbc.driver.OracleDriver
#user
log4j.appender.DATABASE.user=
#pswd
log4j.appender.DATABASE.password=
log4j.appender.DATABASE.sql=INSERT INTO test3(time,message) values(sysdate,'%c-%-4r[%t]%-5p%c%x-%m%n')
log4j.appender.DATABASE.layout=org.apache.log4j.PatternLayout

log4j.appender.DATABASE.layout.ConversionPattern=-%c-%-4r[%t]%-5p%c%x-%m%n

[b]问题补充：[/b]
Hi
向通过log4j将日志写入指定资料库中，但报出错误，麻烦看看哪有问题？！先谢过

log4j:去apache下的最新的(log4j-1.2.15.jar)
DB:oralce 10g，driver使用的是ojdbc14.jar (换过两个版本，2005年和2008年的)

程式代码：
Java code

import sun.jdbc.odbc.*;
import java.sql.*;
import org.apache.log4j.Category;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.apache.log4j.jdbc.*;

public class Test {
public static void main(String[] args) {
final Category cat = Category.getInstance(Test.class.getName());
PropertyConfigurator.configure ( "resx/property/log4j.properties" );
cat.debug ("just test") ;
}
}

配置文件log4j.properties内容：
BatchFile code

log4j.rootLogger=DEBUG,DATABASE
log4j.appender.DATABASE=org.apache.log4j.jdbc.JDBCAppender
#connstring
log4j.appender.DATABASE.URL=jdbc:oracle:thin:@localhost:1521:dbname
log4j.appender.DATABASE.BufferSize=40

log4j.appender.DATABASE.driver=oracle.jdbc.driver.OracleDriver
#user
log4j.appender.DATABASE.user=
#pswd
log4j.appender.DATABASE.password=
log4j.appender.DATABASE.sql=INSERT INTO test3(time,message) values(sysdate,'%c-%-4r[%t]%-5p%c%x-%m%n')
log4j.appender.DATABASE.layout=org.apache.log4j.PatternLayout

log4j.appender.DATABASE.layout.ConversionPattern=-%c-%-4r[%t]%-5p%c%x-%m%n

很抱歉，忘记添出错误信息了！
错误讯息：
log4j:ERROR Failed to load driver
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at org.apache.log4j.jdbc.JDBCAppender.setDriver(JDBCAppender.java:351)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.log4j.config.PropertySetter.setProperty(PropertySetter.java:206)
at org.apache.log4j.config.PropertySetter.setProperty(PropertySetter.java:165)
at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:130)
at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:97)
at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigurator.java:689)
at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigurator.java:647)
at org.apache.log4j.PropertyConfigurator.configureRootCategory(PropertyConfigurator.java:544)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:440)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:334)
at org.apache.log4j.PropertyConfigurator.configure(PropertyConfigurator.java:342)
at Test.main(Test.java:12)

Answer 1

你好，我也做了一个测试，直接配置后，sql并没有执行。配置也没有问题。
上网看了一下API，发现JDBCAppender 已经不推荐使用了。[color=red]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.

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(org.apache.log4j.spi.LoggingEvent) 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.[/color]

意思我们必须自己去包装他。
我自己写了例子，你参考一下：
[code="java"]package com.log4j.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.log4j.jdbc.JDBCAppender;
import org.apache.log4j.spi.LoggingEvent;

public class JdbcAppendtest {
private Logger logger;

private JDBCAppender jdbcAppender;

/***********************************************************************************************
 * 自己定义的日志保存类
 **********************************************************************************************/
class WdzJDBCAppender extends JDBCAppender {
    private LoggingEvent e;

    /**
     * 可以修改这个方法，实现PreparedStatement，Connection得重用
     */
    protected void execute(String sql) throws SQLException {
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = getConnection();
            stmt = con.prepareStatement(this.getSql());
            stmt.setString(1, e.getLoggerName());// LogName
            stmt.setString(2, e.getLevel().toString());// LogLevel
            stmt.setString(3, (String) e.getMessage());// MSG
            stmt.execute();
        } catch (SQLException e) {
            if (stmt != null) stmt.close();
            throw e;
        }
        stmt.close();
        closeConnection(con);
    }

    /*******************************************************************************************
     * *保存日志事件
     ******************************************************************************************/
    protected String getLogStatement(LoggingEvent event) {
        e = event;
        return getLayout().format(event);
    }
}

public JdbcAppendtest() {
    logger = Logger.getLogger(JdbcAppendtest.class.getName());
    // 测试，使用jdbc来保存日志信息
    testJDBCAppender();
}

/***********************************************************************************************
 * 写信息到日志中去
 **********************************************************************************************/
private void writeLogInfo() {
    logger.addAppender(jdbcAppender);
    logger.setLevel(Level.ALL);
    logger.debug("aaa   debug   info");
    // logger.debug("aaa debug info.aaa");
    logger.info("bbb   info");
    logger.warn("warn   info");
    logger.error("error   info");
    logger.fatal("fatal   info");

}

private void testJDBCAppender() {
    jdbcAppender = new WdzJDBCAppender();
    // 下面的参数可以自己修改满足自己的要求
    // 设置 日志数据库保存使用的参数，可以采用配置文件的方式来加载信息
    jdbcAppender.setDriver("oracle.jdbc.driver.OracleDriver");
    jdbcAppender.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.13.232)(PORT=1521)))(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED)))");
    jdbcAppender.setUser("tag2");
    jdbcAppender.setPassword("tag2");
    jdbcAppender.setSql("insert   into   TEST_T   values   (?,?,?)");
    // 测试使用数据库保存日志信息
    writeLogInfo();
    jdbcAppender.close();
}

public static void main(String[] args) {
    JdbcAppendtest wdzTest11 = new JdbcAppendtest();
}

}[/code]