how to resolve org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
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:
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:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLException: unexpected token: IDENTITY
at org.spri开发者_如何学Cngframework.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$1.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
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();";
The issue is that IDENTITY
is a reserved word in SQL; it's got a meaning pre-defined already and so can't be used like that in a CALL
statement. (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):
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).
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.
The following link has what you need:
http://www.devdaily.com/blog/post/jdbc/spring-jdbc-insert-auto-generated-key
精彩评论