在 INSERT ... SELECT on Oracle 后获取插入的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3261274/
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
Getting inserted ID after INSERT ... SELECT on Oracle
提问by Andrew Swan
This SQL statement works if I run it from my Oracle client (SQL Developer):
如果我从我的 Oracle 客户端（SQL 开发人员）运行此 SQL 语句，则该语句有效：
insert into Person (Name) select 'Bob' from dual
It also works if I issue it via Spring JDBC, without using a KeyHolder:
如果我通过 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);
However I need to use a KeyHolder in order to get the ID of the newly inserted row. If I alter the above code to use a KeyHolder as follows:
但是我需要使用 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);
... then I get this error:
...然后我收到此错误：
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.doInPreparedStatement(JdbcTemplate.java:844)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 15 more
FWIW, everything's fine if I do an INSERT ... VALUES instead of an INSERT ... SELECT (although this doesn't help me, as I need to select things):
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')",
new String[] {"PersonID"});
}
};
jdbcOperations.update(psc, keyHolder);
I'm using:
我正在使用：
- Spring JDBC 3.0.3.RELEASE
- JDBC driver: ojdbc6.jar version 11.2.0.1.0
- RDBMS: Oracle9i Release 9.2.0.5.0 - Production
- commons-dbcp 1.4
- Spring JDBC 3.0.3.RELEASE
- JDBC 驱动：ojdbc6.jar 版本 11.2.0.1.0
- RDBMS：Oracle9i 9.2.0.5.0 版 - 生产
- 公地-dbcp 1.4
N.B. my app needs to use standard SQL in order to remain db-neutral, which rules out any Oracle-specific SQL (I won't be selecting from "dual" in real life).
注意，我的应用程序需要使用标准 SQL 以保持数据库中立，这排除了任何特定于 Oracle 的 SQL（我不会在现实生活中从“双重”中进行选择）。
Thanks for any help.
谢谢你的帮助。
回答by Arthur Ronald
java.sql.Connection.prepareStatement(java.lang.String, int)interface is clear
java.sql.Connection.prepareStatement(java.lang.String, int)接口清晰
Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys
创建一个默认的 PreparedStatement 对象，该对象能够检索自动生成的密钥
So you are using The wrong method. Try
所以你使用了错误的方法。尝试
return con.prepareStatement(
"insert into Person (Name) select 'Bob' from dual",
Statement.RETURN_GENERATED_KEYS);
instead
反而
回答by orbfish
How about
怎么样
INSERT INTO blah b (blah1, blah2, blah3)
VALUES (?, ?, ?) RETURNING b.id INTO ?";
回答by bedrin
This feature isn't supported by Oracle JDBC Driver
Oracle JDBC 驱动程序不支持此功能
回答by Dennis
I suspect using a KeyHolder with an INSERT SELECT statement isn't and won't be supported because the select could theoretically select multiple rows, and if it did there would be no way to return those multiple keys into a single KeyHolder. For what you are trying to accomplish, it will likely be easier to simply use a select statement followed by an insert statement.
我怀疑使用带有 INSERT SELECT 语句的 KeyHolder 不会也不会得到支持，因为理论上选择可以选择多行，如果选择了，则无法将这些多个键返回到单个 KeyHolder 中。对于您要完成的任务，简单地使用 select 语句后跟 insert 语句可能会更容易。
