PLSQL JDBC: How to get last row ID?

Haoest_10088 0
Viewed 12547

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");
jdbc plsql oracle java
4 Answers

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

user_8117290

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());
user_34689050
Related