NamedParameterJdbcTemplate+JDBC+批量操作+事务(速度相比于NamedParameterJdbcTemplate批量操作快)

1.只是用NamedParameterJdbcTemplate做批量操作当数据量比较大的时候，其速度并不是太让人满意，我做了一次10万数据的插入就用了一天多的时间，所以才想办法解决看看能不能再提高点速度

1.使用NamedParameterJdbcTemplate循环遍历插入数据，这种速度还行，六万数据在10几分钟就搞定

 @Transactional(rollbackFor = Exception.class)
    public void runPieChartIndexDataToOtherSource(NamedParameterJdbcTemplate namedJdbcTemplate, Date date, String merKuName) {
        //查询商户表中未删除的、激活的商户
        StringBuilder sql=new StringBuilder("SELECT count(1) FROM o2o_merchant t WHERE t.is_del = '0' AND t. STATUS = '0'");
        //执行查询获取商户数量
        Integer merchant_nums = namedJdbcTemplate.queryForObject(sql.toString(), new HashMap<>(), Integer.class);
        //创建格式化日期对象
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        sql=new StringBuilder("select source,source_name,payment,count(1) as order_total,SUM(IFNULL(total_amount,0)) as order_amount from sdb_b2c_orders where FROM_UNIXTIME(createtime,'%Y-%m-%d') = '"+format.format(date)+"' GROUP BY source,source_name,payment");
        List<Map<String, Object>> list = namedJdbcTemplate.queryForList(sql.toString(),new HashMap<>());
        if(list.size()>0){
            for (Map<String, Object> map : list) {
                IndexPieChartData indexPieChartData = new IndexPieChartData();
                //获取商圈名称
                String source_name = (String) map.get("source_name");
                //获取订单来源
                String source = (String) map.get("source");
                //订单数
                String order_total = String.valueOf(map.get("order_total"));
                //订单金额
                String order_amount = String.valueOf(map.get("order_amount"));
                //支付方式
                String payment = (String) map.get("payment");
                //封装数据
                indexPieChartData.setSource_name(source_name);
                indexPieChartData.setMerchant_nums(String.valueOf(merchant_nums));
                indexPieChartData.setSource(source);
                indexPieChartData.setPayment(payment);
                indexPieChartData.setOrder_total(order_total);
                indexPieChartData.setOrder_amount(order_amount);
                indexPieChartData.setYear(DateUtils.getDateYear(date));
                indexPieChartData.setMonth(DateUtils.getDateMonth(date));
                indexPieChartData.setDate(format.format(date));
                sql = new StringBuilder("insert into sdb_pie_chart_data (source_name,merchant_nums,source,order_total,order_amount,payment,year,month,date)values(:source_name,:merchant_nums,:source,:order_total,:order_amount,:payment,:year,:month,:date)");
                namedJdbcTemplate.update(sql.toString(),new BeanPropertySqlParameterSource(indexPieChartData));
            }
        }
    }

2.直接批量操作，把集合作为参数执行，这种对于小规模的数据量还行，大规模的速度待优化

