apache derbyでのシリアルID値の設定方法
一瞬出来ないのかと思ったが、そんなわけないとマニュアルにあたったらすぐ出てきた。
以下のような create 文で、MAP_ID がシリアルなIDカラムとなる。
CREATE TABLE MAPS
Derby Developer's Guide
(
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)
)
生成された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();