mybatis之mysql&oracle 批量操作

最新推荐文章于 2022-02-28 15:35:26 发布

nimeijian

最新推荐文章于 2022-02-28 15:35:26 发布

阅读量813

点赞数

本文链接：https://blog.csdn.net/nimeijian/article/details/50380734

版权

1、MySQL
<!-- 更新数据对象 -->
<update id="updateDataObject" parameterType="com.fable.sys.bean.DataObjectBean">
	<!-- 更新fbs_dataobject主记录 -->
	UPDATE fbs_dataobject 
	SET object_name=#{objectName,jdbcType=VARCHAR},note=#{note,jdbcType=VARCHAR},update_id=#{updateId,jdbcType=VARCHAR},update_name=#{updateName,jdbcType=VARCHAR},updated_time=now()
		<!-- 只有自定义SQL类型可以修改SQL -->
		<if test="3 == type" >
			, fbs_sql=#{sql,jdbcType=CLOB}
		</if>
	WHERE id=#{id,jdbcType=VARCHAR};
	
	<!-- 清空指定dataObjectId下的所有子记录 -->
	DELETE FROM fbs_dataobject_field_label WHERE dataobject_id=#{id,jdbcType=VARCHAR};
	
	<!-- 再插入从表fbs_dataobject_field_label -->
	<!-- <include refid="createDataObjectFieldLabels"/> -->
	INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,note,creator_id,creator_name,created_time) 
		<foreach collection="fieldLabels" item= "fieldLabel" open="VALUES" separator="," close=";">
			(#{fieldLabel.id,jdbcType=VARCHAR},#{fieldLabel.sysId,jdbcType=VARCHAR},#{fieldLabel.dataobjectId,jdbcType=VARCHAR},
			#{fieldLabel.propertyName,jdbcType=VARCHAR},#{fieldLabel.displayTitle,jdbcType=VARCHAR},#{fieldLabel.note,jdbcType=VARCHAR},
			#{fieldLabel.creatorId,jdbcType=VARCHAR},#{fieldLabel.creatorName,jdbcType=VARCHAR},now())
		</foreach >
</update>

2、Oracle
<!-- 更新数据对象 -->
<update id="updateDataObject" parameterType="com.fable.sys.bean.DataObjectBean">
	BEGIN
	<!-- 更新fbs_dataobject主记录 -->
	UPDATE fbs_dataobject 
	SET object_name=#{objectName,jdbcType=VARCHAR},note=#{note,jdbcType=VARCHAR},update_id=#{updateId,jdbcType=VARCHAR},update_name=#{updateName,jdbcType=VARCHAR},updated_time=now()
		<!-- 只有自定义SQL类型可以修改SQL -->
		<if test="3 == type" >
			, sql=#{sql,jdbcType=CLOB}
		</if>
	WHERE id=#{id,jdbcType=VARCHAR};
	
	<!-- 清空指定dataObjectId下的所有子记录 -->
	DELETE FROM fbs_dataobject_field_label WHERE dataobject_id=#{id,jdbcType=VARCHAR};
	
	<!-- 再插入从表fbs_dataobject_field_label -->
	<!-- <include refid="createDataObjectFieldLabels"/> -->
	INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,note,creator_id,creator_name,created_time) 
		<foreach collection="fieldLabels" item= "fieldLabel" separator="union all">
			SELECT #{fieldLabel.id,jdbcType=VARCHAR},#{fieldLabel.sysId,jdbcType=VARCHAR},#{fieldLabel.dataobjectId,jdbcType=VARCHAR},
				#{fieldLabel.propertyName,jdbcType=VARCHAR},#{fieldLabel.displayTitle,jdbcType=VARCHAR},#{fieldLabel.note,jdbcType=VARCHAR},
				#{fieldLabel.creatorId,jdbcType=VARCHAR},#{fieldLabel.creatorName,jdbcType=VARCHAR},now()
			FROM dual
		</foreach >;
	
	END;
</update>