DB中上次插入行的值 [英] Value from last inserted row in DB

问题描述

有没有办法从最后一个插入行中获取值？

Is there some way to get a value from the last inserted row?

我插入一行，其中PK将由于序列创建而自动增加，想得到这个序列号。只有PK在表中保证是唯一的。

I am inserting a row where the PK will automatically increase due to sequence created, and I would like to get this sequence number. Only the PK is guaranteed to be unique in the table.

我使用Java和JDBC和Oracle。

I am using Java with a JDBC and Oracle.

我忘了添加，我想检索这个值使用下面的结果集。 （我已经尝试这与mysql和它的工作成功，但我不得不切换到Oracle，现在我得到一个字符串表示的ID，而不是实际的序列号）

I forgot to add that I would like to retrieve this value using the resultset below. (I have tried this with mysql and it worked successfully, but I had to switch over to Oracle and now I get a string representation of the ID and not the actually sequence number)

Statement stmt = conn.createStatement();
stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
stmt.RETURN_GENERATED_KEYS;
ResultSet rs = stmt.getGeneratedKeys();
if(rs.next()){
   log.info("Successful insert");
   id = rs.getString(1);
}

上述代码段将返回存储在mysql表中的int值。但是，由于我已经切换到Oracle，所返回的值现在是一个奇怪的字符串值。

The above snippet would return the column int value stored in a mysql table. But since I have switched over to Oracle, the value returned is now a strange string value.

推荐答案

do是利用 RETURNING 子句。让我们设置一个示例表和序列：

What you're trying to do is take advantage of the RETURNING clause. Let's setup an example table and sequence:

CREATE TABLE "TEST" 
( "ID" NUMBER NOT NULL ENABLE, 
 "NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, 
  CONSTRAINT "PK_TEST" PRIMARY KEY ("ID")
  );

CREATE SEQUENCE SEQ_TEST;

现在，您的Java代码应如下所示：

Now, your Java code should look like this:

String insertSql = "BEGIN INSERT INTO TEST (ID, NAME) VALUES (SEQ_TEST.NEXTVAL(), ?) RETURNING ID INTO ?; END;";
java.sql.CallableStatement stmt = conn.prepareCall(insertSql);
stmt.setString(1, "John Smith");
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.execute();
int id = stmt.getInt(2);

这篇关于DB中上次插入行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！