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