[SOLVED] MyBatis update returning a list of values

MyBatis update returning a list of values

I have the following update query in my mybatis xml:

<update id="updateRoleValues" parameterType="map">
    UPDATE sandbox.roles
    SET rolename    = #{roleName},
        description = #{description},
        active      = #{active},
        obsolete    = false
    WHERE role_id = #{roleId} RETURNING role_id
</update>

Executing this query in an sql client results in updating an entry in the database if an entry with the supplied id exists. Only the id is returned if it exists.

In my interface the method is defined as follows:

int updateRoleValues(int roleId, String roleName, String description, boolean active);

One would expect that invocation of the method with an id that exists in the database would result in returning that id. Unfortunately MyBatis returns -1 even if the id exists.

How should one use RETURNING in the query. Can MyBatis return a specified value from an updated query other than the number of updated rows? I want to be able to return an unique value from the updated row that is of type String.

Solution

As the statement returns a result set, you need to use <select> with affectedData enabled.
https://mybatis.org/mybatis-3/sqlmap-xml.html#select

<select id="updateRoleValues" resultType="int" affectData="true">
    UPDATE sandbox.roles
    SET rolename    = #{roleName},
        description = #{description},
        active      = #{active},
        obsolete    = false
    WHERE role_id = #{roleId} RETURNING role_id
</select>

Please be sure to use MyBatis version 3.5.13 or later (3.5.12 has a regression bug).