public void runMerchantIncomeData(NamedParameterJdbcTemplate namedJdbcTemplate, Date date, String merKuName) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String year = DateUtils.getDateYear(date);
        String month = DateUtils.getDateMonth(date);
        String date2 = format.format(date);
        //创建sql语句
        StringBuilder sql = new StringBuilder("SELECT\n" +
                "\tom.mer_id AS mer_no,\n" +
                "\tom.mer_name AS mer_name,\n" +
                "\tIFNULL( SUM( sbo.total_amount ), 0 ) AS order_amount,\n" +
                "\t(case when sbo.order_id = ''then 0 when sbo.order_id is null then 0 else count(1)end) as order_total,\n" +
                "\tomc.platform_member_id,\n" +
                "\tom.mer_addr AS mer_address,\n" +
                "\tom.contact_person,\n" +
                "\tom.contact_mobile \n" +
                "FROM\n" +
                "\to2o_merchant AS om\n" +
                "\tLEFT JOIN sdb_b2c_orders AS sbo ON sbo.merchant_bn = om.mer_id \n" +
                "\tAND FROM_UNIXTIME( createtime, '%Y-%m-%d' ) = '" + date2 + "'\n" +
                "\tLEFT JOIN o2o_member_collection AS omc ON omc.mer_id = om.mer_id \n" +
                "GROUP BY\n" +
                "\tom.mer_id,\n" +
                "\tomc.platform_member_id");
        //查询数据
        List<O2oMerchantStatistics> list = namedJdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(O2oMerchantStatistics.class));
        if(list!=null&&list.size()>0){
            SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
            String insertSql = "INSERT INTO o2o_merchant_statistics ( mer_no, mer_name, order_amount, order_total, mer_address, contact_person, contact_mobile,source_name, YEAR, MONTH, date )\n" +
                    "VALUES\n" +
                    "\t(:mer_no,:mer_name,:order_amount,:order_total,:mer_address,:contact_person,:contact_mobile,'" + merKuName + "', '" + year + "', '" + month + "', '" + date2 + "')";
            //插入商圈
            int[] ints = namedJdbcTemplate.batchUpdate(insertSql, params);
            //插入平台
            int[] ints1 = namedParameterJdbcTemplate.batchUpdate(insertSql, params);
            logger.info("------------插入【"+merKuName+"】总计【"+ints.length+"】条数据------------");
            logger.info("------------插入【etoneo2o】总计【"+ints1.length+"】条数据------------");
        }else{
            logger.info("无数据可操作");
        }
    }

3.NamedParameterJdbcTemplate+JDBC+批量+事务，之所以混用是因为再用原生的jdbc时对resultSet的处理比较麻烦，所以用NamedParameterJdbcTemplate来封装一些MAP或实体类比较方便，虽然resultSet利用反射也可以封装为实体类，但对实体类的成员属性类型要求比较严格，所以考虑之下就用的比较百家了，这些仅仅是自己的天马行空，没有什么技术性参考，仅供参考

 public void runO2oMerchantHotGoodsStatisticsToEtoneo2o(NamedParameterJdbcTemplate namedJdbcTemplate, Date date, ReportTradingKu reportTradingKu) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String year = DateUtils.getDateYear(date);
        String month = DateUtils.getDateMonth(date);
        String date2 = format.format(date);
        String merKuName = reportTradingKu.getMerKuName();
        //创建sql语句
        StringBuilder sql = new StringBuilder("SELECT\n" +
                "\tsbm.mer_no AS mer_no,\n" +
                "\tsbm.mer_name AS mer_name,\n" +
                "\tsbm.mer_address AS mer_address,\n" +
                "\tsbm.contact_name AS contact_person,\n" +
                "\tsbm.contact_phone AS contact_mobile,\n" +
                "\tsboi.`name` as goods_name,\n" +
                "\tSUM(IFNULL(sboi.amount,0)) as goods_amount,\n" +
                "\tsum(IFNULL(sboi.nums,0)) as goods_total\n" +
                "FROM\n" +
                "\tsdb_b2c_orders AS sbo,\n" +
                "\tsdb_b2c_order_items AS sboi,\n" +
                "\tsdb_basic_merchant AS sbm \n" +
                "WHERE\n" +
                "\tsbo.order_id = sboi.order_id \n" +
                "\tAND sbo.merchant_bn = sbm.mer_no \n" +
                "\tAND FROM_UNIXTIME( sbo.createtime, '%Y-%m-%d' ) = '"+date2+"' \n" +
                "\tAND sbo.source_name in('etoneo2o','','etoneO2o') \n" +
                "GROUP BY\n" +
                "\tsboi.`name`,\n" +
                "\tsboi.merchant_bn");
        //查询数据
        List<O2oMerchantHotGoodsStatistics> list = namedJdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(O2oMerchantHotGoodsStatistics.class));
        Connection connection = null;
        PreparedStatement pstm = null;
        try {
            //获取url
            StringBuffer sbf = getJdbcUrl(reportTradingKu);
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(sbf.toString(),reportTradingKu.getUserName(),reportTradingKu.getUserPassword());
            connection.setAutoCommit(false);
            String inserSql = "insert into o2o_merchant_hot_goods_statistics(mer_no,mer_name,goods_name,goods_amount,goods_total,mer_address,contact_person,contact_mobile,source_name,year,month,date)values(?,?,?,?,?,?,?,?,?,?,?,?)";
            pstm = connection.prepareStatement(inserSql);
            if(list!=null&&list.size()>0){
                for (O2oMerchantHotGoodsStatistics oms : list) {
                    pstm.setString(1,oms.getMer_no());
                    pstm.setString(2,oms.getMer_name());
                    pstm.setString(3,oms.getGoods_name());
                    pstm.setBigDecimal(4,oms.getGoods_amount());
                    pstm.setInt(5,oms.getGoods_total());
                    pstm.setString(6,oms.getMer_address());
                    pstm.setString(7,oms.getContact_person());
                    pstm.setString(8,oms.getContact_mobile());
                    pstm.setString(9,merKuName);
                    pstm.setString(10,year);
                    pstm.setString(11,month);
                    pstm.setString(12,date2);
                    pstm.addBatch();
                }
                pstm.executeBatch();
                connection.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeConnect(connection);
            closePstm(pstm);
        }

    }

