oracle PLSQL JDBC：如何获取最后一行 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3552260/
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
PLSQL JDBC: How to get last row ID?
提问by Haoest
What's PLSQL (Oracle) equivalent of this SQL server snippet?
这个 SQL 服务器片段的 PLSQL (Oracle) 等价物是什么？
BEGIN TRAN
INSERT INTO mytable(content) VALUES ("test") -- assume there's an ID column that is autoincrement
SELECT @@IDENTITY
COMMIT TRAN
In C#, you can call myCommand.ExecuteScalar() to retrieve the ID of the new row.
在 C# 中，您可以调用 myCommand.ExecuteScalar() 来检索新行的 ID。
How can I insert a new row in Oracle, and have JDBC get a copy of the new id?
如何在 Oracle 中插入新行，并让 JDBC 获取新 ID 的副本？
EDIT:BalusC provided a very good starting point. For some reason JDBC doesn't like named parameter binding. This gives "Incorrectly set or registered parameters" SQLException. Why is this happening?
编辑：BalusC 提供了一个很好的起点。出于某种原因，JDBC 不喜欢命名参数绑定。这给出了“错误设置或注册的参数”SQLException。为什么会这样？
OracleConnection conn = getAppConnection();
String q = "BEGIN INSERT INTO tb (id) values (claim_seq.nextval) returning id into :newId; end;" ;
CallableStatement cs = (OracleCallableStatement) conn.prepareCall(q);
cs.registerOutParameter("newId", OracleTypes.NUMBER);
cs.execute();
int newId = cs.getInt("newId");
回答by BalusC
Normallyyou would use Statement#getGeneratedKeys()for this (see also this answerfor an example), but this is as far (still) not supported by the Oracle JDBC driver.
通常，您会为此使用Statement#getGeneratedKeys()它（另请参阅此答案的示例），但 Oracle JDBC 驱动程序目前（仍然）不支持此功能。
Your best bet is to eithermake use of CallableStatementwith a RETURNINGclause:
最好的办法是使用CallableStatementwithRETURNING子句：
String sql = "BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) RETURNING id INTO ?; END;";
Connection connection = null;
CallableStatement statement = null;
try {
connection = database.getConnection();
statement = connection.prepareCall(sql);
statement.setString(1, "test");
statement.registerOutParameter(2, Types.NUMERIC);
statement.execute();
int id = statement.getInt(2);
// ...
Orfire SELECT sequencename.CURRVALafter INSERTin the same transaction:
或者在同一个事务SELECT sequencename.CURRVAL之后触发INSERT：
String sql_insert = "INSERT INTO mytable(content) VALUES (?)";
String sql_currval = "SELECT seq_mytable.CURRVAL FROM dual";
Connection connection = null;
PreparedStatement statement = null;
Statement currvalStatement = null;
ResultSet currvalResultSet = null;
try {
connection = database.getConnection();
connection.setAutoCommit(false);
statement = connection.prepareStatement(sql_insert);
statement.setString(1, "test");
statement.executeUpdate();
currvalStatement = connection.createStatement();
currvalResultSet = currvalStatement.executeQuery(sql_currval);
if (currvalResultSet.next()) {
int id = currvalResultSet.getInt(1);
}
connection.commit();
// ...
回答by Pablo Santa Cruz
回答by finejustice
You can use getGeneratedKeys(), By explicitly selecting key field. Here is a snippet:
您可以使用 getGeneratedKeys()，通过显式选择键字段。这是一个片段：
// change the string to your connection string
Connection connection = DriverManager.getConnection("connection string");
// assume that the field "id" is PK, and PK-trigger exists
String sql = "insert into my_table(id) values (default)";
// you can select key field by field index
int[] colIdxes = { 1 };
// or by field name
String[] colNames = { "id" };
// Java 1.7 syntax; try-finally for older versions
try (PreparedStatement preparedStatement = connection.prepareStatement(sql, colNames))
{
// note: oracle JDBC driver do not support auto-generated key feature with batch update
// // insert 5 rows
// for (int i = 0; i < 5; i++)
// {
// preparedStatement.addBatch();
// }
//
// int[] batch = preparedStatement.executeBatch();
preparedStatement.executeUpdate();
// get generated keys
try (ResultSet resultSet = preparedStatement.getGeneratedKeys())
{
while (resultSet.next())
{
// assume that the key's type is BIGINT
long id = resultSet.getLong(1);
assertTrue(id != 0);
System.out.println(id);
}
}
}
refer for details: http://docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm#CHDEGDHJ
详情请参考：http: //docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm#CHDEGDHJ
回答by seregamorph
In case if you use spring-jdbcfor database you can consider neat wrappers from morejdbc, it will look like this:
如果您使用spring-jdbc数据库，您可以考虑使用morejdbc 的整洁包装器，它看起来像这样：
import static org.morejdbc.SqlTypes.BIGINT;
import static org.morejdbc.JdbcCall.callSql;
import static org.morejdbc.*;
...
Out<Long> idOut = Out.of(BIGINT);
jdbcTemplate.execute(callSql("BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) "
+ "RETURNING id INTO ?; END;")
.in(content)
.out(BIGINT, idOut));
System.out.println("Id is " + idOut.get());
If you have a pojo like
如果你有一个像 pojo
@lombok.Data
public class Entity {
private long id;
private String content;
}
it can be even more laconic:
它可以更简洁：
Entity entity = ;
jdbcTemplate.execute(callSql("BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) "
+ "RETURNING id INTO ?; END;")
.in(entity.getContent())
.out(BIGINT, entity::setId));
System.out.println("Id is " + entity.get());
