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>