PLSQL JDBC: How to get last row ID?
Solution 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();
// ...
Solution 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.
Solution 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
Comments
-
Karthik Rao almost 2 years
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");
-
Patrick Marchand almost 14 yearsDo you mean "SELECT seq_mytable.CURRVAL from dual" instead of "SELECT CURRVAL(seq_mytable)"?
-
BalusC almost 14 years@Patrick: Oh drat, I had PostgreSQL SQL syntax in mind, I'll update (previously, PostgreSQL used to have the same problem of not supporting
Statement#getGeneratedKeys()
so that the same "workaround" was necessary, but since about a year ago they finally fixed their JDBC driver to support it). -
Karthik Rao almost 14 yearsHi BalusC, thanks for the help. Can you take a look at my edit see if you can solve that other mystery?
-
BalusC almost 14 yearsYou need to specify the placeholder index, not the column name. Replace
"newId"
by2
inregisterOutParameter()
. -
Karthik Rao almost 14 yearsIn practice, I have a long insert statement with 15-some parameters, which I prefer having named binding if I could. JDBC will complain about having mixed binding if I use question mark. Do I must go with ordinal binding when out-parameter is used?
-
BalusC almost 14 yearsFor named binding, use an ORM like Hibernate/JPA. Basic JDBC doesn't support it.
-
Subramanian almost 12 yearsI believe there is nothing incorrect with the JDBC specification from Oracle Driver's perspective. The prepareStatement call needs to be told to return auto-generated keys like this. conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
-
BalusC almost 12 years@Subramanian: So it has changed recently? So the example in the linked answer works for Oracle now? Note that both answers are posted 2 years ago.
-
Ach J almost 8 yearsThis works as long as the insert statement has up to 7 parameters, when there are more than 7 parameters an ArrayIndexOutofBound exception is thrown. Looks like a fault with the oracle jdbc driver. #Oracle_11g_R2. Answer from BalusC is better because of this reason.