以oracle 10g数据库为例
在java应用里面用的最多的日志工具就是log4j了,log4j记录日志非常方便,可以写进普通文本文件、回滚文件、html文件、电子邮件和数据库中。但是日志信息的查看就有些不便了。在单纯的文本里查看记录太吃力了,html文件里的很漂亮,但还是不便查看。比如要看info日志,中间却有很多没用的debug日志信息。还有一点就是文本信息不便于存储检索备份恢复等。日志写进数据库就可以解决这些问题。
Log4j的JDBCAppender可以实现将日志信息写进数据库,但是“Unfortunately, that one was not able to process logging messages that had characters like ' (single quote) and , (comma) in it, because of its simple nature”(见http://www.dankomannhaupt.de/projects/index.html)。而且WARNING: This version of JDBCAppender is very likely to be completely replaced in the future. Moreoever, it does not log exceptions.(见org.apache.log4j.jdbc.JDBCAppender v-1.2.15的java doc中的第一句)
Jdbcplus(在项目主页中没有用Jdbcplus来命名该项目,而是用Log4j JDBCAppender,这里为了和Log4j的org.apache.log4j.jdbc.JDBCAppender区分而使用这个Jdbcplus词)很好的解决了以上问题,详见http://www.dankomannhaupt.de/projects/index.html。里面有详细的Configuration examples log4j.properties和DDL Script for logging table and stored procedure examples logtest.sql。在用的时候发现写进oracle时需要修改一些列信息和sql语句。
-- Create table
create table DB_LOG
(
ID INTEGER not null,
PRIO VARCHAR2(15),
IPRIO INTEGER,
CAT VARCHAR2(255),
THREAD VARCHAR2(30),
MSG VARCHAR2(255),
LAYOUT_MSG VARCHAR2(255),
THROWABLE VARCHAR2(4000),
NDC VARCHAR2(255),
MDC VARCHAR2(255),
MDC2 VARCHAR2(255),
INFO VARCHAR2(255),
ADDON VARCHAR2(255),
THE_TIMESTAMP TIMESTAMP(6),
CREATED_BY VARCHAR2(50),
THE_DATE DATE,
THE_TIME TIMESTAMP(6),
ELAPSED NUMBER
);
-- Add comments to the columns
comment on column DB_LOG.ELAPSED
is 'Used torecord the number of milliseconds elapsed from the construction of the layout until the creation of the logging event.';
-- Create/Recreate primary, unique and foreign key constraints
alter table DB_LOG
add constraint PK_DB_LOG_ID primary key (ID)
using index;
# JDBC appender directly.
#from http://www.dankomannhaupt.de/projects/jdbcappender/src/org/apache/log4j/jdbcplus/examples/test/log4j.properties
log4j.appender.database=org.apache.log4j.jdbcplus.JDBCAppender
# created db alias in aliases.conf: danko = D:/Programme/Firebird/Firebird1.5/Data/danko.fdb
log4j.appender.database.url=jdbc:oracle:thin:@localhost:1521:orcl
log4j.appender.database.dbclass=oracle.jdbc.driver.OracleDriver
log4j.appender.database.username=log4j
log4j.appender.database.password=123456
#log4j.appender.database.sql=INSERT INTO LOG_LOG4J2 (MSG) VALUES ('@MSG@')
#next: example for most functions/log types
log4j.appender.database.sql=INSERT INTO DB_LOG (id, ELAPSED, prio, iprio, cat, thread, msg, layout_msg, throwable, ndc, mdc, mdc2, info, addon, the_date, the_time, the_timestamp, created_by) VALUES (SEQ_LEVEL_ID.NEXTVAL, '@LAYOUT:2@', '@PRIO@', @IPRIO@, '@CAT@', '@THREAD@', '@MSG@', '@LAYOUT:1@', '@THROWABLE@', '@NDC@', '@MDC:MyMDC@', '@MDC:MyMDC2@', '@TIMESTAMP@', '@LAYOUT@',to_date('@LAYOUT:3@','YYYY-MM-DD'), to_date('@LAYOUT:4@','HH24:MI:SS'), to_date('@LAYOUT:3@ @LAYOUT:4@','YYYY-MM-DD HH24:MI:SS'), 'CatchLoggerFixer')
log4j.appender.database.layout=org.apache.log4j.PatternLayout
# conversion pattern with 4 parts separated by #
log4j.appender.database.layout.ConversionPattern=[]#%r#%d{yyyy-MM-dd}#%d{hh:mm:ss}
log4j.appender.database.layoutPartsDelimiter=#
log4j.appender.database.buffer=1
log4j.appender.database.commit=true
log4j.appender.database.quoteReplace=true
log4j.appender.database.throwableMaxChars=3000