log4j mysql 单引号_log4j写数据库存在单引号问题

项目中要求把日志信息写到文件的同时也把其写入数据库中, log4j.properties版本log4j-1.2.17, 配置如下

log4j.rootLogger=INFO,wjc,wjf,wjj

#common

log4j.appender.wjc.Encoding=GB2312

log4j.appender.wjc=org.apache.log4j.ConsoleAppender

log4j.appender.wjc.layout=org.apache.log4j.PatternLayout

log4j.appender.wjc.layout.ConversionPattern=%d %5p (%F:%L) - %m%n

log4j.appender.wjf.Encoding=GB2312

log4j.appender.wjf=org.apache.log4j.DailyRollingFileAppender

log4j.appender.wjf.Append=true

log4j.appender.wjf.File.DatePattern='.'yyyy-MM-dd

log4j.appender.wjf.File=${catalina.base}/logs/jin.log

log4j.appender.wjf.layout=org.apache.log4j.PatternLayout

log4j.appender.wjf.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss } %t %p -%m%n

log4j.appender.wjj.Threshold=WARN

log4j.appender.wjj=com.common.component.syslog.Log4JdbcAppender

log4j.appender.wjj.layout=org.apache.log4j.PatternLayout

log4j.appender.wjj.sql=insert into cp_syslog(log_time, log_level, location, message) values ('%d{yyyyMMddHHmmss }', '%-5p', '%C,%L', '%m')

#special

log4j.logger.org.apache.cxf=WARN

在代码中调用log.warn("abcd");等就可以把信息写入数据库了.错误日志:

log4j:ERROR Failed to excute sql

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'classes/spring/spring'')' at line 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)

at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)

at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)

at com.joysim.common.component.syslog.Log4JdbcAppender.execute(Log4JdbcAppender.java:52)

at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:289)

at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:186)

at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:251)

经调试发现要执行的sql语句里面包含了单引号，而我们知道要插入数据库中单引号是要进行转义处理的.

从错误信息中跟进发现sql是在JDBCAppender.flushBuffer()方法中进行了处理, 源码如下:

