请问在mybatis中用foreach搭配MERGE INTO实现批量插入或者修改应该咋实现？

Question

对象是Persons,参数是List<Persons>，以下是Persons属性：

public class Persons {
    private String ID_P;
    private String LASTNAME;
    private String FIRSTNAME;
    private String ADDRESS;
    private String CITY;
    }

mybatis中使用的是update标签，如下：

<update id="updatetableforccmsF401" parameterType="java.util.List">
        MERGE INTO PERSONS a
        USING (
        <foreach collection="list" item="item" index="index" separator="UNION ALL" open="(" close=")">
        SELECT #{item.ID_P,jdbcType=VARCHAR} idp, #{item.LASTNAME,jdbcType=VARCHAR} lastname, #{item.FIRSTNAME,jdbcType=VARCHAR} firstname, #{item.ADDRESS,jdbcType=VARCHAR} address, #{item.CITY,jdbcType=VARCHAR} city from dual
        </foreach>
        ) b
        ON (a.ID_P = b.idp)
        WHEN NOT MATCHED THEN
        insert  (ID_P,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(b.idp,b.lastname,b.firstname,b.address,b.city)
        WHEN MATCHED THEN
        UPDATE SET LASTNAME = b.lastname,FIRSTNAME = b.firstname,ADDRESS = b.address,CITY = b.city
    </update>

以下是我的sql

这样跑起来还是会报错，报错信息如下：

### Cause: java.sql.SQLException: sql injection violation, syntax error: TODO : WHEN WHEN : MERGE INTO PERSONS a
        USING (
          
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         UNION ALL 
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         UNION ALL 
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         
        ) b
        ON (a.ID_P = b.idp)
        WHEN NOT MATCHED THEN
        insert  (ID_P,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(b.idp,b.lastname,b.firstname,b.address,b.city)
        WHEN MATCHED THEN
        UPDATE SET LASTNAME = b.lastname,FIRSTNAME = b.firstname,ADDRESS = b.address,CITY = b.city
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; sql injection violation, syntax error: TODO : WHEN WHEN : 
Caused by: java.sql.SQLException: sql injection violation, syntax error: TODO : WHEN WHEN : MERGE INTO PERSONS a
        USING (
          
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         UNION ALL 
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         UNION ALL 
        SELECT ? idp, ? lastname, ? firstname, ? address, ? city from dual
         
        ) b
        ON (a.ID_P = b.idp)
        WHEN NOT MATCHED THEN
        insert  (ID_P,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(b.idp,b.lastname,b.firstname,b.address,b.city)
        WHEN MATCHED THEN
        UPDATE SET LASTNAME = b.lastname,FIRSTNAME = b.firstname,ADDRESS = b.address,CITY = b.city
    at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:706)
    at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:234)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
    at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:929)
    at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
    at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
    at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:311)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy105.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    ... 49 more
Caused by: com.alibaba.druid.sql.parser.ParserException: TODO : WHEN WHEN
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser.parseStatementList(OracleStatementParser.java:490)
    at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:108)
    at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:627)
    at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:586)
    at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:694)
    ... 75 more

Answer 1

生成union all的语句，不能使用绑定变量的方式

#{item.ID_P,jdbcType=VARCHAR} idp

改为：

'${item.ID_P,jdbcType=VARCHAR}' idp