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

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

log4j.rootLogger&＃61;INFO,wjc,wjf,wjj

#common

log4j.appender.wjc.Encoding&＃61;GB2312

log4j.appender.wjc&＃61;org.apache.log4j.ConsoleAppender

log4j.appender.wjc.layout&＃61;org.apache.log4j.PatternLayout

log4j.appender.wjc.layout.ConversionPattern&＃61;%d %5p (%F:%L) - %m%n

log4j.appender.wjf.Encoding&＃61;GB2312

log4j.appender.wjf&＃61;org.apache.log4j.DailyRollingFileAppender

log4j.appender.wjf.Append&＃61;true

log4j.appender.wjf.File.DatePattern&＃61;&＃39;.&＃39;yyyy-MM-dd

log4j.appender.wjf.File&＃61;${catalina.base}/logs/jin.log

log4j.appender.wjf.layout&＃61;org.apache.log4j.PatternLayout

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

log4j.appender.wjj.Threshold&＃61;WARN

log4j.appender.wjj&＃61;com.common.component.syslog.Log4JdbcAppender

log4j.appender.wjj.layout&＃61;org.apache.log4j.PatternLayout

log4j.appender.wjj.sql&＃61;insert into cp_syslog(log_time, log_level, location, message) values (&＃39;%d{yyyyMMddHHmmss }&＃39;, &＃39;%-5p&＃39;, &＃39;%C,%L&＃39;, &＃39;%m&＃39;)

#special

log4j.logger.org.apache.cxf&＃61;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 &＃39;classes/spring/spring&＃39;&＃39;)&＃39; 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语句里面包含了单引号&＃xff0c;而我们知道要插入数据库中单引号是要进行转义处理的.

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

public voidflushBuffer() {//Do the actual logging

removes.ensureCapacity(buffer.size());for (Iterator i &＃61;buffer.iterator(); i.hasNext();) {

LoggingEvent logEvent&＃61;(LoggingEvent)i.next();try{

String sql&＃61;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&＃61;s;if (getLayout() &＃61;&＃61; 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&＃61; newStringBuffer(BUF_SIZE);

}else{

sbuf.setLength(0);

}

PatternConverter c&＃61; head;while(c !&＃61; null) {

c.format(sbuf, event);

c&＃61;c.next;

}returnsbuf.toString();

}

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

publicPatternLayout(String pattern) {this.pattern &＃61;pattern;

head&＃61; createPatternParser((pattern &＃61;&＃61; null) ?DEFAULT_CONVERSION_PATTERN :

pattern).parse();

}

或是在setConversionPattern方法中进行赋值

public voidsetConversionPattern(String conversionPattern) {

pattern&＃61;conversionPattern;

head&＃61;createPatternParser(conversionPattern).parse();

}

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

执行parse(),

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

}

构造方法publicBridgePatternConverter(finalString pattern) {

next&＃61; null;

handlesExceptions&＃61; false;

List converters&＃61; newArrayList();

List fields&＃61; newArrayList();

Map converterRegistry&＃61; null;

PatternParser.parse(

pattern, converters, fields, converterRegistry,

PatternParser.getPatternLayoutRules());

patternConverters&＃61; newLoggingEventPatternConverter[converters.size()];

patternFields&＃61; newFormattingInfo[converters.size()];int i &＃61; 0;

Iterator converterIter&＃61;converters.iterator();

Iterator fieldIter&＃61;fields.iterator();while(converterIter.hasNext()) {

Object converter&＃61;converterIter.next();if (converter instanceofLoggingEventPatternConverter) {

patternConverters[i]&＃61;(LoggingEventPatternConverter) converter;

handlesExceptions|&＃61;patternConverters[i].handlesThrowable();

}else{

patternConverters[i]&＃61;

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

}if(fieldIter.hasNext()) {

patternFields[i]&＃61;(FormattingInfo) fieldIter.next();

}else{

patternFields[i]&＃61;FormattingInfo.getDefault();

}

i&＃43;&＃43;;

}

}

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

}

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

