Mybatis批量插入数据(Mysql / Oracle)

ゝ一纸荒年。 2022-05-16 12:54 309阅读 0赞

1. Mysql 的批量插入方式：

<insert id="insertDataByBatch">
    	   insert into RECORD_IMFORMATION
    		(recordId,toUserId,roomName,fromUserId,entityType,
    		 entityContent,url)
    		 
    		values
    	   <foreach collection="records" item="record"  separator="," >
    	   (#{record.recordId},#{record.toUserId},#{record.roomName},
    		 #{record.fromUserId},#{record.entityType},
    		 #{record.entityContent},#{record.url})
    	   </foreach>
    	</insert>

2. Oracle的批量插入方式：

<insert id="insertDataByBatch">
    	   insert into RECORD_IMFORMATION
    		(recordId,toUserId,roomName,fromUserId,entityType,
    		 entityContent,url)
    		SELECT A.* FROM (
    		   <foreach collection="records" item="record" index="index" separator="UNION ALL" >
    		   		SELECT
    			     #{record.recordId, jdbcType=VARCHAR} recordId,
    			     #{record.toUserId, jdbcType=VARCHAR} toUserId,
    			     #{record.roomName, jdbcType=VARCHAR} roomName,
    				 #{record.fromUserId, jdbcType=VARCHAR} fromUserId,
    				 #{record.entityType, jdbcType=VARCHAR} entityType,
    			 	 #{record.entityContent, jdbcType=VARCHAR} entityContent,
    			 	 #{record.url, jdbcType=VARCHAR} url
    			 	FROM dual
    		   </foreach>
    	   ) A
    	</insert>

**3. 动态插入Mysql不同的表**：

<insert id="insertData" statementType="STATEMENT">
    	   insert into ${tableName}
    		(${fields})
    		values(${values})	    
    	</insert>

**附**：

1. 需要Mybatis调用Sqlserver存储过程的话，可以参考：

[MyBatis 调用存储过程(详解)][MyBatis] , [mybatis 调用存储过程，获取其返回值(Sql Server) ][mybatis _Sql Server_]

[MyBatis]: https://blog.csdn.net/M_Jack/article/details/88061998
[mybatis _Sql Server_]: https://my.oschina.net/u/3398250/blog/983634