mybatis使用foreach标签和oracle merge into 语法实现批量更新

1.使用foreach标签和oracle merge into 语法需要在该标签增加 separator=";" open="BEGIN" close=";END;" 三个属性，写法不当会报错，例如：

PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
(
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge json_exists json_value json_query
json_object json_array
2.USING ON子句，ON条件的字段不能更新，若更新会报错，例如：

无法更新 ON 子句中引用的列: "T"."IDT_ID"
3.UPDATE SET 子句中更新字段可以使用case when ... then ... end语句

写法不当会报错，例如：

Caused by: com.alibaba.druid.sql.parser.ParserException:
syntax error, expect RPAREN, actual RBRACE pos 913, line 36, column 98, token RBRACE
4.如果mybatis和druid数据库连接池共用，需要配置WallFilter、WallConfig 两个javabean，该bean的作用是允许mybatis的一个sql标签可以执行多条sql语句，若果不配置会报错:

Caused by: java.sqlSQLException: sql injection violation, multi-statement not allow :

若使用spring需要在spring的xml配置文件里配置两个bean:WallFilter 和 WallConfig,使用springboot则参考以下代码：代码实例：

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;


@Configuration
public class DruidConfig {

//使用连接池dataSource
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource druidDataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
List<Filter> filterList = new ArrayList<>();
filterList.add(wallFilter());
druidDataSource.setProxyFilters(filterList);
return druidDataSource;

}

@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig());
return wallFilter;
}

@Bean
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);//允许一次执行多条语句
config.setNoneBaseStatementAllow(true);//允许非基本语句的其他语句
return config;
}
}
5.最后看下完整的更新sql仅供参考：

<update id="upd" parameterType="map">
<foreach collection="LIST" item="item" separator=";" open="BEGIN" close=";END;" index="index">
MERGE INTO T_PERSON T
USING (SELECT #{item.IDT_ID} IDT_ID FROM dual) T2
ON (T.IDT_ID = T2.IDT_ID)
WHEN NOT MATCHED THEN
INSERT
(...)
VALUES
(...)
WHEN MATCHED THEN
UPDATE
SET ... = ...,
PERSON_TYPE = CASE WHEN (PERSON_ID =#{item.PERSON_ID} AND PERSON_TYPE ='1') THEN '1' ELSE '0' END
</foreach>
</update>
————————————————
版权声明：本文为CSDN博主「易大师不易」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/jinziweiwang/article/details/122457305