Oracle uses MyBatis to implement bulk INSERT or modify data (Oracle) ____oracle

MyBatis itself is very flexible, because you can write SQL in the XML file to operate, it can be inserted into the database at one time, so that only once submitted to the database, performance can be improved a lot. Here's an example:

First, add an interface to the Datamapper.java interface class:

void Batchinsertucfuserbatchimport (List<ucfuserbatchimport> ucfuserbatchimportlist) throws Exception;

Then, write the corresponding implementation SQL in Datamapper.xml, I'm using Oracle, and if it's MySQL or SQL Server, it might make a slightly different SQL statement:

<insert id= "Batchinsertucfuserbatchimport" parametertype= "java.util.List" > <! [cdata[INSERT into Ucf_user_batch_import (Id,ref_merchant,ref_user,real_name,reg_cell,rgn_code,cert_type,cert_no, Bank_name,bank_card_type,bank_code, Bank_card_no,bank_province,bank_city,bank_cell,cert_status,notice_url,gmt_ CREATE)]]> <foreach collection= "list" item= "item" index= "Index" open= "(" close= ")" Separator= "" union "> <! [cdata[Select #{item.id,jdbctype=numeric},#{item.merchantid,jdbctype=varchar},#{item.refuserid,jdbctyp E=varchar}, #{item.realname,jdbctype=varchar},#{item.regcell,jdbctype=varchar},#{item.rgncode,jdbctype=varchar} , #{item.certtype,jdbctype=varchar},#{item.certno,jdbctype=varchar},#{item.bankname,jdbctype=varchar}, # {Item.bankcardtype,jdbctype=varchar},#{item.bankcode,jdbctype=varchar},#{item.bankcardno,jdbctype=varchar}, #{ Item.bankprovince,jdbctype=varchar},#{item.bankcity,jdbctype=varchar},#{item.Bankcell,jdbctype=varchar}, #{item.certstatus,jdbctype=varchar},#{item.noticeurl,jdbctype=varchar},sysdate
 From dual]]> </foreach> </insert>

Finally, call the interface where you need to BULK INSERT data:

Ucfuserbatchimportdao.batchinsertucfuserbatchimport (ucfuserbatchimportlist);

To illustrate the implementation of the XML file, using the foreach tag, is used to splice the internal string, the item is equivalent to a pointer, used to traverse the list of objects, each item attribute value to the internal SQL, the Union together,　　Executes a long SQL statement at a time. If we print the SQL execution statement from the background, we see this SQL statement

Insert into TABLE (ID, TEXT, stauts) 
(
select?,?,? The From dual Union 
select?,?,? The from dual Union 
Selec T??? From dual 
)

In terms of performance, no matter how many data is inserted, it is only submitted once to the database, which can increase the efficiency significantly.

Bulk add operations used in the project:

	<insert id= "Insertbatchagentsharedetail" parametertype= "java.util.ArrayList" >
		<![ cdata[
		Insert All 
		]]>
		<foreach collection= "list" item= "detail" index= "index" > 
			<![ cdata[into 
				ucf_agent_share_detail (id,total_amount,total_fee_amount,total_agent_fee_amount,total_share_ AMOUNT,
					share_record_id,product_code,card_type,trade_count,settle_detail_id) 
				VALUES (#{detail.id, jdbctype=varchar},#{detail.totalamount,jdbctype=numeric},#{detail.totalfeeamount,jdbctype=numeric},#{ Detail.totalagentfeeamount,jdbctype=numeric},
					#{detail.totalshareamount,jdbctype=numeric},#{ Detail.sharerecordid,jdbctype=varchar},#{detail.productcode,jdbctype=varchar},
					#{detail.cardType,jdbcType =varchar},#{detail.tradecount,jdbctype=numeric},#{detail.settledetailid,jdbctype=varchar})
			]]>
		</foreach>
		<![ cdata[
		SELECT * from dual
		]]>
	</insert>

Bulk modification operations used in the project:

<update id= "Updatebatchmerchantsettlerecord" parametertype= "java.util.ArrayList" >
        <foreach collection= "List" item= "record" index= "index" open= "Begin" close= "; > 
        	<![ cdata[UPDATE ucf_merchant_settle_record]]>
			<trim prefix= "SET" suffixoverrides= "," >
				<if test= " Null!= record.sharestatus and  '!= record.sharestatus ' >
	                <![ cdata[   share_status = #{record.sharestatus},]]>
				</if>
			</trim>
			<trim prefix= " WHERE "prefixoverrides=" and ">
				<if test=" null!= record.id and '!= record.id ">
	                <![ cdata[and ID = #{record.id}]]>
				</if>
			</trim>
        </foreach>
</update>

Reference:

http://blog.csdn.net/mahoking/article/details/46811865