Mybatis批处理操作
文章目录
- Mybatis批处理操作
- 一、Foreach方式
- 批量插入
- 批量更新
- 批量删除
- 二、Batch方式
- 三、preparedStatement方式
- 总结
一、Foreach方式
批量插入
插入方式一：
注：（oracle不支持，mysql支持，有返回插入行数）
<insert id="insertByBatch" parameterType="java.util.List">
INSERT INTO WBM_ERT_DED_RST_T
(
<include refid="AllColumns" />
)
VALUES
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index" separator=",">
(
#{ertDedRstDbo.sysDate},
#{ertDedRstDbo.chgCard},
#{ertDedRstDbo.dedBatchId},
#{ertDedRstDbo.dedGroupId},
#{ertDedRstDbo.dedTaskId},
#{ertDedRstDbo.prcResult},
#{ertDedRstDbo.prcErrmsg},
#{ertDedRstDbo.specCode},
#{ertDedRstDbo.techBusinessId},
#{ertDedRstDbo.techTraceId},
#{ertDedRstDbo.techDbName},
#{ertDedRstDbo.techMntTime},
#{ertDedRstDbo.techVersion},
#{ertDedRstDbo.techResv}
)
</foreach>
</insert>插入方式二：
注：（有返回插入行数）
<insert id="insertByBatch" parameterType="java.util.List">
INSERT ALL
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">
INTO WBM_ERT_DED_RST_T
(
<include refid="AllColumns" />
)
VALUES
(
#{ertDedRstDbo.sysDate},
#{ertDedRstDbo.chgCard},
#{ertDedRstDbo.dedBatchId},
#{ertDedRstDbo.dedGroupId},
#{ertDedRstDbo.dedTaskId},
#{ertDedRstDbo.prcResult},
#{ertDedRstDbo.prcErrmsg},
#{ertDedRstDbo.specCode},
#{ertDedRstDbo.techBusinessId},
#{ertDedRstDbo.techTraceId},
#{ertDedRstDbo.techDbName},
#{ertDedRstDbo.techMntTime},
#{ertDedRstDbo.techVersion},
#{ertDedRstDbo.techResv}
)
</foreach>
SELECT 1 FROM DUAL
</insert>插入方式三：
<insert id="insertList" parameterType="java.util.List">
INSERT INTO WFE_RULE_DIM_T (
<include refid="AllColumn"/>
)
<foreach collection="ruleDimList" item="ruleDim" open="(" close=")" separator="union">SELECT
#{ruleDim.itemCode},
#{ruleDim.ruleNumber},
#{ruleDim.seqGroup},
#{ruleDim.seqInner},
#{ruleDim.dimCode},
#{ruleDim.opCode},
#{ruleDim.dimValue},
' ',
#{ruleDim.techBusinessId},
#{ruleDim.techTraceId},
#{ruleDim.techDbName},
#{ruleDim.techMntTime},
#{ruleDim.techMntUser},
#{ruleDim.techVersion},
#{ruleDim.techResv} FROM DUAL
</foreach>
</insert>插入方式四：
insert into t1 (p1,p2,p3,p4) select id,'2','3',name from t2 where ....该方式，是通过select构造得到的结果集，批量插入另一个表中。
批量更新
更新方式一：
注：（oracle和mysql数据库都支持，且有返回更新行数）
<update id="updateByBatch" parameterType="java.util.List">
UPDATE WBM_ERT_DED_RST_T
<trim prefix="SET" suffixOverrides=",">
<trim prefix="PRC_RESULT = CASE" suffix="END,">
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">
WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN #{ertDedRstDbo.prcResult}
</foreach>
</trim>
<trim prefix="TECH_MNT_TIME = CASE" suffix="END,">
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">
WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN #{ertDedRstDbo.techMntTime}
</foreach>
</trim>
<trim prefix="TECH_VERSION = CASE" suffix="END,">
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index">
WHEN CHG_CARD = #{ertDedRstDbo.chgCard} THEN MOD(TECH_VERSION, 9999) + 1
</foreach>
</trim>
</trim>
WHERE SYS_DATE = #{sysDate}
AND CHG_CARD IN
<foreach collection="ertDedRstDboList" index="index" item="ertDedRstDbo" separator="," open="(" close=")">
#{ertDedRstDbo.chgCard}
</foreach>
</update>更新方式二：
注：（orcale需要加上begin end，mysql未测试）
<update id="updateByBatch" parameterType="java.util.List">
<foreach collection="ertDedRstDboList" item="ertDedRstDbo" index="index" open="begin" close=";end;" separator=";">
UPDATE WBM_ERT_DED_RST_T
<set>
PRC_RESULT = #{ertDedRstDbo.prcResult},
PRC_ERRMSG = #{ertDedRstDbo.prcErrmsg},
TECH_BUSINESS_ID = #{ertDedRstDbo.techBusinessId},
TECH_TRACE_ID = #{ertDedRstDbo.techTraceId},
TECH_DB_NAME = #{ertDedRstDbo.techDbName},
TECH_MNT_TIME = #{ertDedRstDbo.techMntTime},
TECH_VERSION = MOD(TECH_VERSION, 9999) + 1
</set>
WHERE SYS_DATE = #{sysDate}
</foreach>
</update>批量删除
注：（oracle和mysql数据库都支持，且有返回更新行数）
<select id="deleteByBatch" resultMap="resultMap">
DELETE FROM WBM_ERT_DED_RST_T
WHERE SYS_DATE = #{sysDate}
AND CHG_CARD IN
<foreach item="chgCard" separator="," open="(" close=")" collection="chgCardSet">
#{chgCard}
</foreach>
</select>二、Batch方式
（示例）：
@Transactional
publicvoid test() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
ErtDedRstMapper ertDedRstMapper = sqlSession.getMapper(ErtDedRstMapper.class);
for (Map.Entry<String, Integer> entry : chgCardCountMap.entrySet()) {
String chgCard = entry.getKey();
List<String> resultAndErrmsgList = multiValueMap.get(chgCard);
String prcResult = resultAndErrmsgList.get(0);
String prcErrmsg = resultAndErrmsgList.get(1);
ErtDedRstDbo ertPrcDtlDbo = ErtDedRstDbo.builder()
.sysDate(x1.getSysDate())
.chgCard(chgCard)
.dedBatchId(x1.getDedBatchId())
.dedGroupId(x1.getDedGroupId())
.dedTaskId(x1.getDedTaskId())
.prcResult(prcResult)
.prcErrmsg(prcErrmsg)
.build();
if (entry.getValue() == 1) {
ertDedRstMapper.insert(ertPrcDtlDbo);
}else {
TechFieldDbo techFieldDbo = TechFieldDbo.builder().build();
ertDedRstMapper.update(x1.getSysDate(), chgCard, x1.getDedBatchId(), x1.getDedGroupId(), x1.getDedTaskId(),
prcResult, prcErrmsg, techFieldDbo);
}
}
}注意：ertDedRstMapper不能直接使用@Autowired注入的，需要在开启批处理方式之后重新获取，且此时循环内的insert和update返回值不是影响行数。
在上面例子中，在 Service 中直接注入了 SqlSessionFactory，通过下面方法获取了一个可以批量提交的 SqlSession：
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);后续使用该SqlSession 。
注意事项（这里理解可能有误，仅供参考）
- 事务
由于在 Spring 集成的情况下，事务连接由 Spring 管理（SpringManagedTransaction），所以这里不需要手动关闭 sqlSession，在这里手动提交（commit）或者回滚（rollback）也是无效的。 - 批量提交
批量提交只能应用于 insert, update, delete。
并且在批量提交使用时，如果在操作同一SQL时中间插入了其他数据库操作，就会让批量提交方式变成普通的执行方式，所以在使用批量提交时，要控制好 SQL 执行顺序。
三、preparedStatement方式
以springboot项目为基础，不说原生jdbc（原生jdbc需要一步步去获取数据连接,较为繁琐）
@Autowiredprivate
SqlSessionTemplate sqlSessionTemplate;
Private String sqlText = “INSERT INTO WRU_RULE_GLOB_REMARK_T ( TECH_MNT_TIME , TECH_VERSION , TECH_RESV ) VALUES( ?,?,? )”;
@Transactional
public void test() {
try {
Connection connection = sqlSessionTemplate.getConnection();
//connection.setAutoCommit(false);
for (int i = 0; i < 100; i++) {
this.preparedStatement = connection.prepareStatement(sqlText);
preparedStatement.setString(parameterIndex++, “1”);
preparedStatement.setString(parameterIndex++, “2”);
preparedStatement.setString(parameterIndex++, “3”);
preparedStatement.addBatch();
//connection.commit();
//connection.setAutoCommit(true);
}
int[] updateCount = preparedStatement.executeBatch();
} catch (SQLException exception) {
throw exception;
} finally {
preparedStatement.close();
}
}注意：使用JDBC方式获取的数据连接，需要自己手动设置事务提交事件。
使用了@Transactional事务注解后，同batch方式一样，不需要手动执行commit()方法来提交事务。
总结
Foreach方式：
当数据过多时，可能生成的动态sql过大，mysql默认仅1M的sql字符串，过长可能会执行失败，可以通过修改配置文件，batch方式无限制。在sql循环时设置batch与否其实执行时间差别不是很大，几乎可以忽略不计。所以其实如果不是特别要求性能。可以直接在sql中使用for循环即可。
Batch方式：
如果需要使用batch，请在需要的函数上面设置batch，不要全局使用。因为batch也是有副作用的。比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，；此外，对于update、delete无法返回更新、插入条数。这在某型情形下是不符合业务要求
批处理选择
已知sql执行效率：insert > delete > update，因此对应的批处理也是一样的顺序。
场景示例：批处理，若数据表未存在记录则作插入，若已存在则作更新。
方案一：使用select + update + insert做批处理，但此方案效率较低。
方案二：使用select + delete + insert 做批处理，推荐使用。