MyBatis+Oracle+Sequence

1. 【MyBatis + Oracle + Sequence】插入单条

<insert id="insertFfbz" useGeneratedKeys="true" parameterType="com.resources.novelcoronavirus.domain.FuFeiBingZhongPO">
	<selectKey resultType="String" order="BEFORE" keyProperty="ID">
		SELECT appgm.SEQ_YB_FFBZ.NEXTVAL FROM DUAL
	</selectKey>
	INSERT INTO appgm.yb_ffbz (
		ID,
		BZJSMLI,
		ABZJSBZMLDM,
		ABZJSBZMC,
		XDSSCZDM,
		XDSSCZMC,
		YXBZ,
		WYJLH,
		SJCJSJ,
		SJGXSJ,
		BBH,
		BZNH,
		BZ,
		BBMC,
		ZLZNYM,
		ZLZNDZDA
	) VALUES (
		#{ID, jdbcType=VARCHAR},
		#{BZJSMLI, jdbcType=VARCHAR},
		#{ABZJSBZMLDM, jdbcType=VARCHAR},
		#{ABZJSBZMC, jdbcType=VARCHAR},
		#{XDSSCZDM, jdbcType=VARCHAR},
		#{XDSSCZMC, jdbcType=VARCHAR},
		#{YXBZ, jdbcType=VARCHAR},
		#{WYJLH, jdbcType=VARCHAR},
		#{SJCJSJ, jdbcType=VARCHAR},
		#{SJGXSJ, jdbcType=VARCHAR},
		#{BBH, jdbcType=VARCHAR},
		#{BZNH, jdbcType=VARCHAR},
		#{BZ, jdbcType=VARCHAR},
		#{BBMC, jdbcType=VARCHAR},
		#{ZLZNYM, jdbcType=VARCHAR},
		#{ZLZNDZDA, jdbcType=VARCHAR}
	)
</insert>

2. 【MyBatis + Oracle + Sequence】批量插入【MyBatis采用【批处理】的方式最为高效也最推荐，但插入报会 ORA-01461-仅能绑定要插入LONG列的LONG值；是由于使用了DUAL虚拟表，会将长度在（2000至4000）的字符串从"varchar2"类型转为"LONG"类型】

<insert id="batchInsertFfbz" useGeneratedKeys="true" parameterType="java.util.List">
	<selectKey resultType="String" order="BEFORE" keyProperty="ID">
		SELECT appgm.SEQ_YB_FFBZ.NEXTVAL FROM DUAL
	</selectKey>
	INSERT INTO appgm.yb_ffbz (
		ID,
		BZJSMLI,
		ABZJSBZMLDM,
		ABZJSBZMC,
		XDSSCZDM,
		XDSSCZMC,
		YXBZ,
		WYJLH,
		SJCJSJ,
		SJGXSJ,
		BBH,
		BZNH,
		BZ,
		BBMC,
		ZLZNYM,
		ZLZNDZDA
	) SELECT appgm.SEQ_YB_FFBZ.NEXTVAL, t.* FROM (
		<foreach collection="list" index="i" item="it" separator="UNION ALL">
			SELECT
				#{it.BZJSMLI, jdbcType=VARCHAR},
				#{it.ABZJSBZMLDM, jdbcType=VARCHAR},
				#{it.ABZJSBZMC, jdbcType=VARCHAR},
				#{it.XDSSCZDM, jdbcType=VARCHAR},
				#{it.XDSSCZMC, jdbcType=VARCHAR},
				#{it.YXBZ, jdbcType=VARCHAR},
				#{it.WYJLH, jdbcType=VARCHAR},
				#{it.SJCJSJ, jdbcType=VARCHAR},
				#{it.SJGXSJ, jdbcType=VARCHAR},
				#{it.BBH, jdbcType=VARCHAR},
				#{it.BZNH, jdbcType=VARCHAR},
				#{it.BZ, jdbcType=VARCHAR},
				#{it.BBMC, jdbcType=VARCHAR},
				#{it.ZLZNYM, jdbcType=VARCHAR},
				#{it.ZLZNDZDA, jdbcType=VARCHAR}
			FROM
				DUAL
		</foreach>
	) t
