javaoraclemybatis

mybatis oracle - ordering by the order of values in the IN clause using instr func

I wanna use the instr function to order the query results by the order of values in the IN clause.

mapper

public List<String> getFilePaths(@Param("ids") Integer[] ids)

xml

<select id="getFilePaths" resultType="java.lang.String">
  select filepath from t_file where id in
  <foreach item="fileid" collection="ids" separator="," open="(" close=")">
    #{fileid}
  </foreach>
  order by instr('
  <foreach item="fileid" collection="ids" separator=",">
    #{fileid}
  </foreach>
  ',id)
</select>

When the ids collection is [1, 2, 3], the sql generated by mybatis is:

select filepath from t_file where id in (?, ?, ?) order by instr('?, ?, ?', id)

But An exception occurs.

org.apache.ibatis.type.TypeException: Error setting non null for parameter #4 with JdbcType null.

After changing the xml for testing like below, no exception occurs.

<select id="getFilePaths" resultType="java.lang.String">
  select filepath from t_file where id in
  <foreach item="fileid" collection="ids" separator="," open="(" close=")">
    #{fileid}
  </foreach>
  and id in
  <foreach item="fileid" collection="ids" separator="," open="(" close=")">
    #{fileid}
  </foreach>
</select>

Preparing: select filepath from t_file where id in (?, ?, ?) and id in (?, ?, ?)
Parameters: 1, 2, 3, 1, 2, 3
Solution

  • You cannot use #{} in a literal. Use ${fileId} in the second foreach loop.
    Please see this FAQ entry.

    order by instr('
<foreach item="fileid" collection="ids" separator=",">
  ${fileid}
</foreach>
',id)

    There may be some preconditions, but the sort logic seems unreliable.
    When ids is [222, 22, 2], for example, the INSTR will return the same value for all three rows.