JPA的saveAll方法的效率居然高于 jdbcTemplate.batchUpdate方法

 @DS(DataSourceTypeEnum.amdb_collect)
        @Override
        public void dropAndSave(List<PsInfoCollect> psInfoCollects){
            psMonitorInfoCollectRepository.deleteAllItem();
            long a=System.currentTimeMillis();
            psMonitorInfoCollectRepository.saveAll(psInfoCollects);
          //  batchInsert(psInfoCollects);
            long b = System.currentTimeMillis();
            logger.error("批量插入用时：" + (b-a) +"毫秒");
        }

 当我使用jpa的saveAll方法时，居然发现这里的插入是一条一条的插入（插入900多条数据，此时可以看到大概是需要2秒钟）。然后就想到用jdbc的批量插入方法。于是使用

jdbcTemplate.batchUpdate方法。

 @DS(DataSourceTypeEnum.amdb_collect)
        @Override
        public void dropAndSave(List<PsInfoCollect> psInfoCollects){
            psMonitorInfoCollectRepository.deleteAllItem();
            long a=System.currentTimeMillis();
           // psMonitorInfoCollectRepository.saveAll(psInfoCollects);
            batchInsert(psInfoCollects);
            long b = System.currentTimeMillis();
            logger.error("批量插入用时：" + (b-a) +"毫秒");
        }

    private void batchInsert(List<PsInfoCollect> psInfoCollects) {
        String sql = "insert  " +
                "    into " +
                "        psinfo_collect " +
                "        (apply_range, ps_id, mp_id, pollutant_code, city_name, credit_code, daily_avg_standard, dqyl, effective_time, environment_principal, equip_name, exmax, exmin, guilv, high, industry_name, is_cdpf, is_important_mp, is_important_pol, is_important_ps, is_zs, jianguan_industry, latitude, longitude, maintance_unit, manufacture_date, manufacturer, mn, montiortype, mp_name, mp_status, mp_type, mp_type_name, outlet_name, outlet_status, outlet_yq, outlut_standard, phone, pol_name, pol_status, position, position_mp, province_name, ps_address, ps_name, ps_status, pwxk_bh, pwxk_bh_pk, pwxk_industry, quxian_name, quxiang, region_code, sampling_area, standard_code, standard_industry, standard_level, standard_name, standard_type, summer_begin_time, update_time, valley, winter_bengin_time, winter_outout_standard, year_limit, zhijing, caiyangzhouqi, cdpf_outlut_standard, area_code)  " +
                "    values " +
                "        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";



        List<Object[]> list = new ArrayList<>();
        for (PsInfoCollect psInfoCollect:psInfoCollects) {
            list.add(new Object[]{psInfoCollect.getApplyRange(),psInfoCollect.getPsId(),psInfoCollect.getMpId(),psInfoCollect.getPollutantCode(),psInfoCollect.getCityName(),psInfoCollect.getCreditCode(),psInfoCollect.getDailyAvgStandard(),psInfoCollect.getDqyl(),psInfoCollect.getEffectiveTime(),psInfoCollect.getEnvironmentPrincipal(),psInfoCollect.getEquipName(),psInfoCollect.getExmax(),psInfoCollect.getExmin(),psInfoCollect.getGuilv(),psInfoCollect.getHigh(),psInfoCollect.getIndustryName(),psInfoCollect.getIsCdpf(),psInfoCollect.getIsImportantMp(),psInfoCollect.getIsImportantPol(),psInfoCollect.getIsImportantPs(),psInfoCollect.getIsZs(),psInfoCollect.getJianguanIndustry(),psInfoCollect.getLatitude(),psInfoCollect.getLongitude(),psInfoCollect.getMaintanceUnit(),psInfoCollect.getManufactureDate(),psInfoCollect.getManufacturer(),psInfoCollect.getMn(),psInfoCollect.getMontiortype(),psInfoCollect.getMpName(),psInfoCollect.getMpStatus(),psInfoCollect.getMpType(),psInfoCollect.getMpTypeName(),psInfoCollect.getOutletName(),psInfoCollect.getOutletStatus(),psInfoCollect.getOutletYq(),psInfoCollect.getOutlutStandard(),psInfoCollect.getPhone(),psInfoCollect.getPolName(),psInfoCollect.getPolStatus(),psInfoCollect.getPosition(),psInfoCollect.getPositionMp(),psInfoCollect.getProvinceName(),psInfoCollect.getPsAddress(),psInfoCollect.getPsName(),psInfoCollect.getPsStatus(),psInfoCollect.getPwxkBh(),psInfoCollect.getPwxkBhPk(),psInfoCollect.getPwxkIndustry(),psInfoCollect.getQuxianName(),psInfoCollect.getQuxiang(),psInfoCollect.getRegionCode(),psInfoCollect.getSamplingArea(),psInfoCollect.getStandardCode(),psInfoCollect.getStandardIndustry(),psInfoCollect.getStandardLevel(),psInfoCollect.getStandardName(),psInfoCollect.getStandardType(),psInfoCollect.getSummerBeginTime(),psInfoCollect.getUpdateTime(),psInfoCollect.getValley(),psInfoCollect.getWinterBenginTime(),psInfoCollect.getWinterOutoutStandard(),psInfoCollect.getYearLimit(),psInfoCollect.getZhijing(),psInfoCollect.getCaiyangzhouqi(),psInfoCollect.getCdpfOutlutStandard(),psInfoCollect.getAreaCode()});

        }

        jdbcTemplate.batchUpdate(sql, list);

    }

 执行完后发现

使用jdbc的批量查询居然需要16秒左右，远远超过了jpa的saveAll方法。

百度之后jdbcTemplate.batchUpdate方法。居然也是一条一条的插入。其中有一个解决方法是

在连接数据库的URL上加上参数rewriteBatchedStatements=true。但是此方法只对数据库是mysql有效。