获取Oracle DB中插入行的最后一个ID [英] Get last ID on inserted row in Oracle DB

问题描述

可能重复：

PLSQL JDBC：如何获取最后一行ID？

我从表中获取ID时遇到问题。我有两个表AJPES_TR和TR_LOG，TR_LOG表中的PK被设置为AJPES_TR表中的外键。

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.

在TR_LOG表中，我只是编写导入的文件数据，我想将该PK链接到主表中。在mySQL中我用 getID.last（）做得很好; int j = getID.getInt（TR_LOG_ID）; 但现在在Oracle中这不再起作用了。

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.

这些是我的PreparedStatements：

These are my PreparedStatements:

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 (?,?)"
  );

在mySQL ID中设置为autoincrement。

In mySQL IDs were set as autoincrement.

如何从TR_LOG获取ID值并在AJPES_TR表中写入该值？

How can I get ID value from TR_LOG and write that value in AJPES_TR table?

推荐答案

如果触发器配置为使用序列中的下一个值自动设置主键字段，那么您可以修改INSERT语句如下：

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 INTO table (field1, field2, field3)
  VALUES (?, ?, ?)
  RETURNING primary_key_field INTO ?

然后，添加INSERT的参数值，这是主键末尾的输出参数，并执行查询。

Then, add the parameter values for the INSERT, an output parameter at the end for the primary key, and execute the query.

执行查询后，获取输出参数的值。它应该包含primary_key_field的值。

After the query is executed, grab the value of the output parameter. It should contain the value of the primary_key_field.

这篇关于获取Oracle DB中插入行的最后一个ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！