之前都是直接写语句,比如更新一百个手机号的状态:
update sms_send set send_time='2022-08-09 11:56:39',is_ok=1,sms_id='202208091156384921798+18052' where task_id=40 and id>0 and mob in(1991808,1991818,1991817,1991816,1991815,1991814,1994080......),直接在service里面用是没问题的。
规范起见,用mybatis、mapper,坑是比较多的。
一是修改这里:jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=GMT%2B8
二是mapper:你如果写成这样:
<update id="updateSmsSendState">
update sms_send set send_time=#{arg0},is_ok=#{arg1},sms_id=#{arg2} where task_id=#{arg3} and id>0 and mob in(#{arg2})
</update>
传参过去,updateSmsSendState(String time, String code, String smsId, int task_id, String mob)
是绝对不会报错的,但是悲剧发生了:
==>Preparing: update sms_send set send_time=?,is_ok=?,sms_id=? where task_id=? and id>0 and mob in(?)
==>Parameters: 2022-08-09, 1, 20220872359+18052, 46, 189000001,1890003417
==>Updates: 0
再查表,一条也没更新。。。。
是的,你想到了,mapper里面不能这么写:mob in(?) 是会要命的。。。。
要这样:
<update id="updateSmsSendState">
update sms_send set send_time=#{arg0},is_ok=#{arg1},sms_id=#{arg2} where task_id=#{arg3} and id>0 and mob in
<foreach item="mob" collection="arg4" open="(" separator="," close=")">
#{mob}
</foreach>
</update>
接口也要改:updateSmsSendState(String time, String code, String smsId, int task_id, String[] mob)
好了,可以放心的批量更新了 :),瞬间更新5000条:
几个参考:
mybatis 中 foreach collection的三种用法 https://blog.csdn.net/qq_35840499/article/details/100112963
mybatis传参源码解析 https://blog.csdn.net/csdnchen666666/article/details/106798721/