java一次查詢(xún)幾十萬(wàn),幾百萬(wàn)數(shù)據(jù)解決辦法.docx

java一次性查詢(xún)幾十萬(wàn)，幾百萬(wàn)數(shù)據(jù)解決辦法 分類(lèi)： java數(shù)據(jù)庫(kù)2012-02-27 09:0816609人閱讀評(píng)論(27)收藏舉報(bào)java查詢(xún)一次性查詢(xún)幾十萬(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)：/blog/static/175273432201191354043148/以下是查詢(xún)數(shù)據(jù)庫(kù)。按批次查詢(xún)public static void getMonthDataList() ResultSet rs = null;Statement stat = null;Connection conn = null;List list = new ArrayList();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 +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= to_date(+nowDate+,yyyy-mm-dd) +and a.dpt_date +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一次性查詢(xún)幾十萬(wàn)，幾百萬(wàn)數(shù)據(jù)解決辦法存入臨時(shí)文件之后，再用讀取大量數(shù)據(jù)文件方法。設(shè)置緩存大小BUFFER_SIZE ，Config.tempdatafile是文件地址來(lái)源博客/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ū)為3MFile f = new File(Config.tempdatafile);/ 來(lái)源博客/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 byteBUFFER_SIZE;/ 每次讀出3M的內(nèi)容for (int offset = 0; offset = BUFFER_SIZE) for (int i = 0; i BUFFER_SIZE; i+)dsti = inputBuffer.get(offset + i); else for (int i = 0; i inputBuffer.capacity() - offset; i+)dsti = 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, len