Key Generation

SQL Server commonly generates primary keys with IDENTITY or a sequence. An IDENTITY value is created during INSERT and dbVisitor writes it back to the entity. A sequence can be read before INSERT so the application knows the key before writing the row.

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 IDENTITY(1, 1) PRIMARY KEY,
    name NVARCHAR(64),
    age  INT
)

Use KeyType.Auto on the entity key:

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

    private String name;
    private Integer age;
}

Builder API

After insertion, the database-generated id is assigned to the current entity automatically.

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

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

Long id = user.getId();

Hand-written Mapper SQL is not augmented with SQL Server clauses. To backfill only an IDENTITY key, use JDBC generated keys:

Method Annotations

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

Use OUTPUT INSERTED when the returned columns must be explicit or ordinary fields must also be backfilled; see Data Backfill.

Mapper File

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

Note

When inserting several entities, SQL Server does not guarantee that OUTPUT rows follow input order. If each generated ID must be assigned to its matching entity, insert one entity per call.

Using a Sequence

SQL Server 2012+ supports sequences. Use a regular primary-key column for this independent example:

CREATE SEQUENCE user_info_id_seq AS BIGINT START WITH 1 INCREMENT BY 1;
CREATE TABLE user_info (id BIGINT PRIMARY KEY, name NVARCHAR(64), age INT);

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_id_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();

The insert obtains the next sequence value, assigns it to id, and writes the row. Each entity receives its own value.

Method Annotations

@SelectKeySql(
    value = "SELECT NEXT VALUE FOR user_info_id_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" order="BEFORE">
        SELECT NEXT VALUE FOR user_info_id_seq
    </selectKey>
    INSERT INTO user_info (id, name, age)
    VALUES (#{id}, #{name}, #{age})
</insert>