public voidflushBuffer() {//Do the actual logging

removes.ensureCapacity(buffer.size());for (Iterator i =buffer.iterator(); i.hasNext();) {

LoggingEvent logEvent=(LoggingEvent)i.next();try{

String sql=getLogStatement(logEvent);

execute(sql);

}catch(SQLException e) {

errorHandler.error("Failed to excute sql", e,

ErrorCode.FLUSH_FAILURE);

}finally{

removes.add(logEvent);

}

}

打印发现是getLogStatement(logEvent)对sql进行了处理, 该方法的代码如下:

/*** 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.

**/

protectedString getLogStatement(LoggingEvent event) {returngetLayout().format(event);

}

View Code

搜索setLayout()方法, 发现是在setSql(String s)方法中进行了赋值, 该方法代码如下:

public voidsetSql(String s) {

sqlStatement=s;if (getLayout() == null) {this.setLayout(newPatternLayout(s));

}else{

((PatternLayout)getLayout()).setConversionPattern(s);

}

}

View Code

该方法的意思就是如果log4j配置文件没有为jdbcAppender配置patterLayout, 那么会默认指定一个PatternLayout对象给jdbcAppender. 接下来就看一下PatternLayout的format方法, 代码如下:

/**Produces a formatted string as specified by the conversion pattern.*/

publicString format(LoggingEvent event) {//Reset working stringbuffer

if(sbuf.capacity() >MAX_CAPACITY) {

sbuf= newStringBuffer(BUF_SIZE);

}else{

sbuf.setLength(0);

}

PatternConverter c= head;while(c != null) {

c.format(sbuf, event);

c=c.next;

}returnsbuf.toString();

}

head在其构造方法中进行了赋值, 代码如下:

publicPatternLayout(String pattern) {this.pattern =pattern;

head= createPatternParser((pattern == null) ?DEFAULT_CONVERSION_PATTERN :

pattern).parse();

}

或是在setConversionPattern方法中进行赋值

public voidsetConversionPattern(String conversionPattern) {

pattern=conversionPattern;

head=createPatternParser(conversionPattern).parse();

}

从上面的代码中可以看到在JDBCAppender的setSql方法中对上述两方法进行了调用.

执行parse(),

publicorg.apache.log4j.helpers.PatternConverter parse() {return newBridgePatternConverter(pattern);

}

构造方法publicBridgePatternConverter(finalString pattern) {

next= null;

handlesExceptions= false;

List converters= newArrayList();

List fields= newArrayList();

Map converterRegistry= null;

PatternParser.parse(

pattern, converters, fields, converterRegistry,

PatternParser.getPatternLayoutRules());

patternConverters= newLoggingEventPatternConverter[converters.size()];

patternFields= newFormattingInfo[converters.size()];int i = 0;

Iterator converterIter=converters.iterator();

Iterator fieldIter=fields.iterator();while(converterIter.hasNext()) {

Object converter=converterIter.next();if (converter instanceofLoggingEventPatternConverter) {

patternConverters[i]=(LoggingEventPatternConverter) converter;

handlesExceptions|=patternConverters[i].handlesThrowable();

}else{

patternConverters[i]=

new org.apache.log4j.pattern.LiteralPatternConverter("");

}if(fieldIter.hasNext()) {

patternFields[i]=(FormattingInfo) fieldIter.next();

}else{

patternFields[i]=FormattingInfo.getDefault();

}

i++;

}

}

解析public static voidparse(final String pattern, finalList patternConverters,final List formattingInfos, final Map converterRegistry, finalMap rules) {if (pattern == null) {throw new NullPointerException("pattern");

}

StringBuffer currentLiteral= new StringBuffer(32);int patternLength =pattern.length();int state =LITERAL_STATE;charc;int i = 0;

FormattingInfo formattingInfo=FormattingInfo.getDefault();while (i

c= pattern.charAt(i++);switch(state) {caseLITERAL_STATE://In literal state, the last char is always a literal.

if (i ==patternLength) {

currentLiteral.append(c);continue;

}if (c ==ESCAPE_CHAR) {//peek at the next char.

switch(pattern.charAt(i)) {caseESCAPE_CHAR:

currentLiteral.append(c);

i++; //move pointer

break;default:if (currentLiteral.length() != 0) {

patternConverters.add(newLiteralPatternConverter(currentLiteral.toString()));

formattingInfos.add(FormattingInfo.getDefault());

}

currentLiteral.setLength(0);

currentLiteral.append(c);//append %

state =CONVERTER_STATE;

formattingInfo=FormattingInfo.getDefault();

}

}else{

currentLiteral.append(c);

}break;caseCONVERTER_STATE:

currentLiteral.append(c);switch(c) {case '-':

formattingInfo=

newFormattingInfo(true, formattingInfo.getMinLength(),

formattingInfo.getMaxLength());break;case '.':

state=DOT_STATE;break;default:if ((c >= '0') && (c <= '9')) {

formattingInfo=

newFormattingInfo(

formattingInfo.isLeftAligned(), c- '0',

formattingInfo.getMaxLength());

state=MIN_STATE;

}else{

i=finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

converterRegistry, rules, patternConverters, formattingInfos);//Next pattern is assumed to be a literal.

state =LITERAL_STATE;

formattingInfo=FormattingInfo.getDefault();

currentLiteral.setLength(0);

}

}//switch

break;caseMIN_STATE:

currentLiteral.append(c);if ((c >= '0') && (c <= '9')) {

formattingInfo=

newFormattingInfo(

formattingInfo.isLeftAligned(),

(formattingInfo.getMinLength()* 10) + (c - '0'),

formattingInfo.getMaxLength());

}else if (c == '.') {

state=DOT_STATE;

}else{

i=finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

converterRegistry, rules, patternConverters, formattingInfos);

state=LITERAL_STATE;

formattingInfo=FormattingInfo.getDefault();

currentLiteral.setLength(0);

}break;caseDOT_STATE:

currentLiteral.append(c);if ((c >= '0') && (c <= '9')) {

formattingInfo=

newFormattingInfo(

formattingInfo.isLeftAligned(), formattingInfo.getMinLength(),

c- '0');

state=MAX_STATE;

}else{

LogLog.error("Error occured in position " +i+ ".\n Was expecting digit, instead got char \"" + c + "\".");

state=LITERAL_STATE;

}break;caseMAX_STATE:

currentLiteral.append(c);if ((c >= '0') && (c <= '9')) {

formattingInfo=

newFormattingInfo(

formattingInfo.isLeftAligned(), formattingInfo.getMinLength(),

(formattingInfo.getMaxLength()* 10) + (c - '0'));

}else{

i=finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

converterRegistry, rules, patternConverters, formattingInfos);

state=LITERAL_STATE;

formattingInfo=FormattingInfo.getDefault();

currentLiteral.setLength(0);

}break;

}//switch

}//while

if (currentLiteral.length() != 0) {

patternConverters.add(newLiteralPatternConverter(currentLiteral.toString()));

formattingInfos.add(FormattingInfo.getDefault());

}

}

View Code

代码较长, 大概的意思就是把log4j配置文件中的sql语句, insert into cp_syslog(log_time, log_level, location, message) values ('%d{yyyyMMddHHmmss }', '%-5p', '%C,%L', '%m'), 中的p, c, l, m等都转成PatternConverter, 并返回第一个PatternConverter, 说到底就是一个链表, 而this.head是这个链表的头元素. 现在主要就是关注各个PatternConverter的format方法,

