Generating Keys

Generated keys is a mechanism that database generate an automatic value for you, can be auto-generated at column level or a sequence level.

Using Oracle Sequence

Creating table and sequence:

CREATE TABLE AUTHOR ( 
  "ID" NUMBER(19) NOT NULL, 
  "NAME" VARCHAR(255), 
  PRIMARY KEY ("ID")
);
CREATE SEQUENCE  "SEQ_AUTHOR"  MINVALUE 1 INCREMENT BY 1 START WITH 1;

Statement query in xml file:

<statements 
 xmlns="http://jkniv.sf.net/schema/sqlegance/statements" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://jkniv.sf.net/schema/sqlegance/statements http://jkniv.sf.net/schema/sqlegance/sqlegance-stmt.xsd"
 >
  <insert id="Author#add">
    <autoGeneratedKey strategy="SEQUENCE" properties="id">
     SELECT SEQ_AUTHOR.nextval from dual
    </autoGeneratedKey>
    INSERT INTO AUTHOR (ID, NAME) VALUES (:id, :name)
  </insert>
</statements>

How check the results:

Author author = new Author();
author.setName("John Lennon");
int rows = repositoryDb.add(author);        
assertThat(rows, is(1));
assertThat(author.getId(), greaterThan(1L));

Using Derby Sequence

Creating table and sequence:

CREATE TABLE AUTHOR  (
 ID BIGINT NOT NULL, 
 NAME VARCHAR(255), 
 PRIMARY KEY (ID)
);
CREATE SEQUENCE SEQ_AUTHOR AS BIGINT START WITH 1;

Statement insert using sequence:

<statements 
 xmlns="http://jkniv.sf.net/schema/sqlegance/statements" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://jkniv.sf.net/schema/sqlegance/statements http://jkniv.sf.net/schema/sqlegance/sqlegance-stmt.xsd"
 >
  <insert id="Author#add">
    <autoGeneratedKey strategy="SEQUENCE" properties="id">
     VALUES (NEXT VALUE FOR SEQ_AUTHOR)
    </autoGeneratedKey>
    INSERT INTO AUTHOR (ID, NAME) VALUES (:id, :name)
  </insert>
</statements>

How check the results:

Author author = new Author();
author.setName("John Lennon");
int rows = repositoryDb.add(author);        
assertThat(rows, is(1));
assertThat(author.getId(), greaterThan(1L));

Using Auto increment

Creating a table with auto-increment column:

CREATE TABLE AUTHOR (
 ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
 NAME VARCHAR(255) 
);

Statement insert using auto/identity column:

<statements 
 xmlns="http://jkniv.sf.net/schema/sqlegance/statements" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://jkniv.sf.net/schema/sqlegance/statements http://jkniv.sf.net/schema/sqlegance/sqlegance-stmt.xsd"
 >
 <insert id="Author#add">
   <autoGeneratedKey strategy="AUTO" properties="id" />
   INSERT INTO AUTHOR (ID, NAME) VALUES (:id, :name)
 </insert>
</statements>

How check the results:

Author author = new Author();
author.setName("John Lennon");
int rows = repositoryDb.add(author);        
assertThat(rows, is(1));
assertThat(author.getId(), greaterThan(1L));