Insert, And Get The Auto-incremented Value

Consider the following table: create table language ( id integer generated always as identity (START WITH 1, INCREMENT BY 1), name long varchar, constraint language_pk

Solution 1:

Through plain SQL:

insertintolanguage(name) values ('value');
 SELECT IDENTITY_VAL_LOCAL();

See the manual for details: http://db.apache.org/derby/docs/10.7/ref/rrefidentityvallocal.html

When doing this from a Java class (through JDBC) you can use getGeneratedKeys() after "requesting" them with the approriate executeUpdate() method.

Solution 2:

You use the JDBC method

st.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSetkeys= st.getGeneratedKeys();

as documented in the Derby manual.

See also Javadocs: DatabaseMetaData#supportsGetGeneratedKeys() and Statement#getGeneratedKeys()

Solution 3:

You could execute this statement (NB, not 100% sure this syntax is correct for Derby:

SELECT TOP 1 id FROMlanguageORDERBY id DESC

To find the last inserted ID.

Alternative for Derby:

SELECTMAX(id) fromlanguage

Obviously this will only be accurate if no other inserts (including inserts by other users) have happened between your insert and select.

See also this discussion: