Java 获取 Oracle DB 中插入行的最后一个 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4458734/
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 ID on inserted row in Oracle DB
提问by Igor
Possible Duplicate:
PLSQL JDBC: How to get last row ID?
I have problem getting ID from tables. I have two tables AJPES_TR and TR_LOG and PK from TR_LOG table is set as foreign key in AJPES_TR table.
我在从表中获取 ID 时遇到问题。我有两个表 AJPES_TR 和 TR_LOG，TR_LOG 表中的 PK 被设置为 AJPES_TR 表中的外键。
In TR_LOG table I just write from which file data was imported and I want to link that PK into main table. In mySQL I was doing just fine with getID.last(); int j = getID.getInt(TR_LOG_ID);but now in Oracle this doesn't work anymore.
在 TR_LOG 表中，我只是写入从中导入数据的文件，并且我想将该 PK 链接到主表中。在 mySQL 中我做得很好，getID.last(); int j = getID.getInt(TR_LOG_ID);但现在在 Oracle 中这不再起作用了。
These are my PreparedStatements:
这些是我的 PreparedStatement：
PreparedStatement insertData =
con.prepareStatement(
"INSERT INTO T_AJPES_TR(rn,sSpre,reg,eno,davcna,Ime,Priimek) VALUES (?,?,?,?,?,?,?)"
);
PreparedStatement select_file_log =
con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG WHERE File_import = ?"
);
PreparedStatement getID = con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG");
PreparedStatement insertFile =
con.prepareStatement(
"INSERT INTO T_AJPES_TR_LOG(Date_import,File_import) VALUES (?,?)"
);
In mySQL IDs were set as autoincrement.
在 mySQL 中，ID 被设置为自动增量。
How can I get ID value from TR_LOG and write that value in AJPES_TR table?
如何从 TR_LOG 获取 ID 值并将该值写入 AJPES_TR 表中？
回答by Michael Pakhantsov
回答by SirMad
You are able to signal the auto increment columns in the prepare statement call
您可以在准备语句调用中发出自动增量列的信号
Example:
例子：
PreparedStatement ps = con.prepareStatement(sql, pkColumns);
After the insert of the database row:
插入数据库行后：
ResultSet keys = ps.getGeneratedKeys();
Important: This only works, if the auto-increment values has been automatically set by a DB-Trigger via sequences
重要提示：这仅适用于自动增量值已由 DB-Trigger 通过序列自动设置的情况
回答by andr
If I understood you properly you want to insert a record in one table T1, get PK of the inserted record and use is as a FK in another table T2. In this case Oracle returning clauseis very useful, you can use it like this (I'm sorry I don't know hot to use it in Java, but you should get the idea):
如果我正确理解您，您想在一个表 T1 中插入一条记录，获取插入记录的 PK 并在另一个表 T2 中用作 FK。在这种情况下，Oracle返回子句非常有用，您可以像这样使用它（对不起，我不知道在 Java 中使用它很热，但您应该明白了）：
declare
fId int;
begin
insert into T1(id) values seq1.nextval returning id into fId
end;
After the insert you'll have the created record id in the fIdvariable.
插入后，您将在fId变量中拥有创建的记录 ID 。
回答by Matthew Rodatus
If a trigger is configured to automatically set the primary key field with the next value from a sequence, then you can modify your INSERT statement as follows:
如果触发器配置为使用序列中的下一个值自动设置主键字段，则可以按如下方式修改 INSERT 语句：
INSERT INTO table (field1, field2, field3)
VALUES (?, ?, ?)
RETURNING primary_key_field INTO ?
Then, add the parameter values for the INSERT, an output parameter at the end for the primary key, and execute the query.
然后，添加 INSERT 的参数值，主键末尾的输出参数，并执行查询。
After the query is executed, grab the value of the output parameter. It should contain the value of the primary_key_field.
执行查询后，抓取输出参数的值。它应该包含primary_key_field 的值。
