Data Backfill

Use FINAL TABLE / OLD TABLE 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 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(64),
    age INTEGER,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Return the Inserted Timestamp

Map<String, Object> saved = jdbcTemplate.queryForMap(
        """
        SELECT id, create_time FROM FINAL TABLE (
            INSERT INTO user_info (name, age) VALUES (?, ?)
        ) AS changed
        """,
        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">
    SELECT id, create_time FROM FINAL TABLE (
        INSERT INTO user_info (name, age) VALUES (#{name}, #{age})
    ) AS changed
</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(
        """
        SELECT id, age FROM FINAL TABLE (
            UPDATE user_info SET age = ? WHERE id = ?
        ) AS changed
        """,
        new Object[] { 19, 1001L });

Return Deleted Values

List<Map<String, Object>> deleted = jdbcTemplate.queryForList(
        """
        SELECT id, name FROM OLD TABLE (
            DELETE FROM user_info WHERE id = ?
        ) AS changed
        """,
        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.

Identity-key configuration is covered in Key Generation.