项目背景:设计到excel导入,数据量也比较大,保证性能的情况下还要考虑到:如果数据中有这条数据的主键,则更新(update),不存在的情况,执行插入(insert)。
mybatis代码:
<insert id="saveOrUpdateBatch" parameterType="java.util.List">
MERGE INTO T_KA02 T
USING (
<foreach collection="list" item="item" index="index" separator="union" >
SELECT
#{item.socSrtDireCd,jdbcType=VARCHAR} AS socSrtDireCd,
#{item.socSrtDireNm,jdbcType=VARCHAR } AS socSrtDireNm,
#{item.doseForm,jdbcType=VARCHAR } AS doseForm,
#{item.ruleUseDm,jdbcType=VARCHAR} AS ruleUseDm
FROM DUAL
</foreach>) T1
ON (T1.socSrtDireCd= T.SOC_SRT_DIRE_CD)
WHEN MATCHED THEN
UPDATE SET T.SOC_SRT_DIRE_NM=T1.socSrtDireNm,
T.DOSE_FORM=T1.doseForm,
T.RULE_USE_DM=T1.ruleUseDm
WHEN NOT MATCHED THEN
INSERT
(SOC_SRT_DIRE_CD,SOC_SRT_DIRE_NM,DOSE_FORM,RULE_USE_DM)
VALUES
(T1.socSrtDireCd,T1.socSrtDireNm,T1.doseForm,T1.ruleUseDm)
</insert>
遇到的问题:
如果数据量太大,则拼接sql时抛异常,大致是说sql语句太长,获取不到完整的套接字。这时候,可以采用分批来执行的方式。我的处理方式大致如下:
@Override
public void saveOrUpdateBatch(List<DrugsCatalog> list) {
int count=0;
int bufSize=200;
List<DrugsCatalog> tmpList=new ArrayList<>();
int j=0;
for (int i = 0; i < list.size(); i++) {
j++;
tmpList.add(list.get(i));
if(j==bufSize){
int save = drugsCatalogMapper.saveOrUpdateBatch(tmpList);
count+=save;
tmpList.clear();
j=0;
}
}
int save2 = drugsCatalogMapper.saveOrUpdateBatch(tmpList);
count+=save2;
log.info("成功导入数据"+count+"条!");
}
这是,完美解决批量保存或更新问题。