Accessing list element by index in myBatis-mapping

Question

I have the following mapping in myBatis.

<update id="updatePersons" parameterType="Map">
    begin
    <foreach item="fname" collection="fnames" index="index" separator=";">
        update person set age = #{ages}[#{index}] where fname = #{fname}
    </foreach>;
    end;
</update>

It should update the age of all the persons whose first name matches the one passed as argument.

And the corresponding call in Java:

Map<String, List<String>> map = new HashMap<>();
List<String> fnames = new ArrayList<>();
List<Integer> ages = new ArrayList<>();
map.put("fnames", fnames);
map.put("ages", ages);
session.update("person.updatePersons", map);

The size of collections fnames and ages is the same. I have some difficulty to access elements of ages by index in myBatis-mapping. I have tried brackets as can be seen in the first snippet. I also tried #{ages}.get(#index) but nothing worked. Is it possible at all?

score 3 · Accepted Answer · answered Dec 16 '19 at 12:06

#{} is a placeholder (i.e. ?) in PreparedStatement, so the expression #{ages}[#index}] is translated to ?[?] which is not a valid SQL syntax.
The correct syntax would be...

<update id="updatePersons">
  begin
  <foreach item="fname" collection="fnames" index="index" separator=";">
    update person set age = #{ages[${index}]} where fname = #{fname}
  </foreach>;
  end;
</update>

See the FAQ entry for the difference between #{} and ${}.

Although this may work given that the driver supports the syntax, it basically is a single big PreparedStatement with many placeholders and is not very efficient especially when there are many items in the lists.
If that is the case, you should consider using batch update. See this answer for the details.

Accessing list element by index in myBatis-mapping

1 Answers1