java查詢一次性查詢幾十萬(wàn),，幾百萬(wàn)數(shù)據(jù)解決辦法
很早的時(shí)候?qū)懝ぞ哂玫囊粋€(gè)辦法,。
當(dāng)時(shí)是用來(lái)把百萬(wàn)數(shù)據(jù)打包 成rar文件,。
所以用了個(gè)笨辦法。 希望高手指導(dǎo)一下,，有什么好方法沒(méi)有啊
1,、先批量查出所有數(shù)據(jù)，例子中是一萬(wàn)條一批,。
2,、在查出數(shù)據(jù)之后把每次的數(shù)據(jù)按一定規(guī)則存入本地文件。
3,、獲取數(shù)據(jù)時(shí)，通過(guò)批次讀取,，獲得大批量數(shù)據(jù),。
此方法參見(jiàn)：
http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/
以下是查詢數(shù)據(jù)庫(kù)。按批次查詢
- 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+" 開(kāi)始查詢第"+(r+1)+"批數(shù)據(jù)");
- 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一次性查詢幾十萬(wàn),，幾百萬(wàn)數(shù)據(jù)解決辦法
存入臨時(shí)文件之后,，再用讀取大量數(shù)據(jù)文件方法。
設(shè)置緩存大小BUFFER_SIZE ,，Config.tempdatafile是文件地址
來(lái)源博客
http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/
- 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; // 緩沖區(qū)為3M
- File f = new File(Config.tempdatafile);
- // 來(lái)源博客http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/
- 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的內(nèi)容
- 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("讀取文件文件花費(fèi)：" + (end - start) + "毫秒");
- }
-
- }
讀取大量數(shù)據(jù)文件方法,。
原文：http://blog.csdn.net/yjflinchong/article/details/7287648
|
