oracle 数据库中最后插入行的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1976625/
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
Value from last inserted row in DB
提问by Ruepen
Is there some way to get a value from the last inserted row?
有没有办法从最后插入的行中获取值？
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.
我正在插入一行，其中 PK 会因创建的序列而自动增加，我想获取此序列号。只有 PK 才能保证在表中是唯一的。
I am using Java with a JDBC and Oracle.
我在 JDBC 和 Oracle 中使用 Java。
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)
我忘了补充说我想使用下面的结果集检索这个值。（我已经在 mysql 上尝试过这个并且成功了，但是我不得不切换到 Oracle，现在我得到了 ID 的字符串表示，而不是实际的序列号）
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);
}
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.
上面的代码片段将返回存储在 mysql 表中的列 int 值。但是由于我已经切换到 Oracle，返回的值现在是一个奇怪的字符串值。
采纳答案by Adam Hawkes
What you're trying to do is take advantage of the RETURNINGclause. Let's setup an example table and sequence:
您要做的是利用该RETURNING条款。让我们设置一个示例表和序列：
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;
Now, your Java code should look like this:
现在，您的 Java 代码应如下所示：
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);
回答by Pascal Thivent
This is not consistent with other databases but, when using Oracle, getGeneratedKeys()returns the ROWID for the inserted row when using Statement.RETURN_GENERATEDKEYS. So you need to use the oracle.sql.ROWIDproprietary type to "read" it:
这与其他数据库不一致，但在使用 Oracle 时，使用 时getGeneratedKeys()返回插入行的 ROWID Statement.RETURN_GENERATEDKEYS。所以你需要使用oracle.sql.ROWID专有类型来“读取”它：
Statement stmt = connection.createStatement();
stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
oracle.sql.ROWID rid = (oracle.sql.ROWID) rs.getObject(1);
But this won't give you the generated ID of the PK. When working with Oracle, you should either use the method executeUpdate(String sql, int[] columnIndexes)or executeUpdate(String sql, String[] columnNames)instead of executeUpdate(String sql, int autoGeneratedKeys)to get the generated sequence value. Something like this (adapt the value to match the index or the name of your primary key column):
但这不会给你生成的 PK ID。使用 Oracle 时，您应该使用 方法executeUpdate(String sql, int[] columnIndexes)或executeUpdate(String sql, String[] columnNames)代替executeUpdate(String sql, int autoGeneratedKeys)获取生成的序列值。像这样（调整值以匹配索引或主键列的名称）：
stmt.executeUpdate(INSERT_SQL, new int[] {1});
ResultSet rs = stmt.getGeneratedKeys();
Or
或者
stmt.executeUpdate(INSERT_SQL, new String[] {"ID"});
ResultSet rs = stmt.getGeneratedKeys();
While digging a bit more on this, it appears that this approach is shown in the Spring documentation(as mentioned here) so, well, I guess it can't be totally wrong. But, unfortunately, it is not really portable and it may not work on other platforms.
虽然挖多一点就这一点，看来，这种做法是在显示Spring文档（如提到这里），所以，好了，我想这不可能是完全错误的。但是，不幸的是，它并不是真正可移植的，它可能无法在其他平台上运行。
回答by BalusC
You should use ResultSet#getLong()instead. If in vain, try ResultSet#getRowId()and eventually cast it to oracle.sql.ROWID. If the returned hex string is actually the ID in hexadecimal flavor, then you can try converting it to decimal by Long#valueOf()or Integer#valueOf().
你应该ResultSet#getLong()改用。如果徒劳无功，请尝试ResultSet#getRowId()并最终将其转换为oracle.sql.ROWID. 如果返回的十六进制字符串实际上是十六进制风格的ID，那么您可以尝试将其转换为十进制Long#valueOf()或Integer#valueOf()。
Long id = Long.valueOf(hexId, 16);
That said, Oracle's JDBC driver didn't support ResultSet#getGeneratedKeys()for a long time and is still somewhat troublesome with it. If you can't get that right, then you need to execute a SELECT CURRVAL(sequencename)on the same statement as you did the insert, or a new statement inside the same transaction, if it was a PreparedStatement. Basic example:
就是说，Oracle的JDBC驱动ResultSet#getGeneratedKeys()很久不支持了，还是有点麻烦。如果你不能得到这种权利，那么你需要执行SELECT CURRVAL(sequencename)同一语句，你做了insert相同的事务中，还是一个新的语句，如果这是一个PreparedStatement。基本示例：
public void create(User user) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
Statement statement = null;
ResultSet generatedKeys = null;
try {
connection = daoFactory.getConnection();
preparedStatement = connection.prepareStatement(SQL_INSERT);
preparedStatement.setValue(1, user.getName());
// Set more values here.
int affectedRows = preparedStatement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating user failed, no rows affected.");
}
statement = connection.createStatement();
generatedKeys = statement.executeQuery(SQL_CURRVAL);
if (generatedKeys.next()) {
user.setId(generatedKeys.getLong(1));
} else {
throw new SQLException("Creating user failed, no generated key obtained.");
}
} finally {
close(generatedKeys);
close(statement);
close(preparedStatement);
close(connection);
}
}
Oh, from your code example, the following line
哦，从您的代码示例中，以下行
stmt.RETURN_GENERATED_KEYS;
is entirely superfluous. Remove it.
完全是多余的。去掉它。
You can find hereanother example which I posted before about getting the generated keys, it uses the normal getGeneratedKeys()approach.
您可以在此处找到我之前发布的关于获取生成的密钥的另一个示例，它使用常规getGeneratedKeys()方法。
