百万数据秒级处理

于 2017-03-26 22:15:05 发布
阅读量2.5k 收藏 2
点赞数
分类专栏： java之路 文章标签： string 数据 批处理 batch 导出百万数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/sinat_15153911/article/details/66529040
版权

1、main方法

//批处理3种方法
        long startTime=System.currentTimeMillis();   //获取开始时间  

        String bable = "01";
        //创建表
        //createTable(bable);
        //插入百万数据
        //insertCode4(bable);

        //导出Excel
        try {
            export2007(bable);
        } catch (Exception e) {
            e.printStackTrace();
        }

        long endTime=System.currentTimeMillis(); //获取结束时间  
        System.out.println("程序运行时间： "+((endTime-startTime)/1000 +1)+"秒");

创建表

private static void createTable(String bable) {

        Connection conn = _dbUtil.getConnection();

        Statement stmt = null;
        try{
            stmt = conn.createStatement();

            String sql = "CREATE TABLE R_"+bable+"_TABLE " +
                    "(AA1       VARCHAR(255) primary key not null," + 
                    " AA2      VARCHAR(255)," + 
                    " AA3      VARCHAR(255)," + 
                    " AA4      VARCHAR(3000),"
                    + "AA5      VARCHAR(255))";
            stmt.executeUpdate(sql);
        }catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            _dbUtil.closeResources2(conn, stmt);
        }   
    }

2、批处理三种方法
insertCode1方法一

