Java 批量插入批量更新数据到SQL Server

inert和update在数据量庞大的情况下，速度非常慢，那怎么解决呢？可以用批量插入和批量更新

批量插入11万条数据，如下图

 

第一个图数据库已经有11万的情况下测试，第二个图数据库已经有22万的情况下测试

 

 

 

 

 我的代码是同时测试批量插入和批量更新，

 

Controller层

    @Test
    public String sayHello(){
        //获取数据保存到List，红色部分你们自己获取数据
        HelloController helloController=new HelloController();
        long strMillis = System.currentTimeMillis();
        List<LogisticsReturn> fileList = helloController.getFileList();
        long endMillis = System.currentTimeMillis();
        long time = endMillis - strMillis;
        
        //批量更新
        strMillis = System.currentTimeMillis();
        logisticsReturnService.saveAll(fileList);
        endMillis = System.currentTimeMillis();
        long time2 = endMillis - strMillis;

        //批量修改
        strMillis = System.currentTimeMillis();
        logisticsReturnService.batchUpdate(fileList);
        endMillis = System.currentTimeMillis();
        long time3 = endMillis - strMillis;

        System.out.println("解析使用时间："+time);
        System.out.println("插入使用时间："+time2);
        System.out.println("更新使用时间："+time3);
    }

 

ServiceImpl层

@Service
public class LogisticsReturnServiceImpl implements LogisticsReturnService {

    static Logger logger = LogManager.getLogger(LogisticsReturnServiceImpl .class);
    @Autowired
    SqlSessionTemplate sqlSessionTemplate;

    //将DAO注入Service层
    @Autowired
    private LogisticsReturnMapper logisticsReturnMapper;

    @Override
    public void saveAll(List<LogisticsReturn> logisticsReturns) {
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        LogisticsReturnMapper dao = session.getMapper(LogisticsReturnMapper.class);
        int size = logisticsReturns.size();
        try {
            for (int i = 0; i < size; i++) {
                dao.insert(logisticsReturns.get(i));
                if (i % 10000 == 0 || i == size - 1) {
                    //手动每10000个一提交，提交后无法回滚
                    session.commit();
                    //清理缓存，防止溢出
                    session.clearCache();
                }
            }
        } catch (Exception e) {
            logger.error("批量保存失败：" ,e);
            session.rollback();
        } finally {
            session.close();
        }
    }

    @Override
    public void batchUpdate(List<LogisticsReturn> logisticsReturns) {
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        LogisticsReturnMapper dao = session.getMapper(LogisticsReturnMapper.class);
        int size = logisticsReturns.size();
        try {
            List<LogisticsReturn> temp=new ArrayList<>();
            for (int i = 0; i < size; i++) {
                temp.add(logisticsReturns.get(i));

                if (i % 500 == 0 || i == size - 1) {
                    //手动每500个一提交，提交后无法回滚
                    dao.updateBatch(temp);
                    temp=new ArrayList<>();
                    session.commit();
                    //清理缓存，防止溢出
                    session.clearCache();
                }
            }
        } catch (Exception e) {
            logger.error("批量更新失败：" ,e);
            session.rollback();
        } finally {
            session.close();
        }
    }
}

 

mybatis的SQL语句

<insert id="insert" parameterType="LogisticsReturn">
        insert into [dbo].[LogisticsReturn]
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="guid != null and guid != ''">guid,</if>
            <if test="logisticsCode != null and logisticsCode != ''">logisticsCode,</if>
            <if test="logisticsNo != null and logisticsNo != ''">logisticsNo,</if>
            <if test="returnStatus != null and returnStatus != ''">returnStatus,</if>
            <if test="returnTime != null and returnTime != ''">returnTime,</if>
            <if test="returnInfo != null and returnInfo != ''">returnInfo,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="guid != null and guid != ''"> #{guid},</if>
            <if test="logisticsCode != null and logisticsCode != ''">#{logisticsCode},</if>
            <if test="logisticsNo != null and logisticsNo != ''"> #{logisticsNo},</if>
            <if test="returnStatus != null and returnStatus != ''">#{returnStatus},</if>
            <if test="returnTime != null and returnTime != ''"> #{returnTime},</if>
            <if test="returnInfo != null and returnInfo != ''">#{returnInfo},</if>
        </trim>
    </insert>

    <update id="updateBatch" parameterType="java.util.List">
        update [dbo].[LogisticsReturn]
        <trim prefix="set" suffixOverrides=",">

            <trim prefix="returnInfo =case" suffix="end," >
                <foreach collection="list" item="i" index="index">
                    <if test="i.returnInfo!=null">
                        when( logisticsNo=#{i.logisticsNo} ) then #{i.returnInfo}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="logisticsReturns" separator="or" item="i" index="index" >
            logisticsNo=#{i.logisticsNo}
        </foreach>
    </update>

 

如果你不懂SQL的when then是什么，建议先看看这条语句

select      ID,Username,namer=(case when(score<='50')    then '实习'
 
                                    when(score>'50'  and  score<='500' )   then '赤脚医生'    
 
                                    when(score>'500'  and score<='1000' )   then '村卫生员' 
 
                                    when(score>'1000'  and score<='1500' )   then '乡卫生员' 
 
                                    when(score>'1500'  and score<='2000' )   then '镇卫生员'
 
                                    when(score>'2000'  and score<='3000' )   then '医师'
 
                                    when(score>'3000'  and score<='5000' )   then '主治医师'
 
                                    when(score>'5000'  and score<='10000' )   then '副主任医师'
 
                                    when(score>'10000'  and score<='20000' )   then '主任医师'
 
                                    when(score>'20000'  and score<='50000' )   then '健康大使'
 
                                    else   '健康大使'  end ), (SELECT count(id)
 
         FROM  jk01_YiWen_Question  
 
         WHERE  UserID =  dbo.jk01_Member.ID)  as  questionnum  
 
   from  jk01_Member

 

Batch速度比insert和update提升好几倍，看完点个推荐看吧。