项目场景:
项目场景:根据输入的id集合查找数据库中符合的id并按照输入id集合的顺序输出
问题描述:
在编写如下xml语句时
<select id="selectRank" parameterType="java.util.List" resultType="com.betterlf.myblog.entity.Blog">
select * from m_blog
where id in
<foreach item = "id" collection="cur" open="(" separator="," close=")">
<!--cur是传入的集合形参名-->
#{id}
</foreach>
order by field (id,
<foreach item = "id" collection="cur" separator=",">
#{id}
</foreach>
)
</select>
- 报错如下:
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:
Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM m_blog WHERE id IN (?)
- 问题:
为什么 in 后面的order by等语句没有显示?
原因分析:
#{}占位符不能解决一下 3 类问题:
表名是动态的: Select * from #{table_name}
列名是动态的:Select #{column_name} from t_role
排序列是动态的: Select * from t_role order by #{columu}
而${}方式一般用于传入数据库对象,比如这种group by 字段 ,order by 字段,
表名,字段名等没法使用占位符的就需要使用${param}
解决方案:
- 将order by field后要加的id参数用${}代替#{}
<select id="selectRank" parameterType="java.util.List" resultType="com.betterlf.myblog.entity.Blog">
select * from m_blog
where id in
<foreach item = "id" collection="cur" open="(" separator="," close=")">
<!--cur是传入的集合形参名-->
#{id}
</foreach>
order by field (id,
<foreach item = "id" collection="cur" separator=",">
${id}
</foreach>
)
</select>