java - 在MyBatis for IBM DB2中执行多个INSERT语句

Question

我正在尝试使用 Spring Boot (Java) 通过 MyBatis for IBM DB2 执行多个 INSERT 语句。

在 XML 中

<insert id="insertIntoTempTmpCopyTo" parameterType="map">
   <foreach collection="list" item="lItem" separator=";">
      <foreach collection="data.keyList" item="rec" separator=";">
         <if test = "rec.sel and ((rec.keyType eq 'PPP'.toString())">
            INSERT INTO SESSION.MP_COPYTO(VP_ENCRP_PRC_RL_ID, PROC_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM)
            SELECT      
                '${lItem.EncrpRlId}' as ENCRP_RL_ID,
                '${rec.systemTo}' as PROC_CD,
                '${rec.keyType}' as KEY_SET_CD,
                '${rec.trnsltTpCd}' as TRNSLT_CD,
                '${rec.setTo}' as SET_INDEX,
                '${rec.dkiTo}' AS SET_NUM,

                <if test="lItem.packetNum != null">
                    ${lItem.packetNum} AS P_NUM
                </if>
                <if test="lItem.packetNum == null">
                    0 AS P_NUM
                </if>
            FROM SYSIBM.SYSDUMMY1
        </if>
    </foreach>
  </foreach>
  ;
</insert>

使用它，我可以创建多个插入语句

 INSERT INTO SESSION.MP_COPYTO(ENCRP_RL_ID, PROC_CD, KEY_SET_CD, TRNSLT_CD, SET_INDEX, SET_NUM, P_NUM) 
 SELECT '500137' as ENCRP_RL_ID, 'DB' as PROC_CD, 'MDK' as KEY_SET_CD, '0' as TRNSLT_CD, '1' as SET_INDEX, '001' AS SET_NUM, 766456 AS P_NUM FROM SYSIBM.SYSDUMMY1 ; 

 INSERT INTO SESSION.MP_COPYTO(ENCRP_RL_ID, PROC_CD, KEY_SET_CD, TRNSLT_CD, SET_INDEX, SET_NUM, P_NUM) 
 SELECT '500137' as ENCRP_RL_ID, 'DB' as PROC_CD, 'MDK' as KEY_SET_CD, '0' as TRNSLT_CD, '2' as SET_INDEX, '002' AS SET_NUM, 766456 AS P_NUM FROM SYSIBM.SYSDUMMY1 ; 

但是当我通过 Spring Boot JAVA 执行时，我只能插入一行

2019-05-24 09:36:51,094 DEBUG [SimpleAsyncTaskExecutor-1] : Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@123536e]
2019-05-24 09:36:51,094 DEBUG [SimpleAsyncTaskExecutor-1] : Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@123536e] from current transaction
2019-05-24 09:36:51,118 DEBUG [SimpleAsyncTaskExecutor-1] : ==>  Preparing: INSERT INTO SESSION.MP_COPYTO(ENCRP_RL_ID, PROC_CD, KEY_SET_CD, TRNSLT_CD, SET_INDEX, SET_NUM, P_NUM) SELECT '500137' as ENCRP_RL_ID, 'DB' as PROC_CD, 'MDK' as KEY_SET_CD, '0' as TRNSLT_CD, '1' as SET_INDEX, '001' AS SET_NUM, 766456 AS P_NUM FROM SYSIBM.SYSDUMMY1 ; INSERT INTO SESSION.MP_COPYTO(ENCRP_RL_ID, PROC_CD, KEY_SET_CD, TRNSLT_CD, SET_INDEX, SET_NUM, P_NUM) SELECT '500137' as ENCRP_RL_ID, 'DB' as PROC_CD, 'MDK' as KEY_SET_CD, '0' as TRNSLT_CD, '2' as SET_INDEX, '002' AS SET_NUM, 766456 AS P_NUM FROM SYSIBM.SYSDUMMY1 ;
2019-05-24 09:36:51,118 DEBUG [SimpleAsyncTaskExecutor-1] : ==> Parameters: 
2019-05-24 09:36:51,158 DEBUG [SimpleAsyncTaskExecutor-1] : <==    Updates: 1

我很困惑为什么会发生这种情况。我正在寻找一个将 allowedMultipleQueries 属性设置为 true 的地方，但我找不到。有什么方法可以在运行时设置它。

这是我能做的吗？

任何输入都会有帮助。

谢谢!

Answer 1

您没有指定版本，但 Db2 可能支持多行插入语法。
根据this thread似乎有一些变化不过。

我刚刚使用 Db2 LUW 11 测试了以下语法，它有效。

INSERT INTO table (col1, col2) VALUES (?, ?), (?, ?)

使用此语法，语句可以编写如下:

<insert id="insertIntoTempTmpCopyTo" parameterType="map">
  INSERT INTO SESSION.MP_COPYTO(VP_ENCRP_PRC_RL_ID,
    PROC_CD, VER_KEY_SET_CD, TRNSLT_TP_CD,
    SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM) VALUES
  <trim suffixOverrides=",">
    <foreach collection="list" item="lItem">
      <foreach collection="data.keyList" item="rec">
        <if test="rec.sel and (rec.keyType eq 'PPP'.toString())">
          (#{lItem.EncrpRlId}, #{rec.systemTo}, #{rec.keyType},
            #{rec.trnsltTpCd}, #{rec.setTo}, #{rec.dkiTo},
          <if test="lItem.packetNum != null">
            #{lItem.packetNum}
          </if>
          <if test="lItem.packetNum == null">
            0
          </if>
          ),
        </if>
      </foreach>
    </foreach>
  </trim>
</insert>

• <trim />元素删除多余的尾随逗号。
• 您应该使用#{}而不是${}只要有可能。请参阅FAQ .