PLSQL JDBC：如何获取最后一行ID？ [英] PLSQL JDBC: How to get last row ID?

问题描述

什么是PLSQL（Oracle）相当于这个SQL服务器片段？

What's PLSQL (Oracle) equivalent of this SQL server snippet?

BEGIN TRAN
INSERT INTO mytable(content) VALUES ("test") -- assume there's an ID column that is autoincrement
SELECT @@IDENTITY
COMMIT TRAN

在C＃中，您可以调用myCommand.ExecuteScalar（）来检索新行的ID。

In C#, you can call myCommand.ExecuteScalar() to retrieve the ID of the new row.

如何在Oracle中插入新行，并让JDBC获取新ID的副本？

How can I insert a new row in Oracle, and have JDBC get a copy of the new id?

编辑：
BalusC提供了一个非常好的起点。由于某种原因，JDBC不喜欢命名参数绑定。这给出了错误设置或注册的参数SQLException。为什么会这样？

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?

        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");

推荐答案

通常你会用 声明#getGeneratedKeys（） （另见这个例子的答案，但这是迄今为止（仍然）不支持Oracle JDBC驱动程序。

Normally you would use Statement#getGeneratedKeys() for this (see also this answer for an example), but this is as far (still) not supported by the Oracle JDBC driver.

最好的办法是 使用 CallableStatement ，带有 RETURNING 子句：

Your best bet is to either make use of CallableStatement with a RETURNING clause:

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);
    // ...

或 fire SELECT sequencename.CURRVAL 在同一交易中 INSERT 之后：

Or fire SELECT sequencename.CURRVAL after INSERT in the same transaction:

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();
    // ...

这篇关于PLSQL JDBC：如何获取最后一行ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！