Skip to main content

Data Backfill

Use OUTPUT when you need the generated timestamp after an insert, the new value after an update, or the old value when deleting a row. The write itself returns these values; no second SELECT is needed.

Prepare the Table​

CREATE TABLE user_info (
    id BIGINT IDENTITY(1, 1) PRIMARY KEY,
    name VARCHAR(64),
    age INTEGER,
    create_time DATETIME2 DEFAULT SYSDATETIME()
);

Return the Inserted Timestamp​

Map<String, Object> saved = jdbcTemplate.queryForMap(
        """
        INSERT INTO user_info (name, age)
        OUTPUT INSERTED.id, INSERTED.create_time
        VALUES (?, ?)
        """,
        new Object[] { "mali", 18 });

The returned row contains id and create_time, including the timestamp assigned by the database. Use query methods here because the statement returns columns.

Assign Values to an Entity​

With a Mapper file, assign the returned columns to the parameter object's id and createTime properties:

<insert id="insertUser"
        useGeneratedKeys="true"
        keyProperty="id,createTime"
        keyColumn="id,create_time"
        generatedKeySource="resultSet">
    INSERT INTO user_info (name, age)
    OUTPUT INSERTED.id, INSERTED.create_time
    VALUES (#{name}, #{age})
</insert>

The object needs Long id and Timestamp createTime properties with getters and setters. keyProperty names the receiving properties; they need not all be primary keys.

Return Updated Values​

Update one record and retrieve its new age. queryForList also handles a missing ID by returning an empty list:

List<Map<String, Object>> changed = jdbcTemplate.queryForList(
        """
        UPDATE user_info SET age = ?
        OUTPUT INSERTED.id, INSERTED.age
        WHERE id = ?
        """,
        new Object[] { 19, 1001L });

Return Deleted Values​

List<Map<String, Object>> deleted = jdbcTemplate.queryForList(
        """
        DELETE FROM user_info
        OUTPUT DELETED.id, DELETED.name
        WHERE id = ?
        """,
        new Object[] { 1001L });

Each returned row contains the ID and name of a deleted record. Replace 1001L in the update and delete examples with an existing ID.

Note

Use parameter-object backfill for a single inserted row. For multiple returned rows, use a query method to read each row; do not match returned rows to inputs by position. This OUTPUT example assumes no enabled trigger for the corresponding operation.

Identity-key configuration is covered in Key Generation.