static{//We set the global rules in the static initializer of PatternParser class

Map rules = new HashMap(17);

rules.put("c", LoggerPatternConverter.class);

rules.put("logger", LoggerPatternConverter.class);

rules.put("C", ClassNamePatternConverter.class);

rules.put("class", ClassNamePatternConverter.class);

rules.put("d", DatePatternConverter.class);

rules.put("date", DatePatternConverter.class);

rules.put("F", FileLocationPatternConverter.class);

rules.put("file", FileLocationPatternConverter.class);

rules.put("l", FullLocationPatternConverter.class);

rules.put("L", LineLocationPatternConverter.class);

rules.put("line", LineLocationPatternConverter.class);

rules.put("m", MessagePatternConverter.class);

rules.put("message", MessagePatternConverter.class);

rules.put("n", LineSeparatorPatternConverter.class);

rules.put("M", MethodLocationPatternConverter.class);

rules.put("method", MethodLocationPatternConverter.class);

rules.put("p", LevelPatternConverter.class);

rules.put("level", LevelPatternConverter.class);

rules.put("r", RelativeTimePatternConverter.class);

rules.put("relative", RelativeTimePatternConverter.class);

rules.put("t", ThreadPatternConverter.class);

rules.put("thread", ThreadPatternConverter.class);

rules.put("x", NDCPatternConverter.class);

rules.put("ndc", NDCPatternConverter.class);

rules.put("X", PropertiesPatternConverter.class);

rules.put("properties", PropertiesPatternConverter.class);

rules.put("sn", SequenceNumberPatternConverter.class);

rules.put("sequenceNumber", SequenceNumberPatternConverter.class);

rules.put("throwable", ThrowableInformationPatternConverter.class);

PATTERN_LAYOUT_RULES= newReadOnlyMap(rules);

Map fnameRules= new HashMap(4);

fnameRules.put("d", FileDatePatternConverter.class);

fnameRules.put("date", FileDatePatternConverter.class);

fnameRules.put("i", IntegerPatternConverter.class);

fnameRules.put("index", IntegerPatternConverter.class);

FILENAME_PATTERN_RULES= newReadOnlyMap(fnameRules);

}

MessagePatternConverter的format方法如下:

public void format(final LoggingEvent event, finalStringBuffer toAppendTo) {

toAppendTo.append(event.getRenderedMessage());

}

MyLoggingEvent类中有getThreadName和getRenderedMessage两个方法

很奇怪, 跟踪到这里发现源码中是有对单引号进行处理的, 可是为什么还会出现问题呢

ThreadPatternConverter 的format方法如下:

public void format(final LoggingEvent event, finalStringBuffer toAppendTo) {

toAppendTo.append(event.getThreadName());

}

我们可以在event.getThreadName()和event.getRenderedMessage()方法返回前对单引号进行替换, 因此我们可以写一个类扩展LoggingEvent, 重写LoggingEvent的getRenderedMessage和getThreadName方法, 代码如下:

public class MyLoggingEvent extendsLoggingEvent {/** */

private static final long serialVersionUID = -3499094864944744184L;publicMyLoggingEvent(String fqnOfCategoryClass, Category logger, Priority level, Object message, Throwable throwable) {super(fqnOfCategoryClass, logger, level, message, throwable);

}publicString getThreadName() {

String thrdName= super.getThreadName();if (thrdName.indexOf("'") != -1) {

thrdName= thrdName.replaceAll("'", "''");

}returnthrdName;

}publicString getRenderedMessage() {

String msg= super.getRenderedMessage();if (msg.indexOf("'") != -1) {

msg= msg.replaceAll("'", "''");

}returnmsg;

}

}

最后在我们log4j配置文件中指定的Log4JdbcAppender处理类中, 覆写JDBCAppender的getLogStatement方法, 让程序执行我们重写的MyLoggingEvent类中的相应方法, 代码如下:

public class Log4JdbcAppender extendsJDBCAppender {privateJdbcSupport jdbcSupport;

@Overrideprotected voidcloseConnection(Connection conn) {try{if (conn != null && !conn.isClosed())

conn.close();

}catch(SQLException e) {

errorHandler.error("Error closing connection", e, ErrorCode.GENERIC_FAILURE);

}

}

@Overrideprotected Connection getConnection() throwsSQLException {if (jdbcSupport == null) {

jdbcSupport= SpringUtils.getBean(JdbcSupport.class, "jdbcSupport");

}return jdbcSupport == null ? null: jdbcSupport.getJt().getDataSource().getConnection();

}

@Overrideprotected void execute(String sql) throwsSQLException {

Connection conn=getConnection();if (conn != null) {

Statement stmt= null;try{

stmt=conn.createStatement();

stmt.executeUpdate(sql);

}catch(SQLException e) {if (stmt != null)

stmt.close();throwe;

}

stmt.close();

closeConnection(conn);

}

}

@OverrideprotectedString getLogStatement(LoggingEvent event) {

String fqnOfCategoryClass=event.fqnOfCategoryClass;

Category logger=event.getLogger();

Priority level=event.getLevel();

Object message=event.getMessage();

Throwable throwable=null;

MyLoggingEvent bEvent=new MyLoggingEvent(fqnOfCategoryClass,logger,level,message,throwable);

return super.getLogStatement(bEvent);

}

}

大功告成, 日志成功插入到数据库