百万数据秒级处理——第二讲

此讲以支付宝订单数据为背景，建立百万级别的数据，包括批量插入、Excel2007导出和txt快速导出，还有多线程txt导出100W数据。

1、批量插入

private static void add100W() {
        int num = 1000000;
        long startTime = System.currentTimeMillis();   
        Date myDate = new Date();
        String date = new SimpleDateFormat("yyyyMMdd").format(myDate);  
        String seconds = new SimpleDateFormat("HHmmss").format(myDate);  
        LinkedHashSet<String> codeSet=new LinkedHashSet<String>();
        while(codeSet.size()<num){
            String code = date+"20004001"+getTwo()+"00"+
                    getTwo()+"00"+seconds+getTwo();
            codeSet.add(code);
        }
        long endTime = System.currentTimeMillis();    
        System.out.println("生成100W时间：" + ((endTime - startTime)/1000+1) + "秒");    

        long startTime2 = System.currentTimeMillis();    
        batchInsert(num,codeSet);
        long endTime2 = System.currentTimeMillis();   
        System.out.println("插入100W时间：" + ((endTime2 - startTime2)/1000+1) + "秒");
    }

    private static void batchInsert(int num, HashSet<String> codeSet){
        Connection con = null;
        PreparedStatement pstmt=null;
        String sql = null;
        try {
            con = _dbUtil.getConnection();
            String tableName = "order_test";
            sql="INSERT INTO " +tableName + "(id,orderNo,batch,name) VALUES(null,?,?,?)";
            con.setAutoCommit(false);
            int i = 0;
            pstmt = (PreparedStatement) con.prepareStatement(sql);          
            for (String code:codeSet) {
                setPreparedStatement(con,pstmt,code,i);
            }
            try {
                // 批量执行 
                pstmt.executeBatch();
                con.commit();
                // 清空批处理
                pstmt.clearBatch();
            } catch (Exception e) {
                System.out.println(e);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            codeSet.clear();
            _dbUtil.closeResources(con, pstmt,null);
        }
    }

    private static void setPreparedStatement(Connection con, PreparedStatement pstmt, String code, int i) {
        try {
            pstmt.setString(1, code);
            pstmt.setString(2, "1701");
            pstmt.setString(3, "dfgdg梵蒂冈");
            pstmt.addBatch();                       
            i++;
            if (i % 5000 == 0) {
                try {
                    pstmt.executeBatch();
                    con.commit();
                    pstmt.clearBatch();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * list去重
     * @param list
     * @return
     */
    private static ArrayList<Order> distinctList(ArrayList<Order> list){  
        ArrayList<Order> newList = new ArrayList<Order>();  
        Set<String> set = new HashSet<String>();
        for (Order item:list){  
            if (set.add(item.getOrderNo())){  
                newList.add(item);  
            }
        }  
        return newList;  
    }  

    /** 
     * 产生随机的2位数 
     * @return 
     */  
    public static String getTwo(){  
        Random rad=new Random();  
        String result  = rad.nextInt(100) +"";  
        if(result.length()==1){  
            result = "0" + result;  
        }  
        return result;  
    }  

2、excel2007导出

/**
     * 查询导出Excel100W时间：10秒
        导出Excel100W时间：28秒
        24M
     * @throws Exception
     */
    private static void exportExcel100W() throws Exception {
        long startTime1 = System.currentTimeMillis();   
        List<Order> crCodeList2 = getOrderList();
        LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
        long endTime1 = System.currentTimeMillis();    
        System.out.println("查询导出Excel100W时间：" + ((endTime1 - startTime1)/1000+1) + "秒");    

        long startTime2 = System.currentTimeMillis();  
        titleMap.put("CODE2", "批次号");
        titleMap.put("CODE5", "条码数据");
        titleMap.put("CODE3", "二维码随机码");
        titleMap.put("CODE4", "二维码地址");
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < crCodeList2.size(); i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("CODE2", (i+1));
            map.put("CODE5", crCodeList2.get(i).getOrderNo());
            map.put("CODE3", crCodeList2.get(i).getBatch());
            map.put("CODE4", crCodeList2.get(i).getName());
            list.add(map);
        }
//      OutputStream os = response.getOutputStream();// 取得输出流
//      response.reset();// 清空输出流
//      response.setHeader("Content-disposition", "attachment; filename="+batch+"批次" + ".xls");// 设定输出文件头
//      response.setContentType("application/msexcel");// 定义输出类型
        FileOutputStream out = new FileOutputStream("D:/1703.xlsx");
        ExportExcelUtil.newInstance().exportCommonExcel(null, titleMap, list, out);
        long endTime2 = System.currentTimeMillis();    
        System.out.println("导出Excel100W时间：" + ((endTime2 - startTime2)/1000+1) + "秒");    
    }

3、txt导出

private static void exportTxt100W() throws Exception {
//      response.setContentType("text/plain");   
//      String fileName = URLEncoder.encode(batch+"批次", "UTF-8");    
//      response.setHeader("Content-Disposition","attachment; filename=" + fileName + ".txt");    
        BufferedOutputStream buff = null;       
        StringBuffer write = new StringBuffer();       
        String enter = "\r\n";       
//      ServletOutputStream outSTr = null;       
        long startTime2 = System.currentTimeMillis();   
        try {       
//          outSTr = response.getOutputStream();  // 建立       
//          buff = new BufferedOutputStream(outSTr); 
            buff = new BufferedOutputStream(new FileOutputStream("D:/13.txt")); 
            StringBuffer result22 = new StringBuffer();
            List<Order> crCodeList3 = getOrderList();

            for(int i = 0;i<crCodeList3.size();i++){
                result22.append(crCodeList3.get(i).getOrderNo()+" "+
                        crCodeList3.get(i).getBatch()+" "
                        + crCodeList3.get(i).getName() + enter);
            }
            write.append(result22.toString());       
            buff.write(write.toString().getBytes("UTF-8"));       
            buff.flush();       
            buff.close();       
        } catch (Exception e) {       
            e.printStackTrace();       
        } finally {       
            try {       
                buff.close();       
//              outSTr.close();       
            } catch (Exception e) {       
                e.printStackTrace();       
            }       
        }  
        long endTime2 = System.currentTimeMillis();    
        System.out.println("导出TXT100W时间：" + ((endTime2 - startTime2)/1000+1) + "秒");    
    }

4、多线程跑

package thread;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.yanhui.util._dbUtil;

import pojo.Order;

public class TicketWindowRunnable implements Runnable {
//  private int i;
    private final static int MAX = 1000000;

    @Override
    public void run() {
        BufferedOutputStream buff = null;       
        StringBuffer write = new StringBuffer();       
        String enter = "\r\n";       

        try {       
            buff = new BufferedOutputStream(new FileOutputStream("D:/12.txt")); 
            StringBuffer result22 = new StringBuffer();
            List<Order> crCodeList3 = getOrderList();

            for(int i = 0;i<MAX;i++){
                result22.append(crCodeList3.get(i).getOrderNo()+" "+
                        crCodeList3.get(i).getBatch()+" "
                        + crCodeList3.get(i).getName() + enter);
//              System.out.println(Thread.currentThread()
//                      +" 的号码是："+(i+1));
            }
            write.append(result22.toString());       
            buff.write(write.toString().getBytes("UTF-8"));       
            buff.flush();       
            buff.close();       
        } catch (Exception e) {       
            e.printStackTrace();       
        } finally {       
            try {       
                buff.close();       
            } catch (Exception e) {       
                e.printStackTrace();       
            }       
        }  
    }

    private static List<Order> getOrderList() throws Exception {
        List<Order> orderList = new ArrayList<Order>();
        Connection con = _dbUtil.getConnection();
        String sql = "select orderNo,batch,name from order_test";
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery(); // 返回结果集ResultSet
        while (rs.next()) {
            String orderNo = rs.getString("orderNo");
            String batch = rs.getString("batch");
            String name = rs.getString("name");

            Order order =new Order(orderNo,batch,name);
            orderList.add(order);
        }
        return orderList;
    }


}

主方法

final TicketWindowRunnable ticket = new TicketWindowRunnable();
        Thread windows1 = new Thread(ticket,"一号窗口");
        Thread windows2 = new Thread(ticket,"二号窗口");
        Thread windows3 = new Thread(ticket,"三号窗口");
        Thread windows4 = new Thread(ticket,"四号窗口");
        Thread windows5 = new Thread(ticket,"五号窗口");
        windows1.start();
        windows2.start();
        windows3.start();
        windows4.start();
        windows5.start();

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

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