java一次性查詢幾十萬,幾百萬數據解決辦法 – JAVA編程語言程序開發技術文章

java查詢一次性查詢幾十萬,幾百萬數據解決辦法
很早的時候寫工具用的一個辦法。
當時是用來把百萬數據打包 成rar文件。
所以用瞭個笨辦法。 希望高手指導一下,有什麼好方法沒有啊

1、先批量查出所有數據,例子中是一萬條一批。
2、在查出數據之後把每次的數據按一定規則存入本地文件。
3、獲取數據時,通過批次讀取,獲得大批量數據。此方法參見:/kf/201203/121508.html

以下是查詢數據庫。按批次查詢
public static void  getMonthDataList() {
        ResultSet rs = null;
        Statement stat = null;
        Connection conn = null;
        List<DataBean> list = new ArrayList<DataBean>();
        try {
            conn = createConnection();
            if(conn!=null){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                SimpleDateFormat timesdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String nowDate = sdf.format(new Date());
                Config.lasttimetext = timesdf.format(new Date());
                String lastDate = sdf.format(CreateData.addDaysForDate(new Date(), 30));
                stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                int lastrow = 0;
                int datanum = 0;
                String countsql = "SELECT count(a.id) FROM trip_special_flight a" +
                " where a.dpt_date >= to_date('"+nowDate+"','yyyy-mm-dd') " +
                "and a.dpt_date <= to_date('"+lastDate+"','yyyy-mm-dd') and rownum>"+lastrow+" order by a.get_time  desc";
                rs = stat.executeQuery(countsql);
                while (rs.next()) {
                    datanum = rs.getInt(1);
                }
                int onerun = 10000;
                int runnum = datanum%onerun==0?(datanum/onerun):(datanum/onerun)+1;
                for(int r =0;r<runnum;r++){
                    System.out.println("getMonthDataList–"+datanum+" 開始查詢第"+(r+1)+"批數據");
                    String sql = "SELECT * FROM  (SELECT rownum rn, a.dpt_code, a.arr_code,a.dpt_date,a.airways,a.flight," +
                    "a.cabin,a.price FROM trip_special_flight a" +
                    " where a.dpt_date >= to_date('"+nowDate+"','yyyy-mm-dd') " +
                    "and a.dpt_date <= to_date('"+lastDate+"','yyyy-mm-dd')  order by rownum  asc) WHERE rn > "+lastrow;
                    stat.setMaxRows(onerun);
                    stat.setFetchSize(1000);
                    rs = stat.executeQuery(sql);
                    String text = "";
                    int i = 1;
                    while (rs.next()) {
                        text += rs.getString(2)+"|"+rs.getString(3)+"|"+rs.getDate(4)+"|"+rs.getString(5)+"|"+rs.getString(6)+"|"+rs.getString(7)+"|"+rs.getString(8)+"||";
                        if(i%1000==0){
                            FileUtil.appendToFile(Config.tempdatafile, text);
                            text = "";
                        }
                        i++;
                    }
                    if(text.length()>10){
                        FileUtil.appendToFile(Config.tempdatafile, text);
                    }
                    lastrow+=onerun;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, stat, conn);
        }
    }
—–java一次性查詢幾十萬,幾百萬數據解決辦法

存入臨時文件之後,再用讀取大量數據文件方法。
設置緩存大小BUFFER_SIZE ,Config.tempdatafile是文件地址
來源博客/kf/201203/121508.html

package com.yjf.util;
import java.io.File;
import java.io.RandomAccessFile;
import java.nio.MappedByteBuffer;
import java.nio.channels.FileChannel;

public class Test {
    public static void main(String[] args) throws Exception {
        final int BUFFER_SIZE = 0x300000; // 緩沖區為3M
        File f = new File(Config.tempdatafile);
 //  來源博客/kf/201203/121508.html
        int len = 0;
        Long start = System.currentTimeMillis();
        for (int z = 8; z >0; z–) {
            MappedByteBuffer inputBuffer = new RandomAccessFile(f, "r")
                    .getChannel().map(FileChannel.MapMode.READ_ONLY,
                            f.length() * (z-1) / 8, f.length() * 1 / 8);
            byte[] dst = new byte[BUFFER_SIZE];// 每次讀出3M的內容
            for (int offset = 0; offset < inputBuffer.capacity(); offset += BUFFER_SIZE) {
                if (inputBuffer.capacity() – offset >= BUFFER_SIZE) {
                    for (int i = 0; i < BUFFER_SIZE; i++)
                        dst[i] = inputBuffer.get(offset + i);
                } else {
                    for (int i = 0; i < inputBuffer.capacity() – offset; i++)
                        dst[i] = inputBuffer.get(offset + i);
                }
                int length = (inputBuffer.capacity() % BUFFER_SIZE == 0) ? BUFFER_SIZE
                        : inputBuffer.capacity() % BUFFER_SIZE;
                len += new String(dst, 0, length).length();
                System.out.println(new String(dst, 0, length).length()+"-"+(z-1)+"-"+(8-z+1));
            }
        }
        System.out.println(len);
        long end = System.currentTimeMillis();
        System.out.println("讀取文件文件花費:" + (end – start) + "毫秒");
    }
}
讀取大量數據文件方法。

摘自 玩轉java

發佈留言