Insert

You can insert data using an entity bean or a Map as the container.

Use a bean as the data container

User user = new User();
user.setId(20);
user.setName("new name");
user.setAge(88);
user.setCreateTime(new Date());

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyEntity(user)
                   .executeSumResult();
// result is 1

Use a Map as the data container

Map<String, Object> user = new HashMap<>();
user.put("id", 20);
user.put("name", "new name");
user.put("age", 88);
user.put("create_time", new Date());

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyMap(user)
                   .executeSumResult();
// result is 1

Auto-Generated Key Backfill

When the primary key in entity mapping is generated by the database, Lambda insert can cooperate with object mapping and database dialect to backfill the key. Different databases return primary keys in quite different ways; see Data Source Features for details. For a general description of Generated Keys, see Generated Keys.

In normal usage, only two things need confirmation: the primary key column in entity mapping has a generation strategy configured, and the current database dialect supports the corresponding generated keys behavior.

In-depth Reading

The execution strategy explanation below is suitable for understanding the differences between batch insert, database-returned primary keys, and custom KeyHolder scenarios. Regular single-row inserts can skip this.

Lambda Insert first distinguishes two categories of post-insert key handling:

• Database-returned type: onAfter=true and useGeneratedKeys=true, such as auto-increment keys, columns returned via getGeneratedKeys(), RETURNING, or OUTPUT INSERTED.
• Custom post-insert type: onAfter=true but useGeneratedKeys=false, handled by a user-defined KeyHolder after insert, independent of the database's generated-key result set.

Only database-returned columns are passed as returnColumns to the dialect for SQL generation. For PostgreSQL this may generate RETURNING, for SQL Server OUTPUT INSERTED, and other databases may use JDBC getGeneratedKeys().

For batch insert, dbVisitor chooses the execution strategy as follows:

• When no key backfill is needed, prefer plain JDBC batch.
• When only database-returned key backfill exists, the dialect selects the optimal approach, e.g., PostgreSQL VALUES (...), (...) RETURNING id or SQL Server OUTPUT INSERTED.id.
• When a custom post-insert KeyHolder exists, fall back to one-by-one execution to ensure the user-defined post-insert logic runs after each row.

Lambda Insert Key Backfill Strategy

ColumnMapping
    |
    +-- onBefore=true
    |       |
    |       +-- Generate value before INSERT
    |           e.g., UUID32, UUID36, Sequence, custom before KeyHolder
    |
    +-- onAfter=true
            |
            +-- useGeneratedKeys=true
            |       |
            |       +-- returnColumns
            |           |
            |           +-- Dialect decides return method
            |               |
            |               +-- PostgreSQL: RETURNING
            |               +-- SQL Server: OUTPUT INSERTED
            |               +-- JDBC: getGeneratedKeys()
            |
            +-- useGeneratedKeys=false
                    |
                    +-- customAfterProperties
                        |
                        +-- Batch insert falls back to OneByOne
                            ensuring afterApply runs after each INSERT

Execution Strategy
    |
    +-- customAfterProperties non-empty
    |       |
    |       +-- OneByOne
    |
    +-- returnColumns non-empty
    |       |
    |       +-- Dialect selects: MultiValuesResultSet / JdbcBatchGeneratedKeys / OneByOne
    |
    +-- returnColumns empty
            |
            +-- Prefer JdbcBatch

Therefore, pre-insert generation strategies like KeyType.UUID32, KeyType.UUID36, and KeyType.Sequence usually do not trigger database return columns; only KeyType.Auto or KeyHolder with useGeneratedKeys=true participate in generated keys backfill.

Batching

Use batch insert when inserting many records.

User user1 = new User();
...
User user2 = new User();
...
User user3 = new User();
...

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyEntity(user1, user2, user3)               // varargs
                 //.applyEntity(new User[]{user1, user2, user3});  // array
                 //.applyEntity(Arrays.asList(user1, user2, user3));// List
                   .executeSumResult();
// result is 3

• For Map data, use applyMap instead of applyEntity.
• You can call applyEntity/applyMap multiple times before executeSumResult to stage batches.

Write Conflicts

Inserting duplicate data into a database is usually unintentional, and a primary key conflict can be troublesome. A common workaround is to query first and then decide whether to update or insert.

Common approach

if (lambda.query(User.class)
            .eq(User::getId,user.getId())
            .queryForCount() > 0) {
    // update
} else {
    // insert
}

The good news is that databases themselves often provide more efficient approaches for write conflicts, for example:

• MySQL can use the ON DUPLICATE KEY UPDATE clause with INSERT.
• Oracle can use MERGE INTO ... WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ... statements.

Using these database features requires two prerequisites:

• The dbVisitor database dialect must support them; see Dialect Support.
• A conflict handling strategy must be specified via onDuplicateStrategy.

dbVisitor provides three conflict strategies to avoid redundant code logic in write operations:

• Error (Into): uses regular INSERT INTO to write data.
• Replace (Update): uses Merge or ON CONFLICT and other database-specific syntax to auto-update on write conflict.
• Ignore (Ignore): uses Ignore or other database-provided statements to skip conflicting records according to database semantics, not suppress all write errors.

Default Strategy (INTO)

The default strategy uses a plain insert into statement; when a conflict occurs the database will typically error out.

Default strategy can be omitted or explicitly set

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyEntity(user)
                   .onDuplicateStrategy(DuplicateKeyStrategy.Into) // explicit
                   .executeSumResult();

Replace Strategy (UPDATE)

Implementation depends on the specific database dialect, for example:

• For MySQL, it uses ON DUPLICATE KEY UPDATE clause with INSERT.
• For Oracle, it uses MERGE INTO ... WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ... statement.
• For PostgreSQL, it uses ON CONFLICT (...) DO UPDATE SET ... clause with INSERT.

Tip

Whether this strategy is supported depends on the database dialect; forcing it on an unsupported dialect will raise an error.

Usage

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyEntity(user)
                   .onDuplicateStrategy(DuplicateKeyStrategy.Update) // update on conflict
                   .executeSumResult();

Ignore Strategy (IGNORE)

Implementation depends on the specific database dialect, for example:

• For MySQL, it uses INSERT IGNORE statement.
• For Oracle, it uses MERGE INTO ... WHEN NOT MATCHED THEN ... statement.
• For Dameng (DM), it uses the database HINT IGNORE_ROW_ON_DUPKEY_INDEX to ignore by primary key column.

Usage

LambdaTemplate lambda = ...
int result = lambda.insert(User.class)
                   .applyEntity(user)
                   .onDuplicateStrategy(DuplicateKeyStrategy.Ignore) // ignore on conflict
                   .executeSumResult();