Key Generation

DB2 supports identity columns and sequences. For identity columns, JDBC generated keys can be used; for sequences, getting the value before INSERT is more recommended.

Note

For general configuration, see Generated Keys.

Key Strategies

Strategy	Usage on this datasource
Assigned	Supply the primary key before insertion.
Auto-increment	Use a database-generated numeric key.
UUID	Use UUID32 or UUID36 with a string key field.
Sequence	Fetch a sequence value before insertion.
Custom	Use a custom generator before or after insertion.

Using IDENTITY

CREATE TABLE user_info (
    id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(64),
    age  INT
)

Builder API

DB2 IDENTITY mapping

@Table("user_info")
public class UserInfo {
    @Column(value = "id", primary = true, keyType = KeyType.Auto)
    private Long id;

    @Column("name")
    private String name;
}

Automatic property backfill after insert

UserInfo user = new UserInfo();
user.setName("mali");

LambdaTemplate lambda = ...;
lambda.insert(UserInfo.class)
      .applyEntity(user)
      .executeSumResult();

Long id = user.getId();

Method Annotations

@Insert(
    value = "INSERT INTO user_info (name) VALUES (#{name})",
    useGeneratedKeys = true,
    keyProperty = "id",
    keyColumn = "id"
)
int insertUser(UserInfo user);

Mapper File

DB2 generated keys

<insert id="insertUser"
        useGeneratedKeys="true"
        keyProperty="id"
        keyColumn="id">
    INSERT INTO user_info (name)
    VALUES (#{name})
</insert>

Note

When inserting multiple entities with generated keys, dbVisitor inserts and fills each entity individually. For all-or-nothing writes, use Multiple-Write Consistency.

Using a Sequence

This example uses a regular primary-key column and a sequence, independently of the identity example above:

CREATE TABLE user_info (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(64)
);
CREATE SEQUENCE user_info_seq START WITH 1 INCREMENT BY 1;

Builder API

import net.hasor.dbvisitor.mapping.Column;
import net.hasor.dbvisitor.mapping.KeySeq;
import net.hasor.dbvisitor.mapping.KeyType;
import net.hasor.dbvisitor.mapping.Table;

@Table("user_info")
public class UserInfo {
    @Column(primary = true, keyType = KeyType.Sequence)
    @KeySeq("user_info_seq")
    private Long id;
    private String name;

    // Getters and setters omitted
}

UserInfo user = new UserInfo();
user.setName("mali");

lambda.insert(UserInfo.class)
        .applyEntity(user)
        .executeSumResult();

Long id = user.getId();

Each insert obtains a sequence value, assigns it to id, and inserts the row.

Method Annotations

@SelectKeySql(
    value = "VALUES NEXT VALUE FOR user_info_seq",
    keyProperty = "id",
    keyColumn = "id",
    order = Order.Before
)
@Insert("INSERT INTO user_info (id, name) VALUES (#{id}, #{name})")
int insertUser(UserInfo user);

Mapper File

<insert id="insertUser">
    <selectKey keyProperty="id" keyColumn="id" order="BEFORE">
        VALUES NEXT VALUE FOR user_info_seq
    </selectKey>
    INSERT INTO user_info (id, name)
    VALUES (#{id}, #{name})
</insert>