首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >PLSQL JDBC:如何获取最后一行ID?

PLSQL JDBC:如何获取最后一行ID?
EN

Stack Overflow用户
提问于 2010-08-24 06:41:00
回答 4查看 12K关注 0票数 20

这个SQL服务器片段的PLSQL (Oracle)等价物是什么?

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。

如何在Oracle中插入新行,并让JDBC获得新id的副本?

编辑: 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");
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2010-08-24 07:01:01

通常情况下,您将使用Statement#getGeneratedKeys() (另请参阅this answer获取示例),但是Oracle JDBC驱动程序目前(仍然)不支持这种方式。

最好的方法是使用带有RETURNING子句的CallableStatement

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

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();
    // ...
票数 37
EN

Stack Overflow用户

发布于 2010-08-24 06:48:46

可以使用Oracle的子句。

insert into mytable(content) values ('test') returning your_id into :var;

请查看this link以获取代码示例。您需要Oracle 10g或更高版本,以及新版本的JDBC驱动程序。

票数 8
EN

Stack Overflow用户

发布于 2013-11-22 13:24:32

您可以通过显式选择关键字字段来使用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);
            }
        }
    }

详情请参阅:http://docs.oracle.com/cd/E16655_01/java.121/e17657/jdbcvers.htm#CHDEGDHJ

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/3552260

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档