INSERT, and get the auto-incremented value

sql derby javadb

deprecated · Jul 22, 2011 · Viewed 10.8k times · Source

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 primary key (id)
);

To which I'd insert an entry this way.

insert into language(name) values ('value');

How does one know what value for id was created? Just doing a SELECT using the name field is not valid, because there can be duplicate entries.

Answer

Through plain SQL:

 insert into language(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.

INSERT, and get the auto-incremented value

Answer

Related questions