Cache JDBC get row id of the table while inserting the data

Question

Suman Samanta · Feb 12, 2020

HI I am using below code to retrieve the last inserted ID of the table. It works when we do not set any custom column as IDKey. If we define any column as

IDKey below code does not return any IDKey. I know when i am defining any column as IDKey its not auto generated , but whats the best way to get the ID column value

           String sql = "INSERT INTO TestFramework_UI_Data.Execution (TotalTestSteps) VALUES (0)";
           //ResultSet rs = createExecutionSt.executeQuery("SELECT * FROM TestFramework_E2E_Data_TestData.TestCases");
           int t = createExecutionSt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);

           ResultSet rsLatest = createExecutionSt.getGeneratedKeys();
           rsLatest.next();
           System.out.println(rsLatest.getString(1))

Discussion (1)0

David Van De Griek · Feb 19, 2020

Hi Suman,

For the case where the ID is not system generated, try the following query after a successful INSERT:

SELECT LAST_IDENTITY()

You can actually do this for the case where the ID is system generated also, but in that case getGeneratedKeys is probably a better option.

LAST_IDENTITY() simply returns the value of the %ROWID variable, which is set after a successful INSERT to the value of the rowid field.

3 0