sql - log4j DB 附加程序抛出异常

Question

我在 log4j.properties 文件中添加了一个数据库附加程序:

log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.URL=jdbc:mysql://${db.url}/mydb
log4j.appender.DB.driver=com.mysql.jdbc.Driver
log4j.appender.DB.user=user
log4j.appender.DB.password=password
log4j.appender.DB.sql=INSERT INTO logs (Date, Logger, Priority, Message) VALUES ('%d', '%c', '%p', '%m')
log4j.appender.DB.layout=org.apache.log4j.PatternLayout
log4j.appender.DB.threshold=INFO

这就是我的表格的样子:

CREATE TABLE `logs` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Date` varchar(30) NOT NULL,
  `Logger` varchar(128) NOT NULL,
  `Priority` varchar(10) NOT NULL,
  `Message` varchar(4096) NOT NULL,
  PRIMARY KEY (`Id`)

部署应用程序后，启动 tomcat 时我看到此异常:

log4j:ERROR Failed to excute sql
com.mysql.jdbc.exceptions.jdbc4.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 '09:49:36,227, org.springframework.web.context.ContextLoader, INFO, Root WebAppli' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
    at com.mysql.jdbc.Util.getInstance(Util.java:378)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3361)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3295)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1852)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1975)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2470)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1370)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1289)
    at org.apache.log4j.jdbc.JDBCAppender.execute(JDBCAppender.java:178)
    at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:250)
    at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:146)
    at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:251)
    at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:66)
    at org.apache.log4j.Category.callAppenders(Category.java:206)
    at org.apache.log4j.Category.forcedLog(Category.java:391)
    at org.apache.log4j.Category.log(Category.java:856)
    at org.slf4j.impl.Log4jLoggerAdapter.log(Log4jLoggerAdapter.java:597)
    at org.apache.commons.logging.impl.SLF4JLocationAwareLog.info(SLF4JLocationAwareLog.java:159)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:273)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:111)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4779)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5273)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:895)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:871)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:615)
    at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:649)
    at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1585)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)

我哪里出错了？

Answer 1

您需要替换所有可能导致 SQL 查询语法错误的特殊字符。请参阅 log4j JDBCAppender JavaDoc对于sqlStatement字段:

Stores the string given to the pattern layout for conversion into a SQL statement, eg: insert into LogTable (Thread, Class, Message) values ("%t", "%c", "%m"). Be careful of quotes in your messages! Also see PatternLayout.

我建议您将自定义数据库附加程序与准备好的语句一起使用。