Key Generation

Dameng auto-increment columns are suitable for JDBC generated-key backfill, while sequences provide the key before INSERT.

Note

For general configuration, see Generated Keys.

Using an Auto-increment Column

CREATE TABLE user_info (
    id BIGINT IDENTITY(1, 1) PRIMARY KEY,
    name VARCHAR(64)
);

Builder API

Dameng auto-increment column 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

Dameng generated keys

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

Recommend explicitly writing keyColumn to keep the backfill column consistent with the business primary key column.

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)
);
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 = "SELECT user_info_seq.NEXTVAL AS id",
    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">
        SELECT user_info_seq.NEXTVAL AS id
    </selectKey>
    INSERT INTO user_info (id, name)
    VALUES (#{id}, #{name})
</insert>