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

推荐答案

通常你会使用 Statement#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);
    // ...

或在同一事务中的INSERT之后触发SELECT sequencename.CURRVAL:

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屋！