如何通过MyBatis获取更新操作对应的行数据而非更新行数？

Great question! It's totally common to need the actual updated record data rather than just the number of rows affected, and MyBatis gives you a couple of reliable ways to make this happen. Let's break them down:

1. Use Database's RETURNING Clause (Recommended for Supported Databases) #

Many modern databases (like PostgreSQL, MySQL 8.0.20+, and SQL Server) let you return updated rows directly in the same UPDATE statement using a RETURNING clause. This is the most efficient approach since it’s a single database call.

Example Mapper XML: #

<update id="updateAndReturnEntity" parameterType="com.example.YourEntity" resultType="com.example.YourEntity">
    UPDATE your_table
    SET desc = #{desc}
    WHERE name = #{name}
    RETURNING id, name, desc; <!-- Specify the columns you want to get back -->
</update>

Corresponding Mapper Interface: #

YourEntity updateAndReturnEntity(YourEntity updateParams);

When you call this method, MyBatis will map the returned row directly to your entity class, giving you the updated data right away. Just double-check your database version—MySQL users need 8.0.20 or newer to use this syntax.

2. Two-Step Query + Update (For Databases Without RETURNING Support) #

If you’re working with an older database that doesn’t support RETURNING, you can use a transactional two-step approach: first fetch the record you’re about to update, then perform the update. Important: wrap this in a transaction to avoid race conditions (so no other process can modify the record between your query and update).

Step 1: Mapper Methods #

Add a select and update to your mapper:

<select id="getEntityByName" parameterType="String" resultType="com.example.YourEntity">
    SELECT id, name, desc FROM your_table WHERE name = #{name}
</select>

<update id="updateEntityDesc">
    UPDATE your_table SET desc = #{newDesc} WHERE name = #{name}
</update>

Step 2: Transactional Service Layer #

Use your mapper in a service method annotated with @Transactional (if using Spring) or manage transactions manually:

@Transactional
public YourEntity updateAndFetchData(String name, String newDesc) {
    // First, get the existing record
    YourEntity entity = yourMapper.getEntityByName(name);
    
    if (entity != null) {
        // Perform the update
        yourMapper.updateEntityDesc(newDesc, name);
        // Update the local entity with the new value, or re-fetch for absolute accuracy
        entity.setDesc(newDesc);
        // Optional: Re-fetch to ensure you have the latest state
        // entity = yourMapper.getEntityByName(name);
    }
    
    return entity;
}

This works for any database, but it’s two separate calls—so the RETURNING method is better if you can use it.

内容的提问来源于stack exchange，提问作者PurpleCraw