Plsql Jdbc: How to Get Last Row Id

PLSQL JDBC: How to get last row ID?

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

Retrieving the last inserted id in Oracle using JDBC

I found some documentation about this. It's for 11g, but the situation probably won't be better for 10g.

The proximal cause of your error is probably the limitation that:

You need to access the ResultSet object returned from getGeneratedKeys method by position only

It seems the Oracle driver also requires you to identify the key column in order for it to retrieve the key column instead of just the ROWID. Sample code for this is included in the linked documentation.

Value from last inserted row in DB

What you're trying to do is take advantage of the RETURNING clause. Let's setup an example table and sequence:

CREATE TABLE "TEST" 
( "ID" NUMBER NOT NULL ENABLE, 
 "NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, 
  CONSTRAINT "PK_TEST" PRIMARY KEY ("ID")
  );

CREATE SEQUENCE SEQ_TEST;

Now, your Java code should look like this:

String insertSql = "BEGIN INSERT INTO TEST (ID, NAME) VALUES (SEQ_TEST.NEXTVAL(), ?) RETURNING ID INTO ?; END;";
java.sql.CallableStatement stmt = conn.prepareCall(insertSql);
stmt.setString(1, "John Smith");
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.execute();
int id = stmt.getInt(2);

Get last insert id with Oracle 11g using JDBC

make it a function that returns it to you (instead of a procedure). Or, have a procedure with an OUT parameter.

How to get last inserted id into jdbc in jsp

Try something like this:

String query = "BEGIN " +
               "  INSERT INTO Recipients( CustomerID, Name, Street, City, ZipCode, PhoneNumber" + 
               "                         ,EmailAddress, ContactPersonName, ContactPersonSurname" +
               "                         ,ContactPersonPhoneNumber, ContactPersonEmailAddress) " +
               "    VALUES(?,?,?,?,?,?,?,?,?,?,?) RETURNING id into ?; " +
               "END;";

CallableStatement cs = conn.prepareCall(query);

cs.setString(1,transportTypeID);
cs.setString(2,customerID);
cs.setInt(3,recipientID);
...
cs.registerOutParameter(12, java.sql.Types.INTEGER);

cs.execute();

int id = cs.getInt(12);

Then use the resultant id for your next insert. Or you could also put it all into a proc.

How to obtain last insert id in Oracle using MyBatis?

Something like this should work

class User {
  int userId
  ...
}

<insert id="addUser" useGeneratedKeys="true" keyColumn="user_id" keyProperty="userId">
  INSERT INTO user(login, name,...) VALUES(#{login}, #{name},...
</insert>

Get ID of last inserted record in oracle db

Use the PL/SQL RETURNING clause:

insert into mytable (...) values (...)
returning id into v_id;

Batch duplicating last row

You retrieve some data from the table:

 String jugada = (String)table.getValueAt(row, 0);
 String ruleta = (String)table.getValueAt(row, 1);
 String apuesta = (String)table.getValueAt(row, 2);

But fail to insert them anywhere into you database.

---

Related Topics