使用 JDBC 获取 Oracle 11g 的最后一个插入 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4952256/
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
Get last insert id with Oracle 11g using JDBC
提问by geowa4
I'm new to using Oracle so I'm going off what has already been previously answered in this SO question. I just can't seem to get it to work. Here's the statement that I'm using:
我是使用 Oracle 的新手，所以我将结束之前在这个 SO question 中已经回答的内容。我似乎无法让它发挥作用。这是我正在使用的语句：
declare
lastId number;
begin
INSERT INTO "DB_OWNER"."FOO"
(ID, DEPARTMENT, BUSINESS)
VALUES (FOO_ID_SEQ.NEXTVAL, 'Database Management', 'Oracle')
RETURNING ID INTO lastId;
end;
When I call executeQuerythe PreparedStatement that I have made, it inserts everything into the database just fine. However, I cannot seem to figure out how to retrieve the ID. The returned ResultSet object will not work for me. Calling
当我调用executeQuery我制作的 PreparedStatement 时，它会将所有内容插入数据库中。但是，我似乎无法弄清楚如何检索 ID。返回的 ResultSet 对象对我不起作用。打电话
if(resultSet.next()) ...
yields a nasty SQLException that reads:
产生一个讨厌的 SQLException ，内容如下：
Cannot perform fetch on a PLSQL statement: next
无法对 PLSQL 语句执行提取：下一个
How do I get that lastId? Obviously I'm doing it wrong.
我怎么得到那个lastId？显然我做错了。
采纳答案by tbone
make it a function that returns it to you (instead of a procedure). Or, have a procedure with an OUT parameter.
使它成为一个返回给你的函数（而不是一个过程）。或者，有一个带有 OUT 参数的过程。
回答by Jason Gritman
Not sure if this will work, since I've purged all of my computers of anything Oracle, but...
不确定这是否有效，因为我已经清除了所有 Oracle 计算机中的任何内容，但是......
Change your declare to:
将您的声明更改为：
declare
lastId OUT number;
Switch your statement from a PreparedStatement to a CallableStatement by using prepareCall() on your connection. Then register the output parameter before your call, and read it after the update:
通过在您的连接上使用 prepareCall() 将您的语句从 PreparedStatement 切换到 CallableStatement。然后在调用前注册输出参数，更新后读取：
cstmt.registerOutParameter(1, java.sql.Types.NUMERIC);
cstmt.executeUpdate();
int x = cstmt.getInt(1);
回答by Franz
I tried with Oracle driver v11.2.0.3.0 (since there are some bugs in 10.x and 11.1.x, see other blog). Following code works fine:
我尝试使用 Oracle 驱动程序 v11.2.0.3.0（因为 10.x 和 11.1.x 中存在一些错误，请参阅其他博客）。以下代码工作正常：
final String sql = "insert into TABLE(SOME_COL, OTHER_COL) values (?, ?)";
PreparedStatement ps = con.prepareStatement(sql, new String[] {"ID"});
ps.setLong(1, 264);
ps.setLong(2, 1);
int executeUpdate = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next() ) {
// The generated id
long id = rs.getLong(1);
System.out.println("executeUpdate: " + executeUpdate + ", id: " + id);
}
回答by ColinD
You can use Statement.getGeneratedKeys()to do this. You just need to make sure to tell JDBC what columns you want back using one of the method overloads for that, such as the Connection.prepareStatementoverload here:
您可以使用Statement.getGeneratedKeys()来执行此操作。您只需要确保使用其中一种方法重载来告诉 JDBC 您想要返回哪些列，例如Connection.prepareStatement此处的重载：
Connection conn = ...
PreparedStatement pS = conn.prepareStatement(sql, new String[]{"id"});
pS.executeUpdate();
ResultSet rS = pS.getGeneratedKeys();
if (rS.next()) {
long id = rS.getLong("id");
...
}
You don't need to do the RETURNING x INTOstuff with this, just use the basic SQL statement you want.
你不需要用它来做这些RETURNING x INTO事情，只需使用你想要的基本 SQL 语句。
回答by jzd
When you prepare the statement set the second parameter to RETURN_GENERATED_KEYS. Then you should be able to get a ResultSetoff the statement object.
准备语句时，将第二个参数设置为RETURN_GENERATED_KEYS. 然后你应该能够得到一个ResultSet关闭语句对象。
回答by RealHowTo
Are you doing that in a stored procedure ? According to this Oracle document, it won't work with the server-side driver.
你是在存储过程中这样做吗？根据这个Oracle 文档，它不适用于服务器端驱动程序。
The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature.