同上

public void importData() {
        logger.info("导入数据开始>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        //开始时间
        long startTime = System.currentTimeMillis();
        //查询所有的连接商户库信息
        List<MerchantConnectData> connectDataList = o2oImportOrderPaymentsService.queryConnectMerchantKu();
        //如果数据不为空
        if (connectDataList != null && connectDataList.size() > 0) {
            //遍历所有的商户链接信息取出对应的数据
            for (int i = 0; i < connectDataList.size(); i++) {
                //获取信息
                String yklBn = connectDataList.get(i).getYklBn();  //商户号   订单号+商户号 组成新的订单号防止重复
                String mHost = connectDataList.get(i).getmHost();  //主数据库地址
                String mUserName = connectDataList.get(i).getmUserName();  //主数据库用户名
                String mUserPassword = connectDataList.get(i).getmUserPassword();  //主数据库密码
                String mDbName = connectDataList.get(i).getmDbName();  //主数据库的名字 和商户对应 一个商户一个库
                String sourceName = o2oImportOrderPaymentsService.querySourceName(yklBn); //商圈名
                logger.info("开始连接数据库【" + mDbName + "】>>>>>>>>>>");
                //获取NamedParameterJdbcTemplate，用于查询
                NamedParameterJdbcTemplate namedParameterJdbcTemplate = getNamedParameterJdbcTemplate(mHost, mUserName, mUserPassword, mDbName);
                //查询语句
                String SdbEctoolsPaymentsSql = "select sep.payment_id as paymentId,sep.money as money,sep.pay_app_id as payAppId,sep.t_confirm as tConfirm,seob.rel_id as relId from sdb_ectools_payments as sep,sdb_ectools_order_bills as seob where seob.bill_id = sep.payment_id";
                //查询要插入的数据
                List<ProtogeneticPaymentData> pbList = namedParameterJdbcTemplate.query(SdbEctoolsPaymentsSql, new BeanPropertyRowMapper<>(ProtogeneticPaymentData.class));
                List<PaymentsBatch> list = new ArrayList<>();
                //遍历数据封装
                for (ProtogeneticPaymentData pd : pbList) {
                    PaymentsBatch paymentsBatch = new PaymentsBatch();
                    paymentsBatch.setPaymentId(pd.getPaymentId());//支付单号
                    paymentsBatch.setMoney(pd.getMoney()); //支付金额
                    paymentsBatch.setPayAppId(pd.getPayAppId()); //支付方式
                    paymentsBatch.setTconfirm(pd.getTconfirm()); //确实时间
                    paymentsBatch.setOrderId(yklBn + pd.getRelId()); //订单号
                    paymentsBatch.setSourceName(sourceName); //商圈
                    paymentsBatch.setMerchantBn(yklBn); //商户号
                    list.add(paymentsBatch);
                }
                //批量+事务操作
                //查询需要跑批的连接信息
                List<ReportTradingKu> tradingKus = o2oImportOrderPaymentsService.queryTradingKus();
                if (tradingKus != null && tradingKus.size() > 0) {
                    for (ReportTradingKu kus : tradingKus) {
                        if (kus.getMerKuName().equals("etoneo2o")) {
                            Connection connect = null;
                            PreparedStatement pstm = null;
                            try {
                                String jdbcUrl = getJdbcUrl(kus);
                                MyJdbcUtil.setJdbcParam("com.mysql.cj.jdbc.Driver", jdbcUrl, kus.getUserName(), kus.getUserPassword());
                                connect = MyJdbcUtil.connect();
                                connect.setAutoCommit(false);
                                String sql = "insert into sdb_ectools_payments (payment_id,money,pay_app_id,t_confirm,order_id,source_name,merchant_bn) values(?,?,?,?,?,?,?)";
                                pstm = connect.prepareStatement(sql);
                                //遍历数据
                                if (list != null && list.size() > 0) {
                                    for (PaymentsBatch pb : list) {
                                        pstm.setString(1, pb.getPaymentId());
                                        pstm.setString(2, pb.getMoney());
                                        pstm.setString(3, pb.getPayAppId());
                                        pstm.setString(4, pb.getTconfirm());
                                        pstm.setString(5, pb.getOrderId());
                                        pstm.setString(6, pb.getSourceName());
                                        pstm.setString(7, pb.getMerchantBn());
                                        pstm.addBatch();
                                    }
                                    int[] ints = pstm.executeBatch();
                                    connect.commit();
                                    logger.info("总计插入数据:【" + ints.length + "】条数据");
                                }
                            } catch (Exception e) {
                                e.printStackTrace();
                            } finally {
                                if (connect != null) {
                                    try {
                                        connect.close();
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }
                                if (pstm != null) {
                                    try {
                                        pstm.close();
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }
                            }
                        }
                    }
                }

            }
        } else {
            logger.info("没有对应的数据可以处理!");
        }


    }

    public String getJdbcUrl(ReportTradingKu kus) {
        //创建StringBuffer  最终结果是：jdbc:mysql://127.0.0.1:3306/etoneo2o
        StringBuffer sbf = new StringBuffer();
        sbf.append("jdbc:mysql://");
        sbf.append(kus.getMerKuAddress());
        sbf.append(":");
        sbf.append(kus.getMerKuPort()); //端口都是3306
        sbf.append("/");
        sbf.append(kus.getMerKuName());
        sbf.append("?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8");
        String jdbcUrl = sbf.toString();
        return jdbcUrl;
    }

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String mHost, String mUserName, String mUserPassword, String mDbName) {
        //创建StringBuffer
        StringBuffer sbf = new StringBuffer();
        sbf.append("jdbc:mysql://");
        sbf.append(mHost);
        sbf.append(":");
        sbf.append("3306");
        sbf.append("/");
        sbf.append(mDbName);
        sbf.append("?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8");
        String jdbcUrl = sbf.toString();
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(mUserName);
        dataSource.setPassword(mUserPassword);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        return namedParameterJdbcTemplate;
    }

测试结果，几十万的数据，1小时搞定，比原先的速度快的不是一星半点，原先可是一天啊，批量+事务赢得本次比赛我们恭喜亚索喜提风男称号

[ DEBUG] [2019-05-17 14:21:51] org.springframework.jdbc.core.JdbcTemplate [682] - Executing prepared SQL query
[ DEBUG] [2019-05-17 14:21:51] org.springframework.jdbc.core.JdbcTemplate [616] - Executing prepared SQL statement [SELECT
	om.mer_id AS mer_no,
	om.mer_name AS mer_name,
	om.mer_addr AS mer_address,
	om.contact_person,
	om.contact_mobile,
	sboi.`name` AS goods_name,
	SUM(IFNULL(sboi.amount,0)) as goods_amount,
	sum(IFNULL(sboi.nums,0)) as goods_total
FROM
	sdb_b2c_orders AS sbo,
	sdb_b2c_order_items AS sboi,
	o2o_merchant AS om 
WHERE
	sboi.order_id = sbo.order_id 
	AND FROM_UNIXTIME( sbo.createtime, '%Y-%m-%d' ) = '2019-05-15' 
	AND om.mer_id = sbo.merchant_bn 
GROUP BY
	sboi.merchant_bn,
	sboi.`name`]
[ INFO ] [2019-05-17 14:21:51] [301] - -------------跑批结束用时:【8153】毫秒------------
[ INFO ] [2019-05-17 14:21:51] TestController [262] - -------总计用时:【4071522】毫秒------

 

对于百万级数据的处理，一直没有得到相对来说比较好的方案，希望有看到的朋友分享一些。