Key Generation

Oracle supports IDENTITY columns and has long used sequences for primary key generation. The recommended configurations in dbVisitor differ between these two approaches:

• IDENTITY: generated by the database after insert, backfilled via JDBC generated keys.
• sequence: get the sequence value before INSERT, then pass the primary key value into the 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 IDENTITY

Oracle 12c+ can use IDENTITY columns:

CREATE TABLE user_info (
    id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(64),
    age  NUMBER
)

Builder API

Configure KeyType.Auto on the primary-key property for automatic backfill after insertion.

Oracle IDENTITY mapping

@Table("user_info")
public class UserInfo {
    @Column(value = "id", primary = true, keyType = KeyType.Auto)
    private Integer 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 = ...;
int rows = lambda.insert(UserInfo.class)
                 .applyEntity(user)
                 .executeSumResult();

Integer id = user.getId();

Note

• Multiple objects are inserted individually, with keys backfilled separately. JDBC batching is not used.
• When an insert conflict skips the insert or updates an existing row, key backfill is not guaranteed.

Method Annotations

Set useGeneratedKeys = true, with keyProperty naming the property to fill and keyColumn naming the primary-key column:

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

Mapper File

Set useGeneratedKeys="true", with keyProperty naming the property to fill and keyColumn naming the primary-key column:

Oracle IDENTITY generated keys

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

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 NUMBER(10) PRIMARY KEY,
    name VARCHAR2(100),
    age NUMBER(3)
);
CREATE SEQUENCE user_info_seq START WITH 1 INCREMENT BY 1;

Builder API

Configure the primary-key property with KeyType.Sequence and name the sequence with @KeySeq:

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

Before each insert, dbVisitor runs SELECT user_info_seq.NEXTVAL FROM dual, assigns the value to user.id, and inserts the row. When inserting multiple entities, each receives its own sequence value.

Method Annotations

Annotations can use @SelectKeySql:

@SelectKeySql(
    value = "SELECT user_info_seq.NEXTVAL AS id FROM dual",
    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

Mapper file using selectKey

<insert id="insertUser">
    <selectKey keyProperty="id" keyColumn="id" order="BEFORE">
        SELECT user_info_seq.NEXTVAL AS id FROM dual
    </selectKey>
    INSERT INTO user_info (id, name, age)
    VALUES (#{id}, #{name}, #{age})
</insert>

Choosing the key source

For method annotations and Mapper files, use useGeneratedKeys with the default key source. The generated-key result is separate from the statement's ordinary result set; do not set generatedKeySource="resultSet" for these inserts.

For values returned by RETURNING ... INTO, use OUT parameters instead; see Returning data.