熊大最近发现一个批量更新时不用循环字段的更新,跟各位撰码人分享分享。同为码农深知码农不易,勿入坑。
好了咱们来说正事儿,来看看这条sql
<update id="updateBatch" parameterType="java.util.List">
update mydata_table
set status=
<foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.id} then #{item.status}
</foreach>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
这无非就是根据id批量修改了mydata_table这张表的status字段,熊大觉得写的有点过于复杂了,如果字段多了就会代码冗余,不方便管理。咱们在看看下面这条i
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator=";" open="" close="">
update t_order
<set>
status= #{item.ostatus}
</set>
<where>
id = #{item.id}
</where>
</foreach>
</update>
循环整个sql进行映射而不是一个字段,这样看着是很爽,有个坑啊,我就往里面跳了2个小时才爬出来
咱们这样写固然是好,条件是配置数据库连接池druid wallfilter
@Autowired
WallFilter wallFilter;
@Bean(name = "dcDataSource")
@Primary
public DataSource getDataSourceDc() throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(password);// 密码
dataSource.setMaxActive(maxActive);
dataSource.setMinIdle(minIdle);
dataSource.setInitialSize(initialSize);
List<Filter> filters = new ArrayList<>();
filters.add(wallFilter);
dataSource.setProxyFilters(filters);
return dataSource;
}
@Bean(name = "wallConfig")
WallConfig wallFilterConfig(){
WallConfig wc = new WallConfig ();
wc.setMultiStatementAllow(true);
return wc;
}
@Bean(name = "wallFilter")
@DependsOn("wallConfig")
WallFilter wallFilter(WallConfig wallConfig){
WallFilter wfilter = new WallFilter();
wfilter.setConfig(wallConfig);
return wfilter;
}
至于配置这个不懂得可以度一下详情的帖子还是很多很好的。
感谢您的查看,欢迎评论!!