日志持久化插入sql语法错误处理

准备将项目中的日志持久化到数据库，log4j.properties配置好，项目启动之后报如下错误：

2017-04-30 16:37:35 INFO    (org.springframework.context.annotation.ClassPathBeanDefinitionScanner) JSR-250 'javax.annotation.ManagedBean' found and supported for component scanning
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 'javax.annotation.ManagedBean' found and supported for component scanning')' at line 1

分析过程如下，通过比较数据库中的相邻的日志记录与该日志的区别发现该日志中有单引号，而配置文件中sql的格式中也是使用的是单引号，因此日志插入出现了异常，这个异常是没有经过转义的sql出现了错误的语法的情况下执行插入出了错误，经过配置文件和日志信息综合推断，错误的sql如下：

insert into esmp_log_info (class,method,createtime,loglevel,msg) VALUES ('9',
		'org.springframework.beans.factory.xml.XmlBeanDefinitionReader',
		'loadBeanDefinitions',
		'2017-04-30 04:27:26',
		'INFO',
		'JSR-250 'javax.annotation.ManagedBean' found and supported for component scanning')

插入的动作中values中出现了两对重合的单引号，导致了错误。要使其正确执行必须进行防止出现这个情况。

解决方案：对配置文件中的sql的格式进行转义，防止单引号错误，修改前：

log4j.appender.database.sql=insert into esmp_log_info (class,method,createtime,loglevel,msg) VALUES ('%C','%M','%d{yyyy-MM-dd hh:mm:ss}','%p','%m')

修改后：

log4j.appender.database.sql=insert into esmp_log_info (class,method,createtime,loglevel,msg) VALUES (\"%C\",\"%M\",\"%d{yyyy-MM-dd hh:mm:ss}\",\"%p\",\"%m\")

修改之后启动时没有报错，问题解决了。

附配置文件：

log4j.rootLogger=INFO, DailyRolling, Terminal,database,stdout

log4j.appender.DailyRolling=com.bj58.esmpol.utils.logs.DailyRollingApender
log4j.appender.DailyRolling.layout=org.apache.log4j.PatternLayout
log4j.appender.DailyRolling.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-7p (%c) %m%n
log4j.appender.DailyRolling.File=/tmp/logs/esmpol
log4j.appender.DailyRolling.DatePattern=yyyy-MM-dd'.log' 
log4j.appender.DailyRolling.Threshold=DEBUG
log4j.appender.DailyRolling.BufferedIO=true 
log4j.appender.DailyRolling.BufferSize=8192
log4j.appender.DailyRolling.MaxFileSize=50MB
log4j.appender.DailyRolling.MaxBackupIndex=100

log4j.appender.Terminal=org.apache.log4j.ConsoleAppender
log4j.appender.Terminal.layout=org.apache.log4j.PatternLayout
log4j.appender.Terminal.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-7p (%c) %m%n

log4j.logger.httpclient.wire=INFO
log4j.logger.org.apache.commons=INFO
log4j.logger.org.hibernate=WARN

log4j.logger.com.mchange.v2=INFO
log4j.logger.org.springframework=INFO
log4j.logger.org.apache.http=WARN
log4j.logger.org.apache.commons.httpclient=WARN
log4j.logger.com.bj58.bsp=INFO
log4j.logger.com.bj58.spat=INFO

########################  
 
# JDBC Appender  
 
#######################  
### stdout ###
#
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
#
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss}  %m%n

###JDBC###
log4j.appender.database=org.apache.log4j.ConsoleAppender
log4j.appender.database.layout=org.apache.log4j.PatternLayout
log4j.appender.database.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss:SSS}[%p]: %m%n

log4j.appender.database=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.database.driver=com.mysql.jdbc.Driver
log4j.appender.database.URL=jdbc:mysql://localhost:3306/esmp?useUnicode=true&characterEncoding=UTF-8
log4j.appender.database.user=root
log4j.appender.database.password=root
log4j.appender.database.sql=insert into esmp_log_info (class,method,createtime,loglevel,msg) VALUES ('%C','%M','%d{yyyy-MM-dd hh:mm:ss}','%p','%m')
log4j.appender.database.layout=org.apache.log4j.PatternLayout