Execute multiple INSERT statement in MyBatis for IBM DB2

Question

I am trying to execute multiple INSERT statement via MyBatis for IBM DB2 using Spring Boot (Java).

In 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>

Using this, I am able to create multiple insert statements

 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 ; 

But when I am executing through Spring Boot JAVA , I am only able to insert one row

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

I am confused why is that happening. I am looking for a place to set allowMultipleQueries property to true, but I am not able to find. Is there any way I could set it during runtime.

Is that something I could do.

Any inputs would be helpful.

Thanks !

Answer 1

im not familiar with IBM DB2, but you could change your mapper.xml to something like this

<insert id="insertIntoTempTmpCopyTo" parameterType="map">
    INSERT ALL  
    <foreach collection="list" item="element" index="index" >
        <foreach collection="data.keyList" item="rec">
            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
            (
                #{lItem.EncrpRlId},
                #{rec.systemTo},
                #{rec.keyType},
                #{rec.trnsltTpCd},
                #{rec.setTo},
                #{rec.dkiTo},
                <choose>
                    <when test="lItem.packetNum != null">
                        #{lItem.packetNum}
                    </when>
                    <otherwise>
                        0
                    </otherwise>
                </choose>
            )
        </foreach>
    </foreach>
    SELECT * FROM dual
    </insert>
</mapper>

the SELECT * FROM dual at the end is very important

or you change your mapper.xml to

<insert id="insertIntoTempTmpCopyTo" parameterType="map">
    -- Change the delimiter from ';' to '§'
    -- @DELIMITER §

    -- removed separator=";"
    <foreach collection="list" item="lItem">

        -- removed separator=";"
        <foreach collection="data.keyList" item="rec">
            <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,

                  <choose>
                      <when test="lItem.packetNum != null">
                          #{lItem.packetNum} AS P_NUM
                      </when>
                      <otherwise>
                          0  AS P_NUM
                      </otherwise>
                  </choose>
                  FROM SYSIBM.SYSDUMMY1

                  -- separator inserted
                  ;
            </if>
        </foreach>
    </foreach>
    -- Change the delimiter back to ';'
    -- @DELIMITER ;
</insert>

by changing mybatis-delimiter from ; to something different, that doesnt appear in your statement, as §, you're able to execute semicolon-delimited sql-scripts

Answer 2

You didn't specify versions, but Db2 may support multi-row insert syntax.
There seems to be some variations according to this thread, though.

I just tested the following syntax with Db2 LUW 11 and it worked.

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

Using this syntax, the statement can be written as follows:

<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>

• The <trim /> element removes the extra trailing comma.
• You should use #{} instead of ${} whenever it is possible. See the FAQ.