SQL 如何解决 org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5190252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to resolve org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
提问by Chitresh
I am using HSQLDB as my database. i want to get a primary key of latest inserted row. for that i have return a query in my java class as below:
我使用 HSQLDB 作为我的数据库。我想获取最新插入行的主键。为此，我在我的 java 类中返回了一个查询，如下所示：
final String query = "INSERT INTO polling_log (start_date,status,action) VALUES(CURRENT_TIMESTAMP,?,?); CALL IDENTITY();";
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
int update = adapterJdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(
Connection connection) throws SQLException {
PreparedStatement preparedStatement = connection
.prepareStatement(query);
preparedStatement.setInt(1, pollingLogVO.getStatus());
preparedStatement.setString(2, pollingLogVO.getAction());
System.out.println(preparedStatement.getGeneratedKeys().getFetchSize());
return preparedStatement;
}
}, generatedKeyHolder);
System.out.println("###################### "+ update);
Number logId = generatedKeyHolder.getKey();
pollingLogId = logId.intValue();
and to store the query i have used GeneratedKeyHolder. but while runing this i get an exception:
并存储我使用 GeneratedKeyHolder 的查询。但是在运行这个时我得到了一个例外：
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLException: unexpected token: IDENTITY
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:817)
at com.platysgroup.lmex.adapter.moodle.dao.LogDao.insertPollingLog(LogDao.java:36)
at com.platysgroup.lmex.adapter.MoodlePostingTask.insertPollingLog(MoodlePostingTask.java:134)
at com.platysgroup.lmex.adapter.MoodlePostingTask.run(MoodlePostingTask.java:55)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)
Caused by: java.sql.SQLException: unexpected token: IDENTITY
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
at com.platysgroup.lmex.adapter.moodle.dao.LogDao.createPreparedStatement(LogDao.java:41)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:580)
... 6 more
Caused by: org.hsqldb.HsqlException: unexpected token: IDENTITY
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 12 more
采纳答案by Donal Fellows
The problem is with this line (wrapped for clarity):
问题出在这一行（为清楚起见而包装）：
final String query = "INSERT INTO polling_log (start_date,status,action) VALUES(CURRENT_TIMESTAMP,?,?); CALL IDENTITY();";
final String query = "INSERT INTO polling_log (start_date,status,action) VALUES(CURRENT_TIMESTAMP,?,?); CALL IDENTITY();";
The issue is that IDENTITYis a reserved word in SQL; it's got a meaning pre-defined already and so can't be used like that in a CALLstatement. (I don't know what it is actually used for; the full definition of SQL is huge and has a very large number of reserved words.) The immediate work around would be to enclose the problem word in double quotes (which would need to be backslash-quoted because of being in a Java string):
问题是它IDENTITY是 SQL 中的保留字；它已经有一个预定义的含义，所以不能像这样在CALL语句中使用。（我不知道它的实际用途；SQL 的完整定义非常庞大，并且有大量的保留字。）直接的解决方法是将问题词括在双引号中（这需要用双引号括起来）因为在 Java 字符串中而被反斜杠引用）：
final String query = "INSERT INTO polling_log (start_date,status,action) VALUES(CURRENT_TIMESTAMP,?,?); CALL \"IDENTITY\"();";
final String query = "INSERT INTO polling_log (start_date,status,action) VALUES(CURRENT_TIMESTAMP,?,?); CALL \"IDENTITY\"();";
However, if you're just calling that to get the inserted row, STOP!Just let Spring do the work for you, assuming you have JDBC 3.0 or later (i.e., Java 5 or later).
但是，如果您只是调用它来获取插入的行，请停止！假设您有 JDBC 3.0 或更高版本（即 Java 5 或更高版本），让 Spring 为您完成工作。
回答by jabal
As far as I know you cannot put more than one statements into one string to be executed. Execute two separate operations instead of this.
据我所知，您不能将多个语句放入一个要执行的字符串中。执行两个单独的操作而不是这个。
回答by digitalsanctum
The following link has what you need:
以下链接包含您需要的内容：
http://www.devdaily.com/blog/post/jdbc/spring-jdbc-insert-auto-generated-key
http://www.devdaily.com/blog/post/jdbc/spring-jdbc-insert-auto-generated-key
