如果我从Oracle客户端(sql Developer)运行它,则此sql语句有效:
insert into Person (Name) select 'Bob' from dual
如果我通过Spring JDBC发布它,而不使用KeyHolder,它也可以工作:
final PreparedStatementCreator psc = new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws sqlException
{
return con.prepareStatement(
"insert into Person (Name) select 'Bob' from dual");
}
};
jdbcOperations.update(psc);
但是我需要使用KeyHolder来获取新插入的行的ID.如果我改变上面的代码使用KeyHolder如下:
final KeyHolder keyHolder = new GeneratedKeyHolder();
final PreparedStatementCreator psc = new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws sqlException
{
return con.prepareStatement(
"insert into Person (Name) select 'Bob' from dual",new String[] {"PersonID"});
}
};
jdbcOperations.update(psc,keyHolder);
…然后我收到此错误:
Exception in thread "main" org.springframework.jdbc.BadsqlGrammarException: PreparedStatementCallback; bad sql grammar []; nested exception is java.sql.sqlSyntaxErrorException: ORA-00933: sql command not properly ended
at org.springframework.jdbc.support.sqlExceptionSubclassTranslator.doTranslate(sqlExceptionSubclassTranslator.java:94)
at org.springframework.jdbc.support.AbstractFallbacksqlExceptionTranslator.translate(AbstractFallbacksqlExceptionTranslator.java:72)
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:842)
at au.com.bisinfo.codecombo.logic.ImportServiceImpl.insertLoginRedirectRule(ImportServiceImpl.java:107)
at au.com.bisinfo.codecombo.logic.ImportServiceImpl.runImport(ImportServiceImpl.java:68)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy8.runImport(Unknown Source)
at au.com.bisinfo.codecombo.ui.Main.main(Main.java:39)
Caused by: java.sql.sqlSyntaxErrorException: ORA-00933: sql command not properly ended
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:844)
at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 15 more
FWIW,一切都很好,如果我做一个INSERT … VALUES而不是INSERT … SELECT(虽然这对我没有帮助,因为我需要选择东西):
final KeyHolder keyHolder = new GeneratedKeyHolder();
final PreparedStatementCreator psc = new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws sqlException
{
return con.prepareStatement(
"insert into Person (Name) values ('Bob')",keyHolder);
我正在使用:
> Spring JDBC 3.0.3.RELEASE
> JDBC驱动程序:ojdbc6.jar版本11.2.0.1.0
> RDBMS:Oracle9i版本9.2.0.5.0 – 生产
> commons-dbcp 1.4
注:我的应用程序需要使用标准sql才能保持数据库中立,这排除了任何特定于Oracle的sql(我不会在现实生活中选择“双重”).
谢谢你的帮助.
最佳答案
java.sql.Connection.prepareStatement(java.lang.String,int)界面清晰
Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys@H_404_62@
return con.prepareStatement(
"insert into Person (Name) select 'Bob' from dual",Statement.RETURN_GENERATED_KEYS);
代替