MyBatis实现mysql、oracle批量插入

最新推荐文章于 2024-10-14 17:56:52 发布

机械熊猫侠

最新推荐文章于 2024-10-14 17:56:52 发布

阅读量1.3k

点赞数

本文链接：https://blog.csdn.net/qq_39768738/article/details/103509593

版权

一、mybatis insert标签属性　　　　　　　　　　　　　　　　　　　　　　　　　　

1、parameterType：入参的全限定类名或类型别名。

2、keyColumn：设置数据表自动生成的主键名。对特定数据库（如PostgreSQL），若自动生成的主键不是第一个字段则必须设置。

3、keyProperty：默认值unset，用于设置getGeneratedKeys方法或selectKey子元素返回值将赋值到领域模型的哪个属性中

4、useGeneratedKeys：取值范围true、false（默认值），设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中。MySQL和SQLServer执行auto-generated key field，因此当数据库设置好自增长主键后，可通过JDBC的getGeneratedKeys方法获取。但像Oralce等不支持auto-generated key field的数据库就不能用这种方法获取主键了。

5、statementType：取值范围statement、prepared（默认值）、callable。

6、flushCache：取值范围true(默认值)、false，设置执行该操作后是否会清空二级缓存和本地缓存。

6、timeout：默认为unset（依赖jdbc驱动器的设置），设置执行该操作的最大时限，超时将抛异常。

7、databaseId：取值范围oracle、mysql等，表示数据库厂家，元素内部可通过<if test="_databaseId = 'oracle'">来为特定数据库指定不同的sql语句。

二、一般的insert操作（返回值为插入的记录数目）　　　　　

/**
 * 添加学生信息
 * @param student 学生实例
 * @return 成功操作的记录数目
 */
int add(Student student);

