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
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.