java EclipseLink MySQLSyntaxErrorException找不到错误

8 月，2 周 Questions & Answers 367

我有一个这样运行的代码：

S3Logs s3Logs = new S3Logs();
                            s3Logs.setBucketOwner(a.getBucketOwner());
                            s3Logs.setBucket(a.getBucket());
                            s3Logs.setTime(a.getTime().toString());
                            s3Logs.setRemoteIpAddress(a.getRemoteIpAddress());
                            s3Logs.setRequester(a.getRequester());
                            s3Logs.setRequestId(a.getRequestId());
                            s3Logs.setOperation(a.getOperation());
                            s3Logs.setKey(a.getKey());
                            s3Logs.setRequestUri(a.getRequestUri());
                            s3Logs.setHttpStatus(a.getHttpStatus());
                            s3Logs.setErrorCode(a.getErrorCode());
                            s3Logs.setBytesSent(a.getBytesSent());
                            s3Logs.setObjectSize(a.getObjectSize());
                            s3Logs.setTotalTime(a.getTotalTime());
                            s3Logs.setTurnAroundTime(a.getTurnAroundTime());
                            if(a.getReferrer() == "-"){
                                s3Logs.setReferrer(null);
                            }else{
                            s3Logs.setReferrer(a.getReferrer());}
                            if(a.getUserAgent().contains(",")){
                                String s = a.getUserAgent().replace(",", "");
                                s3Logs.setUserAgent(s);
                            }else{
                                s3Logs.setUserAgent(a.getUserAgent());
                            }

                            s3Logs.setVersionId(a.getVersionId());
                            LogsDAO.em.persist(s3Logs);
                            LogsDAO.em.getTransaction().commit();

但当我尝试运行时，它给了我这样的错误：

Internal Exception: 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 'KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, RE' at line 1
Error Code: 1064
Call: INSERT INTO S3LOGS (BUCKET, BUCKETOWNER, BYTESSENT, ERRORCODE, EXTRAS, HTTPSTATUS, KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, REQUESTER, TIME, TOTALTIME, TURNAROUNDTIME, USERAGENT, VERSIONID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [seminar2017, 5abc648552c5f, 0, null, null, 200, null, 0, REST.PUT.LOGGING_STATUS, -, 10.0.0.100, C785888F31E34747, PUT /seminar2017?logging= HTTP/1.1, 5abc648552c5f, 2017-05-10T15:42:40.000Z, 198, 0, aws-internal/3 S3Console/0.4, null]
Query: InsertObjectQuery(de.logsparsing.S3Logs@74ea2410)
Exception in thread "main" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, RE' at line 1
Error Code: 1064
Call: INSERT INTO S3LOGS (BUCKET, BUCKETOWNER, BYTESSENT, ERRORCODE, EXTRAS, HTTPSTATUS, KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, REQUESTER, TIME, TOTALTIME, TURNAROUNDTIME, USERAGENT, VERSIONID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [cpabecloudseminar2017, 5abc648552c5f, 0, null, null, 200, null, 0, REST.PUT.LOGGING_STATUS, -, 54.239.6.71, C785888F31E34747, PUT /seminar2017?logging= HTTP/1.1, 5abc648552c5f, 2017-05-10T15:42:40.000Z, 198, 0, aws-internal/3 S3Console/0.4, null]
Query: InsertObjectQuery(de.logsparsing.S3Logs@74ea2410)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:159)
    at de.awsmonitor.AWSLogsMapper.main(AWSLogsMapper.java:176)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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 'KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, RE' at line 1
Error Code: 1064
Call: INSERT INTO S3LOGS (BUCKET, BUCKETOWNER, BYTESSENT, ERRORCODE, EXTRAS, HTTPSTATUS, KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, REQUESTER, TIME, TOTALTIME, TURNAROUNDTIME, USERAGENT, VERSIONID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [seminar2017, 5abc648552c5f291cc61eb5cbcb347c3074ed816d2a8bd140e598b3e30595338, 0, null, null, 200, null, 0, REST.PUT.LOGGING_STATUS, -, 10.0.0.100, C785888F31E34747, PUT /seminar2017?logging= HTTP/1.1, 5abc648552c5f291cc61eb5cbcb347c3074ed816d2a8bd140e598b3e30595338, 2017-05-10T15:42:40.000Z, 198, 0, aws-internal/3 S3Console/0.4, null]
Query: InsertObjectQuery(de.logsparsing.S3Logs@74ea2410)
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:377)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:803)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1790)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:227)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:126)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4264)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:278)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169)
    at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:134)
    ... 1 more
Caused by: 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 'KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, RE' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:892)
    ... 32 more

我试图找出错误，但直到现在才发现。我甚至尝试实例化S3Log对象而不在其中放入任何内容，但它仍然给出相同的错误

这是我的S3Log对象：

@Entity
public class S3Logs {

    public S3Logs() {

    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    public long id;

    private String bucketOwner;
    private String bucket;
    private String time;
    private String remoteIpAddress;
    private String requester;
    private String requestId;
    private String operation;
    private String key;
    private String requestUri;
    private int httpStatus;
    private String errorCode;
    private long bytesSent;
    private long objectSize;
    private long totalTime;
    private long turnAroundTime;
    private String referrer;
    private String userAgent;
    private String versionId;
}

共 (1) 个答案

# 1 楼答案
问题

MySQL向您提示了问题所在，如下所示：

Error Code: 1064

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

'KEY, OBJECTSIZE, OPERATION, REFERRER, REMOTEIPADDRESS, REQUESTID, REQUESTURI, RE' at line 1

KEY是MySQL中的保留字。并且保留字不能用作标识符（例如表名、列名）

若要使用保留字作为标识符（或使用包含无效字符的字符串，如空格），可以通过将标识符包含在反勾号字符中来转义该标识符

例如：
```
INSERT INTO `mytable` (`mycol`, `key`, ... 
            ^       ^  ^     ^  ^   ^
```
修复程序

解决此问题的一种快速方法是在源代码中添加一个JPA 2.0指令，以覆盖默认列名，并指定包含在反勾号中的列名，如下所示：
```
private String operation;
@Column(name="`key`")
private String key;
private String requestUri;
```
将列名更改为保留字以外的其他名称将是另一个（首选）选择

有些人会（正确地）认为这是一个特定于MySQL的修复程序。（MySQL使用单反勾号，SQL Server使用方括号转义标识符，Oracle使用双引号，等等），因此这个“修复”与数据库的可移植性背道而驰

JPA配置中应该有一些常规设置，有效地表示“转义所有标识符”，这不是特定于数据库的

Python中文网

有 Java 编程相关的问题?

java EclipseLink MySQLSyntaxErrorException找不到错误

共 (1) 个答案

# 1 楼答案