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

来源：互联网发布：微念科技有限公司知乎编辑：程序博客网时间：2024/06/12 23:57

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

2017-04-30 16:37:35 INFO    (org.springframework.context.annotation.ClassPathBeanDefinitionScanner) JSR-250 'javax.annotation.ManagedBean' found and supported for component scanninglog4j:ERROR Failed to excute sqlcom.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,stdoutlog4j.appender.DailyRolling=com.bj58.esmpol.utils.logs.DailyRollingApenderlog4j.appender.DailyRolling.layout=org.apache.log4j.PatternLayoutlog4j.appender.DailyRolling.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-7p (%c) %m%nlog4j.appender.DailyRolling.File=/tmp/logs/esmpollog4j.appender.DailyRolling.DatePattern=yyyy-MM-dd'.log' log4j.appender.DailyRolling.Threshold=DEBUGlog4j.appender.DailyRolling.BufferedIO=true log4j.appender.DailyRolling.BufferSize=8192log4j.appender.DailyRolling.MaxFileSize=50MBlog4j.appender.DailyRolling.MaxBackupIndex=100log4j.appender.Terminal=org.apache.log4j.ConsoleAppenderlog4j.appender.Terminal.layout=org.apache.log4j.PatternLayoutlog4j.appender.Terminal.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-7p (%c) %m%nlog4j.logger.httpclient.wire=INFOlog4j.logger.org.apache.commons=INFOlog4j.logger.org.hibernate=WARNlog4j.logger.com.mchange.v2=INFOlog4j.logger.org.springframework=INFOlog4j.logger.org.apache.http=WARNlog4j.logger.org.apache.commons.httpclient=WARNlog4j.logger.com.bj58.bsp=INFOlog4j.logger.com.bj58.spat=INFO########################   # JDBC Appender   #######################  ### stdout ####log4j.appender.stdout=org.apache.log4j.ConsoleAppender#log4j.appender.stdout.Target=System.outlog4j.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.ConsoleAppenderlog4j.appender.database.layout=org.apache.log4j.PatternLayoutlog4j.appender.database.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss:SSS}[%p]: %m%nlog4j.appender.database=org.apache.log4j.jdbc.JDBCAppenderlog4j.appender.database.driver=com.mysql.jdbc.Driverlog4j.appender.database.URL=jdbc:mysql://localhost:3306/esmp?useUnicode=true&characterEncoding=UTF-8log4j.appender.database.user=rootlog4j.appender.database.password=rootlog4j.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

0 0