</insert>

3. 利用循环SQL拼接的方式，能解决【方法2】中批处理方式中DUAL引起的报错问题，但性能远不如批处理。

<!-- 低版本得MyBatis需要将useGeneratedKeys="false"这是一个坑点 -->
    <insert id="batchInsertTmbz" useGeneratedKeys="false" parameterType="java.util.List">
        <foreach collection="list" index="i" item="it" open="begin" close=";end;" separator=";">
            INSERT INTO appgm.yb_tmbz (
                ID,
                MMMTBZMLDM,
                MMMTBZDLMC,
                MMMTBZXFLMC,
                YBQH,
                BZ,
                YXBZ,
                WYJLH,
                SJCJSJ,
                SJGXSJ,
                BBH,
                BZNH,
                BBMC,
                ZLZNYM,
                ZLZNDZDA,
                MMMTBZMC,
                MMMTBZDLDM
            ) VALUES (
                appgm.SEQ_YB_TMBZ.NEXTVAL,
                #{it.MMMTBZMLDM, jdbcType = VARCHAR},
                #{it.MMMTBZDLMC, jdbcType = VARCHAR},
                #{it.MMMTBZXFLMC, jdbcType = VARCHAR},
                #{it.YBQH, jdbcType = VARCHAR},
                #{it.BZ, jdbcType = VARCHAR},
                #{it.YXBZ, jdbcType = VARCHAR},
                #{it.WYJLH, jdbcType = VARCHAR},
                #{it.SJCJSJ, jdbcType = VARCHAR},
                #{it.SJGXSJ, jdbcType = VARCHAR},
                #{it.BBH, jdbcType = VARCHAR},
                #{it.BZNH, jdbcType = VARCHAR},
                #{it.BBMC, jdbcType = VARCHAR},
                #{it.ZLZNYM, jdbcType = VARCHAR},
                #{it.ZLZNDZDA, jdbcType = VARCHAR},
                #{it.MMMTBZMC, jdbcType = VARCHAR},
                #{it.MMMTBZDLDM, jdbcType = VARCHAR}
            )
        </foreach>
    </insert>

4. 【MyBatis + Oracle】批量更新

<update id="batchUpdateFfbz" parameterType="java.util.List">
	<foreach collection="list" index="i" item="it" open="begin" close=";end;" separator=";">
		UPDATE
			appgm.yb_ffbz
		SET
			BZJSMLI = #{it.BZJSMLI, jdbcType=VARCHAR},
			ABZJSBZMLDM = #{it.ABZJSBZMLDM, jdbcType=VARCHAR},
			ABZJSBZMC = #{it.ABZJSBZMC, jdbcType=VARCHAR},
			XDSSCZDM = #{it.XDSSCZDM, jdbcType=VARCHAR},
			XDSSCZMC = #{it.XDSSCZMC, jdbcType=VARCHAR},
			YXBZ = #{it.YXBZ, jdbcType=VARCHAR},
			WYJLH = #{it.WYJLH, jdbcType=VARCHAR},
			SJCJSJ = #{it.SJCJSJ, jdbcType=VARCHAR},
			SJGXSJ = #{it.SJGXSJ, jdbcType=VARCHAR},
			BBH = #{it.BBH, jdbcType=VARCHAR},
			BZNH = #{it.BZNH, jdbcType=VARCHAR},
			BZ = #{it.BZ, jdbcType=VARCHAR},
			BBMC = #{it.BBMC, jdbcType=VARCHAR},
			ZLZNYM = #{it.ZLZNYM, jdbcType=VARCHAR},
			ZLZNDZDA = #{it.ZLZNDZDA, jdbcType=VARCHAR}
		WHERE
			BZJSMLI = #{it.BZJSMLI}
	</foreach>
</update>