PLSQL JDBC: How to get last row ID?

Question

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

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

How can I insert a new row in Oracle, and have JDBC get a copy of the new 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?

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

Answer 1

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.

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

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

Answer 2

You can use Oracle's returning clause.

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

Check out this link for a code sample. You need Oracle 10g or later, and a new version of JDBC driver.

Answer 3

You can use getGeneratedKeys(), By explicitly selecting key field. Here is a snippet:

    // 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

Answer 4

In case if you use spring-jdbc for database you can consider neat wrappers from morejdbc, it will look like this:

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

@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());