今天使用mybatis遇到一个sql查询的问题,在这里记录一下:
情景:http://www.tofacebook.com/jinjiezhimei.html
<select id="getUsers"
parameterType="java.lang.String"
resultType="com.ssm.demo.entity.user.ActivityArticleUser">
select
u.uid as uid,
u.nickname as nickName,
u.mobile as mobile,
um.balance as balance,
concat(b.brand_name, m.model_name) as autoBrandName,
u.register_time as registerTime
from t_user u
left join t_user_car c on c.uid = u.uid
left join t_auto_model m on c.auto_model_id = m.auto_model_id
left join t_auto_brand b on b.auto_brand_id = m.auto_brand_id
left join t_user_money um on u.uid = um.uid
WHERE
u.uid in (#{users})
order by u.uid limit 10
哈哈,当时结果只显示了一个出来,而预期结果应该是10个才对。仔细查看了日志:
Parameters: 11,14,189,202,227,242,255,256,266,267,271,288,289,295,296,297,301,305,309,48,5(String)
确实有正确传入参数,怎么返回结果就一个呢?
经过一番查询资料,最后整理如下:
当参数采用:#{} : 解析为一个 JDBC 预编译语句(prepared statement)的参数标记符,一个 #{ } 被解析为一个参数占位符 。${}: 仅仅为一个纯碎的 string 替换,在动态 SQL 解析阶段将会进行变量替换。
也就是说我们实际的sql语句变成了类似:
select * from t_user where uid in
('11,14,189,202,227,242,255,256,266,267,271,
288,289,295,296,297,301,305,309,48,5')
调式了半天,此时自然不是我们想要的查询结果,修改sql语句为:
select
u.uid as uid,
u.nickname as nickName,
u.mobile as mobile,
um.balance as balance,
concat(b.brand_name, m.model_name) as autoBrandName,
u.register_time as registerTime
from t_user u
left join t_user_car c on c.uid = u.uid
left join t_auto_model m on c.auto_model_id = m.auto_model_id
left join t_auto_brand b on b.auto_brand_id = m.auto_brand_id
left join t_user_money um on u.uid = um.uid
WHERE
u.uid in (${_parameter})
order by u.uid
结果已经查出10条了,达到了我们想要的效果,记住无论参数名是啥,都要改成”_parameter”
终于搞完,花了两三个小时,水到家了哈,往见谅!