Java 如何从最后插入的行中获取值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/241003/
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
How to get a value from the last inserted row?
提问by eflles
Is there some way to get a value from the last inserted row?
有没有办法从最后插入的行中获取值？
I am inserting a row where the PK will automatically increase, and I would like to get this PK. Only the PK is guaranteed to be unique in the table.
我在插入一行 PK 会自动增加，我想得到这个 PK。只有 PK 才能保证在表中是唯一的。
I am using Java with a JDBC and PostgreSQL.
我在 JDBC 和 PostgreSQL 中使用 Java。
采纳答案by Luc M
With PostgreSQL you can do it via the RETURNING keyword:
使用 PostgreSQL，您可以通过 RETURNING 关键字来完成：
INSERT INTO mytable( field_1, field_2,... )
VALUES ( value_1, value_2 ) RETURNING anyfield
It will return the value of "anyfield". "anyfield" may be a sequence or not.
它将返回“anyfield”的值。“anyfield”可能是一个序列，也可能不是。
To use it with JDBC, do:
要将其与 JDBC 一起使用，请执行以下操作：
ResultSet rs = statement.executeQuery("INSERT ... RETURNING ID");
rs.next();
rs.getInt(1);
回答by svrist
The sequences in postgresql are transaction safe. So you can use the
postgresql 中的序列是事务安全的。所以你可以使用
currval(sequence)
currval
Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer even if other sessions are executing nextval meanwhile.
电流
返回 nextval 在当前会话中为该序列最近获得的值。（如果在此会话中从未为此序列调用过 nextval，则会报告错误。）请注意，因为它返回的是会话本地值，因此即使其他会话同时正在执行 nextval，它也会给出可预测的答案。
回答by BradC
Use sequences in postgres for id columns:
在 id 列中使用 postgres 中的序列：
INSERT mytable(myid) VALUES (nextval('MySequence'));
SELECT currval('MySequence');
currval will return the current value of the sequence in the same session.
currval 将返回同一会话中序列的当前值。
(In MS SQL, you would use @@identity or SCOPE_IDENTITY())
（在 MS SQL 中，您将使用 @@identity 或 SCOPE_IDENTITY()）
回答by Andrew Watt
See the API docs for java.sql.Statement.
请参阅java.sql.Statement的 API 文档。
Basically, when you call executeUpdate()or executeQuery(), use the Statement.RETURN_GENERATED_KEYSconstant. You can then call getGeneratedKeysto get the auto-generated keys of all rows created by that execution. (Assuming your JDBC driver provides it.)
基本上，当您调用executeUpdate()or 时executeQuery()，请使用Statement.RETURN_GENERATED_KEYS常量。然后，您可以调用getGeneratedKeys以获取该执行创建的所有行的自动生成的键。（假设您的 JDBC 驱动程序提供了它。）
It goes something along the lines of this:
它是这样的：
Statement stmt = conn.createStatement();
stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keyset = stmt.getGeneratedKeys();
回答by anjanb
If you're using JDBC 3.0, then you can get the value of the PK as soon as you inserted it.
如果您使用的是 JDBC 3.0，那么您可以在插入后立即获取 PK 的值。
Here's an article that talks about how : https://www.ibm.com/developerworks/java/library/j-jdbcnew/
这是一篇讨论如何操作的文章：https: //www.ibm.com/developerworks/java/library/j-jdbcnew/
Statement stmt = conn.createStatement();
// Obtain the generated key that results from the query.
stmt.executeUpdate("INSERT INTO authors " +
"(first_name, last_name) " +
"VALUES ('George', 'Orwell')",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if ( rs.next() ) {
// Retrieve the auto generated key(s).
int key = rs.getInt(1);
}
回答by eflles
Here is how I solved it, based on the answers here:
根据这里的答案，这是我解决它的方法：
Connection conn = ConnectToDB(); //ConnectToDB establishes a connection to the database.
String sql = "INSERT INTO \"TableName\"" +
"(\"Column1\", \"Column2\",\"Column3\",\"Column4\")" +
"VALUES ('value1',value2, 'value3', 'value4') RETURNING
\"TableName\".\"TableId\"";
PreparedStatement prpState = conn.prepareStatement(sql);
ResultSet rs = prpState.executeQuery();
if(rs.next()){
System.out.println(rs.getInt(1));
}
回答by smilek
Don't use SELECT currval('MySequence') - the value gets incremented on inserts that fail.
不要使用 SELECT currval('MySequence') - 该值在插入失败时增加。
回答by BalusC
Since PostgreSQL JDBC driver version 8.4-701the PreparedStatement#getGeneratedKeys()is finally fully functional. We use it here almost one year in production to our full satisfaction.
自 PostgreSQL JDBC 驱动程序版本8.4-701 以来，PreparedStatement#getGeneratedKeys()它终于功能齐全。我们在这里使用它将近一年的生产令我们非常满意。
In "plain JDBC" the PreparedStatementneeds to be created as follows to make it to return the keys:
在“普通 JDBC”中，PreparedStatement需要按如下方式创建以使其返回键：
statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
You can download the current JDBC driver version here(which is at the moment still 8.4-701).
您可以在此处下载当前的 JDBC 驱动程序版本（目前仍为 8.4-701）。
回答by abdurrahim dagdelen
PreparedStatement stmt = getConnection(PROJECTDB + 2)
.prepareStatement("INSERT INTO fonts (font_size) VALUES(?) RETURNING fonts.*");
stmt.setString(1, "986");
ResultSet res = stmt.executeQuery();
while (res.next()) {
System.out.println("Generated key: " + res.getLong(1));
System.out.println("Generated key: " + res.getInt(2));
System.out.println("Generated key: " + res.getInt(3));
}
stmt.close();
回答by emicklei
For MyBatis 3.0.4 with Annotations and Postgresql driver 9.0-801.jdbc4 you define an interface method in your Mapper like
对于带有注释和 Postgresql 驱动程序 9.0-801.jdbc4 的 MyBatis 3.0.4，您在 Mapper 中定义一个接口方法，例如
public interface ObjectiveMapper {
@Select("insert into objectives" +
" (code,title,description) values" +
" (#{code}, #{title}, #{description}) returning id")
int insert(Objective anObjective);
Note that @Select is used instead of @Insert.
请注意，使用@Select 而不是@Insert。