private static void insertCode1(String bable) {
        int countLong = 1000000;
        DecimalFormat df = new DecimalFormat("00000000"); 
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;
        try {
            con = _dbUtil.getConnection();
            String tableName = "R_"+bable+"_TABLE";
            sql="INSERT INTO " +tableName + "(AA1,AA2,AA3,AA4) VALUES(?,?,?,?)";
            con.setAutoCommit(false);
            String code1_ = "";
            String code2_ = "";
            String code3_ = "";
            String code4_ = "";
            pstmt = (PreparedStatement) con.prepareStatement(sql);          

            for (int i = 0; i< countLong; i++ ) {
                String radom = df.format(i+1); //8位
                code1_ = bable + radom;
                code2_ = bable + "";
                code3_ = code2_;
                code4_ = code2_;
                setPreparedStatement(con,pstmt,code1_,code2_,code3_,code4_,i);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            _dbUtil.closeResources(con, pstmt,null);
        }
    }

    private static void setPreparedStatement(Connection con, PreparedStatement pstmt, String code1_, String code2_,
            String code3_, String code4_, int i) {
        try {
            pstmt.setString(1, code1_);
            pstmt.setString(2, code2_);
            pstmt.setString(3, code3_);
            pstmt.setString(4, code4_);
            pstmt.addBatch();                       
            i++;
            if (i % 1000 == 0) {
                try {
                    pstmt.executeBatch();
                    con.commit();
                    pstmt.clearBatch();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

insertCode2 方法二

private static void insertCode2(String bable) {
        int countLong = 1000000;
        DecimalFormat df = new DecimalFormat("00000000"); 
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;

        try {
            con = _dbUtil.getConnection();
            con.setAutoCommit(false);
            String tableName = "R_"+bable+"_TABLE";
            sql="INSERT INTO " +tableName + "(AA1,AA2,AA3,AA4) VALUES(?,?,?,?)";
            pstmt = con.prepareStatement(sql);
            int num1 = (int) (countLong / COUNT);  //第一层循环数                      2
            int num2 = (int) (countLong % COUNT);  //判断第一层循环数是否等于1000      3
            int lastNum = COUNT; //二层循环数
            int num5 = COUNT;
            if(num2 != 0){
                num1 =num1 + 1;
                lastNum = num2;
            }

            for(int i = 0;i < num1 ; i++){
                if(i == (num1 - 1) ){
                    num5 = lastNum;
                }
                for(int j = 0;j < num5 ; j++) {
                    setPstmt((j+i*COUNT), bable+"",pstmt);
                }
                pstmt.executeBatch();
            }
            con.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            _dbUtil.closeResources(con, pstmt,null);
        }
    }
//设置批处理
    private static void setPstmt(int index,String bable,PreparedStatement pstmt) throws SQLException {
        DecimalFormat df = new DecimalFormat("00000000");
        String radom = df.format(index+1); 
        String code1_ = bable+radom;
        String code2_ = bable+"";
        String code3_ = code2_;
        String code4_ = code2_;
        pstmt.setString(1, code1_);
        pstmt.setString(2, code2_);
        pstmt.setString(3, code3_);
        pstmt.setString(4, code4_);
        pstmt.addBatch();
    }

insertCode3 方法三 这是逐条逐条插进 不是批处理

private static void insertCode3(String bable) {
        int countLong = 1000000;
        DecimalFormat df = new DecimalFormat("00000000");
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;

        try {
            String tableName = "R_"+bable+"_TABLE";
            sql="INSERT INTO " +tableName + "(AA1,AA2,AA3,AA4) VALUES(?,?,?,?)";
            con = _dbUtil.getConnection();
            pstmt = (PreparedStatement) con.prepareStatement(sql);
            for(int i = 0; i <countLong ; i++){
                String radom = df.format(i+1); 
                System.out.println(bable);
                String code1_ = bable + radom;
                pstmt.setString(1, code1_);
                pstmt.setString(2, bable);
                pstmt.setString(3, bable);
                pstmt.setString(4, bable);
                pstmt.executeUpdate();
            }
            pstmt.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            _dbUtil.closeResources(con, pstmt,null);
        }
    }

insertCode4 方法四 sql语句拼接插

private static void insertCode4(String bable) {
        int countLong = 1000000;
        DecimalFormat df = new DecimalFormat("00000000"); 
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;

        try {
            String tableName = "R_"+bable+"_TABLE";
            sql="INSERT INTO " +tableName + "(AA1,AA2,AA3,AA4) VALUES";
            con = _dbUtil.getConnection();

            for(int i = 0; i <countLong ; i++){
                String radom = df.format(i+1); 
                System.out.println(radom);
                String code1_ = bable + radom;
                if(i == (countLong-1)){
                    sql += "("+code1_+","+code1_+","+code1_+","+code1_+")";
                }else{
                    sql += "("+code1_+","+code1_+","+code1_+","+code1_+"),";
                }
            }
            pstmt = (PreparedStatement) con.prepareStatement(sql);
            pstmt.executeUpdate();
            pstmt.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            _dbUtil.closeResources(con, pstmt,null);
        }
    }

导出百万数据9秒

private static void export2007(String bable) throws Exception {
        LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
//      Paginable<aa> paginable = aaMapper.listaa(aa+"", "aaa", 1, aa);
//      List<aa> aa = paginable.getList();
        titleMap.put("aa1", "1");
        titleMap.put("aa2", "2");
        titleMap.put("aa3", "3");
        titleMap.put("aa4", "4");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 1000000; i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("aa1", i);
            map.put("aa2", i+1);
            map.put("aa3", i+2);
            map.put("aa4", i+3);
            list.add(map);
        }
        /*OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流
        response.setHeader("Content-disposition", "attachment; filename="+aa" + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        ExportExcelUtil.newInstance().exportCommonExcel(null, titleMap, list, os);*/
        FileOutputStream out = new FileOutputStream("D:/aa.xlsx");
        ExportExcelUtil.newInstance().exportCommonExcel(null, titleMap, list, out);
    }

利用线程

private static Integer pages=1; 

    private static boolean exeFlag=true; 

    public static void main(String[] args) {
        long startTime=System.currentTimeMillis();    
        ExecutorService executorService=Executors.newFixedThreadPool(10); 

        while(exeFlag){
            if(pages<=100){
                executorService.execute(new Runnable() {

                    @Override
                    public void run() {
                        System.out.println("第"+pages+"网页...");
                        insertCode3("1709",pages);
                        pages++;
                    }
                });
            }else{
                if(((ThreadPoolExecutor)executorService).getActiveCount()==0){ 
                    executorService.shutdown(); 
                    exeFlag=false;
                    System.out.println("任务已经完成");
                }
            }

            try {
                Thread.sleep(500); 
            } catch (InterruptedException e) {
                e.printStackTrace();
            } 
        }
        long endTime=System.currentTimeMillis(); 
        System.out.println("程序运行时间： "+((endTime-startTime)/1000 +1)+"秒"); 

    }

    private static void insertCode3(String batch,int pages) {
        DecimalFormat df = new DecimalFormat("00000000"); 
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;

        try {
            String tableName = "CR_"+batch+"_CODE";
            sql="INSERT INTO " +tableName + "(code1,code2,code3,code4) VALUES(?,?,?,?)";
            con = _dbUtil.getConnection();
            pstmt = (PreparedStatement) con.prepareStatement(sql);
            String batch1 = "1706" ;
            pstmt.setString(1, pages+"");
            pstmt.setString(2, batch1);
            pstmt.setString(3, batch1);
            pstmt.setString(4, batch1);
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            _dbUtil.closeResources(con, pstmt,null);
        }
    }

下载地址：链接：http://pan.baidu.com/s/1i5wVRnf 密码：br99

全优快云的丰（好）色（se）博客，这里有书本看不到的Java技术，电影院禁播的电影，欢迎关注QQ群494808400

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值