<insert id="add" parameterType="Student">
  insert into Student(name, age) values(#{name}, #{age})
</insert>

三、执行insert操作后获取记录主键

mapper.xml分为两种情况了，一种是数据库（如MySQL、SQLServer）支持auto-generated key field，另一种是数据库（如Oracle）不支持auto-generated key field的。

1. 数据库（如MySQL、SQLServer）支持auto-generated key field的情况

方法1：
<insert id="add" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
  insert into Student(name, age) values(#{name}, #{age})
</insert>

方法2：
<insert id="add" parameterType="Student">
  // 下面是mySQL获取最近一次插入记录的主键值的方式
  <selectKey resultType="_long" keyProperty="id" order="AFTER">
    select @@IDENTITY as id
  </selectKey>
  insert into Student(name, age) values(#{name}, #{age})
</insert>

由于方法2获取主键的方式依赖数据库本身，因此推荐使用方法1。


// mysql批量插入
<insert id ="insertCodeBatch" parameterType="java.util.List">
	<selectKey resultType="int" keyProperty="id" order="AFTER">
		select LAST_INSERT_ID()
	</selectKey >
	insert into redeem_code(bach_id, code, type, facevalue,create_user,create_time)
	values
	<foreach collection="list" item="reddemCode" index="index" separator ="," open="(" close=")">
		#{reddemCode.batchId}, #{reddemCode.code},
		#{reddemCode.type},
		#{reddemCode.facevalue},
		#{reddemCode.createUser}, #{reddemCode.createTime}
	</foreach >
</insert>

2. 数据库(如Oracle)不支持auto-generated key field的情况

// 单条插入
<insert id="singleInsert">
	<selectKey keyProperty="id" resultType="int" order="BEFORE">
	    select MAX(ID)+1 from GH_BAS_HOLIDAY_CONFIG
	</selectKey>
	insert into GH_BAS_HOLIDAY_CONFIG (<include refid="Base_Column_List"/>)
	values(#{id}, #{year}, #{startYmd}, #{endYmd}, #{name})
</insert>

// 批量插入，以下两个方法都需要依靠序列器
<insert id="insertGhEnergy" keyProperty="id" keyColumn="id" useGeneratedKeys="false">
    insert into GH_FC_YENERGY(ID, NETID, CALIBERID, YEAR, RESULT, RESULT_TYPE, CREATEDATE, USERNO)
    // SEQ_AUTOID.nextval为Oracle中创建的序列，在外包一层循环是为了id自增
    SELECT SEQ_AUTOID.nextval as id, t.* FROM (
    // UNION ALL：把多个归属类型结果当作临时表并起来插入到数据库中
    <foreach collection="list" item="ghFcEnergy" open="(" close=")" separator="UNION all">
        select
            #{ghFcEnergy.netid},
            #{ghFcEnergy.caliberid},
            #{ghFcEnergy.year},
            #{ghFcEnergy.result},
            #{ghFcEnergy.resultType},
            #{ghFcEnergy.createdate},
            #{ghFcEnergy.userno}
         FROM dual
     </foreach>
     ) t
</insert>


// 使用存储过程实现
<insert id="insertBatch1" parameterType="java.util.List">
    begin
        <foreach collection="list" item="item" index="index">
            insert into WF_TASKTEMP_DEALSTAFF
            (DEAL_ID, TEMP_ID, STAFF_ID,DEAL_TYPE, STATUS)
            values(WF_TASKTEMP_DEALSTAFF$SEQ.nextval,
                #{item.tempId,jdbcType=VARCHAR},
                #{item.staffId,jdbcType=VARCHAR},
                #{item.dealType,jdbcType=VARCHAR},
                #{item.status,jdbcType=CHAR}
            );
        </foreach>
    end;
</insert>

1、selectKey标签属性（在insert元素和update元素中插入查询语句）

1）keyProperty：默认值unset，用于设置getGeneratedKeys方法或selectKey子元素返回值将赋值到领域模型的哪个属性中。

2）resultType：keyPropety所指向的属性类全限定类名或类型别名。

3）order：取值范围BEFORE、AFTER，指定是在insert语句前还是后执行selectKey操作。

4）statementType：取值范围STATEMENT、PREPARED（默认值）、CALLABLE。

注意：selectKey操作会将操作查询结果赋值到insert元素的parameterType的入参实例下对应的属性中，并提供给insert语句使用。

2、创建序列

-- 序列名
create sequence SEQ_AUTOID
-- 每次加几个
INCREMENT BY 1 
-- 从1开始计数
START WITH 10 
-- 不设置最大值
NOMAXVALUE
-- 一直累加，不循环
NOCYCLE 
-- 如果设置cache 100，当sequence取到90时突然断电，那么在重启数据库后，sequence的值将从101开始
NOCACHE;

四、批量更新

// oracle批量更新
<update id="batchUpdate" parameterType="list">
	<foreach collection="list" item="ghBasHolidayConfig" open="begin" separator=";" close=";end;">
		update GH_BAS_HOLIDAY_CONFIG
		<set>
			<if test="ghBasHolidayConfig.startYmd != null and ghBasHolidayConfig.startYmd != ''">
				STARTYMD=#{ghBasHolidayConfig.startYmd},
			</if>
			<if test="ghBasHolidayConfig.endYmd != null and ghBasHolidayConfig.endYmd != ''">
				ENDYMD=#{ghBasHolidayConfig.endYmd},
			</if>
			<if test="ghBasHolidayConfig.name != null">
				NAME=#{ghBasHolidayConfig.name}
			</if>
		</set>
		where ID=#{ghBasHolidayConfig.id}
	</foreach>
</update>

mybatis批量更新oracle数据时，返回的结果是-1，针对这种情况需要编写以下代码。

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
  for (YourItem item : list) {
    sqlSession.update("biz-update", item);
  }
  List<BatchResult> results = sqlSession.flushStatements();
  int totalNumberOfAffectedRows = Arrays.stream(results.get(0).getUpdateCounts()).sum();
  sqlSession.commit();
} finally {
  sqlSession.close();
}


1、sqlSession#flushStatements()返回的列表BatchResult。在这种情况下，批处理中只有一个语句，因此列表大小为1。如果执行多个语句（例如，更新表A，然后插入表B），则列表可能包含多个BatchResults。
2、BatchResult#getUpdateCounts()返回一个int数组。第一个元素（= int）是第一个UPDATE更新的行数，第二个元素是第二个UPDATE更新的行数，依此类推。

五、存在即更新，不存在即插入

// oracle存在即更新，不存在即插入
<insert id="save">
	MERGE INTO GH_BAS_HOLIDAY_CONFIG t
	USING (
	<foreach collection="list" item="ghBasHolidayConfig" index="index" separator="union">
		SELECT
		#{ghBasHolidayConfig.id} ID,
		#{ghBasHolidayConfig.year} YEAR,
		#{ghBasHolidayConfig.startYmd} STARTYMD,
		#{ghBasHolidayConfig.endYmd} ENDYMD,
		#{ghBasHolidayConfig.name} NAME
		FROM DUAL
	</foreach>) t1
	ON (t.ID = t1.ID)
	WHEN MATCHED THEN
	  UPDATE SET t.STARTYMD = t1.STARTYMD, t.ENDYMD = t1.ENDYMD, t.NAME = t1.NAME
	WHEN NOT MATCHED THEN
	  INSERT(<include refid="Base_Column_List"/>) VALUES (t1.ID, t1.YEAR, t1.STARTYMD, t1.ENDYMD, t1.NAME)
</insert>

六、mybatis开启batch模式批量更改

@Autowired
private SqlSessionFactory sqlSessionFactory;

@Transactional(rollbackFor = Exception.class)
// BiConsumer函数接口
public <T, M> void insertOrUpdateBatch(List<T> dataList, int batchCommitSize, Class<M> mapperClass, BiConsumer<M, List<T>> function) {
	SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
	M modelMapper = sqlSession.getMapper(mapperClass);
	try {
		// 批量提交
		for (int i = 0, length = dataList.size(); i < length; i += batchCommitSize) {
			int startIndex = i;
			if(i > 0){
				startIndex = i + 1;
			}
			int endIndex = Math.min(i + batchCommitSize, length);
			List<T> newDataList =  dataList.subList(startIndex, total);
			function.accept(modelMapper, newDataList);
			
			sqlSession.flushStatements();
			sqlSession.commit();
		}
	} catch(Exception e) {
		e.printStackTrace();
	} finally {
        // 关闭sqlSession、sqlSessionFactory
		closeSqlSession(sqlSession, sqlSessionFactory);
	}	
}


//批量插入
mybatisBatchExecutor.insertOrUpdateBatch(dataList, BatchTestMapper.class, (mapper, data)-> {
            mapper.insertDataList(data);
});

//批量更新
mybatisBatchExecutor.insertOrUpdateBatch(dataList, BatchTestMapper.class, (mapper, data)-> {
            mapper.updateBatchByPrimaryKeySelective(data);
});

关于batch模式

1、Mybatis内置的ExecutorType有3种，默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句。
 
2、但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的。

3、batch可以配合url设置，在数据库url中添加rewriteBatchedStatements=true，如jdbc:mysql://127.0.0.1:3306/project?rewriteBatchedStatements=true，这样效率大大提高。

注意事项

1、事务
由于在 Spring 集成的情况下，事务连接由 Spring 管理（SpringManagedTransaction），所以这里不需要手动关闭 sqlSession，在这里手动提交（commit）或者回滚（rollback）也是无效的。

2、批量提交
批量提交只能应用于 insert、update、delete。并且在批量提交使用时，如果在操作同一SQL时中间插入了其他数据库操作，就会让批量提交方式变成普通的执行方式，所以在使用批量提交时，要控制好SQL执行顺序。