FormattingInfo formattingInfo&＃61;FormattingInfo.getDefault();while (i

c&＃61; pattern.charAt(i&＃43;&＃43;);switch(state) {caseLITERAL_STATE://In literal state, the last char is always a literal.

if (i &＃61;&＃61;patternLength) {

currentLiteral.append(c);continue;

}if (c &＃61;&＃61;ESCAPE_CHAR) {//peek at the next char.

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

currentLiteral.append(c);

i&＃43;&＃43;; //move pointer

break;default:if (currentLiteral.length() !&＃61; 0) {

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

formattingInfos.add(FormattingInfo.getDefault());

}

currentLiteral.setLength(0);

currentLiteral.append(c);//append %

state &＃61;CONVERTER_STATE;

formattingInfo&＃61;FormattingInfo.getDefault();

}

}else{

currentLiteral.append(c);

}break;caseCONVERTER_STATE:

currentLiteral.append(c);switch(c) {case &＃39;-&＃39;:

formattingInfo&＃61;

newFormattingInfo(true, formattingInfo.getMinLength(),

formattingInfo.getMaxLength());break;case &＃39;.&＃39;:

state&＃61;DOT_STATE;break;default:if ((c >&＃61; &＃39;0&＃39;) && (c <&＃61; &＃39;9&＃39;)) {

formattingInfo&＃61;

newFormattingInfo(

formattingInfo.isLeftAligned(), c- &＃39;0&＃39;,

formattingInfo.getMaxLength());

state&＃61;MIN_STATE;

}else{

i&＃61;finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

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

state &＃61;LITERAL_STATE;

formattingInfo&＃61;FormattingInfo.getDefault();

currentLiteral.setLength(0);

}

}//switch

break;caseMIN_STATE:

currentLiteral.append(c);if ((c >&＃61; &＃39;0&＃39;) && (c <&＃61; &＃39;9&＃39;)) {

formattingInfo&＃61;

newFormattingInfo(

formattingInfo.isLeftAligned(),

(formattingInfo.getMinLength()* 10) &＃43; (c - &＃39;0&＃39;),

formattingInfo.getMaxLength());

}else if (c &＃61;&＃61; &＃39;.&＃39;) {

state&＃61;DOT_STATE;

}else{

i&＃61;finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

converterRegistry, rules, patternConverters, formattingInfos);

state&＃61;LITERAL_STATE;

formattingInfo&＃61;FormattingInfo.getDefault();

currentLiteral.setLength(0);

}break;caseDOT_STATE:

currentLiteral.append(c);if ((c >&＃61; &＃39;0&＃39;) && (c <&＃61; &＃39;9&＃39;)) {

formattingInfo&＃61;

newFormattingInfo(

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

c- &＃39;0&＃39;);

state&＃61;MAX_STATE;

}else{

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

state&＃61;LITERAL_STATE;

}break;caseMAX_STATE:

currentLiteral.append(c);if ((c >&＃61; &＃39;0&＃39;) && (c <&＃61; &＃39;9&＃39;)) {

formattingInfo&＃61;

newFormattingInfo(

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

(formattingInfo.getMaxLength()* 10) &＃43; (c - &＃39;0&＃39;));

}else{

i&＃61;finalizeConverter(

c, pattern, i, currentLiteral, formattingInfo,

converterRegistry, rules, patternConverters, formattingInfos);

state&＃61;LITERAL_STATE;

formattingInfo&＃61;FormattingInfo.getDefault();

currentLiteral.setLength(0);

}break;

}//switch

}//while

if (currentLiteral.length() !&＃61; 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 (&＃39;%d{yyyyMMddHHmmss }&＃39;, &＃39;%-5p&＃39;, &＃39;%C,%L&＃39;, &＃39;%m&＃39;), 中的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 &＃61; 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&＃61; newReadOnlyMap(rules);

Map fnameRules&＃61; 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&＃61; 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 &＃61; -3499094864944744184L;publicMyLoggingEvent(String fqnOfCategoryClass, Category logger, Priority level, Object message, Throwable throwable) {super(fqnOfCategoryClass, logger, level, message, throwable);

}publicString getThreadName() {

String thrdName&＃61; super.getThreadName();if (thrdName.indexOf("&＃39;") !&＃61; -1) {

thrdName&＃61; thrdName.replaceAll("&＃39;", "&＃39;&＃39;");

}returnthrdName;

}publicString getRenderedMessage() {

String msg&＃61; super.getRenderedMessage();if (msg.indexOf("&＃39;") !&＃61; -1) {

msg&＃61; msg.replaceAll("&＃39;", "&＃39;&＃39;");

}returnmsg;

}

}

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

public class Log4JdbcAppender extendsJDBCAppender {privateJdbcSupport jdbcSupport;

&＃64;Overrideprotected voidcloseConnection(Connection conn) {try{if (conn !&＃61; null && !conn.isClosed())

conn.close();

}catch(SQLException e) {

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

}

}

&＃64;Overrideprotected Connection getConnection() throwsSQLException {if (jdbcSupport &＃61;&＃61; null) {

jdbcSupport&＃61; SpringUtils.getBean(JdbcSupport.class, "jdbcSupport");

}return jdbcSupport &＃61;&＃61; null ? null: jdbcSupport.getJt().getDataSource().getConnection();

}

&＃64;Overrideprotected void execute(String sql) throwsSQLException {

Connection conn&＃61;getConnection();if (conn !&＃61; null) {

Statement stmt&＃61; null;try{

stmt&＃61;conn.createStatement();

stmt.executeUpdate(sql);

}catch(SQLException e) {if (stmt !&＃61; null)

stmt.close();throwe;

}

stmt.close();

closeConnection(conn);

}

}

&＃64;OverrideprotectedString getLogStatement(LoggingEvent event) {

String fqnOfCategoryClass&＃61;event.fqnOfCategoryClass;

Category logger&＃61;event.getLogger();

Priority level&＃61;event.getLevel();

Object message&＃61;event.getMessage();

Throwable throwable&＃61;null;

MyLoggingEvent bEvent&＃61;new MyLoggingEvent(fqnOfCategoryClass,logger,level,message,throwable);

return super.getLogStatement(bEvent);

}

}

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