Key Generation

H2 supports common IDENTITY auto-increment columns and also sequences. Under H2, dbVisitor primarily uses JDBC generated keys or pre-insert sequence reading to handle primary keys.

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

Entity mapping uses KeyType.Auto:

H2 IDENTITY mapping

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

    @Column("name")
    private String name;

    @Column("age")
    private Integer age;
}

Automatic property backfill after insert

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

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

Long id = user.getId();

Method Annotations

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

Mapper File

H2 generated keys

<insert id="insertUser"
        useGeneratedKeys="true"
        keyProperty="id"
        keyColumn="id">
    INSERT INTO user_info (name, age)
    VALUES (#{name}, #{age})
</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 PRIMARY KEY,
    name VARCHAR(64),
    age INTEGER
);
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;
    private Integer age;

    // 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, age) VALUES (#{id}, #{name}, #{age})")
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, age)
    VALUES (#{id}, #{name}, #{age})
</insert>