apache derbyでのシリアルID値の設定方法

一瞬出来ないのかと思ったが、そんなわけないとマニュアルにあたったらすぐ出てきた。

以下のような create 文で、MAP_ID がシリアルなIDカラムとなる。

CREATE TABLE MAPS
(
MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
MAP_NAME VARCHAR(24) NOT NULL,
REGION VARCHAR(26),
AREA DECIMAL(8,4) NOT NULL,
PHOTO_FORMAT VARCHAR(26) NOT NULL,
PICTURE BLOB(102400),
UNIQUE (MAP_ID, MAP_NAME)
)

Derby Developer's Guide

生成されたID値を取得する方法は以下
Derby Reference Manual

Example

Assume that we have a table TABLE1 defined as follows:

CREATE TABLE TABLE1 (C11 int, C12 int GENERATED ALWAYS AS IDENTITY)

The following three code fragments will all do the same thing: that is, they will create a ResultSet that contains the value of C12 that is inserted into TABLE1.

Code fragment 1:

Statement stmt = conn.createStatement(); 
stmt.execute(
    "INSERT INTO TABLE1 (C11) VALUES (1)",
    Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();

Code fragment 2:

Statement stmt = conn.createStatement(); 
String [] colNames = new String [] { "C12" };
stmt.execute(
    "INSERT INTO TABLE1 (C11) VALUES (1)",
    colNames);
ResultSet rs = stmt.getGeneratedKeys();

Code fragment 3:

Statement stmt = conn.createStatement(); 
int [] colIndexes = new int [] { 2 };
stmt.execute(
    "INSERT INTO TABLE1 (C11) VALUES (1)",
    colIndexes);
ResultSet rs = stmt.getGeneratedKeys();