Key Generation

PostgreSQL commonly generates primary keys with SERIAL, BIGSERIAL, IDENTITY, or a sequence. The first three generate the value during INSERT and dbVisitor writes it back to the entity. A sequence can also be read before INSERT.

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 SERIAL / IDENTITY

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

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

The Builder API generates the INSERT from the entity mapping and writes the PostgreSQL-generated key to the current entity.

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

Method Annotations

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

See Data Backfill for explicit RETURNING clauses or ordinary returned fields.

Mapper File

To backfill only the database-generated key, use JDBC generated keys:

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

Note

Rows skipped by Ignore have no returned ID. See Insert Conflicts for handling existing records.

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

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

Method Annotations

@SelectKeySql(
    value = "SELECT nextval('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 nextval('user_info_id_seq')
    </selectKey>
    INSERT INTO user_info (id, name, age)
    VALUES (#{id}, #{name}, #{age})
</insert>