How can I get update rows with mybatis?

Question

If I use mybatis, I can easily get the count of updated rows, just like

update table set desc = 'xxx' where name = ?

However, if I want to get the updated rows, not the count, how can I achieve this by mybatis?

Answer 1

mybatis itself can't do that because this update happens in database and no row data is returned back.

The only option is to modify the query and make it update and select the data you need. The exact way how to achieve this effect depends on the database you are using and/or driver support.

In postgres for example you can change the query and add RETURNING clause like this:

UPDATE table
SET desc = 'xxx'
WHERE name = ?
RETURNING *

This will turn this query to a select one and you can map it as select query in mybatis. Some other databases have similar features.

Another option (if you database and/or JDBC driver support this) is to do two queries, update and select like this

<select id='updateAndReturnModified" resultMap="...">
    UPDATE table
    SET desc = 'xxx'
    WHERE name = @{name};
    SELECT *
    FROM table
    WHERE name = @{name};
</select>

This however may require to use more strict isolation level (READ_COMMITED for example will not work) to make sure the second select sees the state after update and does not see changes made by some concurrent update. Again whether you need this or not depends on the database your are using.