java查询几万条数据_java一次性查询几十万，几百万数据解决办法(转载)

0hB)H"aK ZJ%K Z0java查询一次性查询几十万，几百万数据解决办法51Testing软件测试网0L?pAzOk51Testing软件测试网2@ai.g\1p

很早的时候写工具用的一个办法。51Testing软件测试网HK9h~aA5i

51Testing软件测试网PGA[HMx51Testing软件测试网sC)Bu;|2B5rM

当时是用来把百万数据打包 成rar文件。xc xW!~

N0

uH1Z?%`!e6rWg%M0所以用了个笨办法。 希望高手指导一下，有什么好方法没有啊

zTo^J*j051Testing软件测试网'aX/FZKe51Testing软件测试网 iKH~)hI#Z5X1n

1、先批量查出所有数据，例子中是一万条一批。

p)K,HhIKg02、在查出数据之后把每次的数据按一定规则存入本地文件。51Testing软件测试网$Oa"["pB&t

3、获取数据时，通过批次读取，获得大批量数据。此方法参见：http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/51Testing软件测试网{Z!g.Dr4@

S-Kr

-^

s? Wg;EsqY0以下是查询数据库。按批次查询51Testing软件测试网*Qr)v%z#MH'e)f8ev

public static void getMonthDataList() {

$Z B1wE!o T H0ResultSet rs = null;51Testing软件测试网,Iu"ZD b

Statement stat = null;

"Pd!G K?8N0Connection conn = null;

%jZ:d}W9]hAX/F0List list = new ArrayList();

vI@y%a%N`p7oX0try {

ku2ED:o:]$R%kX;w0conn = createConnection();51Testing软件测试网u'z.o@@kP

if(conn!=null){51Testing软件测试网2ox`*A3W

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");51Testing软件测试网i]~)_Z pE v0j

SimpleDateFormat timesdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");51Testing软件测试网@P`W/[-c

String nowDate = sdf.format(new Date());

R1S#z`#oP0Config.lasttimetext = timesdf.format(new Date());

3ePJ2J7v,s`0String lastDate = sdf.format(CreateData.addDaysForDate(new Date(), 30));51Testing软件测试网 ~i)M5P2al2d_

stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);51Testing软件测试网 c`1Au}]EN

int lastrow = 0;

;he(S0_tR\0int datanum = 0;51Testing软件测试网

|,u0DCce

String countsql = "SELECT count(a.id) FROM trip_special_flight a" +

(_cK4B+rRT&D0" where a.dpt_date >= to_date('"+nowDate+"','yyyy-mm-dd') " +51Testing软件测试网1WHsr2_:V^

"and a.dpt_date <= to_date('"+lastDate+"','yyyy-mm-dd') and rownum>"+lastrow+" order by a.get_time desc";

^'sX}D^b&sVnF0rs = stat.executeQuery(countsql);

'[.M

F2c(bO{U0while (rs.next()) {51Testing软件测试网Y*s |d

{H

datanum = rs.getInt(1);51Testing软件测试网8Q*[?yc4A;Y

}

E0G$oO,d1]U}0int nerun = 10000;51Testing软件测试网JZ_m2Qg*t,x*U WI

int runnum = datanum%onerun==0?(datanum/onerun):(datanum/onerun)+1;51Testing软件测试网Oob?(@

for(int r =0;r

System.out.println("getMonthDataList--"+datanum+" 开始查询第"+(r+1)+"批数据");51Testing软件测试网h1iB$L1A}8{,D@9P

String sql = "SELECT * FROM (SELECT rownum rn, a.dpt_code, a.arr_code,a.dpt_date,a.airways,a.flight," +

s

MB/V {3s)A+{3p9}`O0"a.cabin,a.price FROM trip_special_flight a" +

,M7_&]%[G!s(@0" where a.dpt_date >= to_date('"+nowDate+"','yyyy-mm-dd') " +51Testing软件测试网M

|3M%pe6m+H

"and a.dpt_date <= to_date('"+lastDate+"','yyyy-mm-dd') order by rownum asc) WHERE rn > "+lastrow;51Testing软件测试网*Xvr0W v"c.Z

stat.setMaxRows(onerun);51Testing软件测试网@1E7\0o#sKQcj,z

stat.setFetchSize(1000);51Testing软件测试网Y.l!n;E9hI

rs = stat.executeQuery(sql);

5L)[)F3uK'U}7zX0String text = "";51Testing软件测试网!y

B)_%r6l

int i = 1;51Testing软件测试网:h1sx:e3U2C)rW#}

while (rs.next()) {51Testing软件测试网:m)VVi$qx

text += rs.getString(2)+"|"+rs.getString(3)+"|"+rs.getDate(4)+"|"+rs.getString(5)+"|"+rs.getString(6)+"|"+rs.getString(7)+"|"+rs.getString(8)+"||";51Testing软件测试网8otL+Z$Y]'~\/FT

if(i%1000==0){

1uk`D-R*nRq-j0M0FileUtil.appendToFile(Config.tempdatafile, text);51Testing软件测试网MJX,x

_

text = "";

%XXXjSfl0}51Testing软件测试网%e0eh4L[{8ht

i++;

J/eF2BQF;E\(B)Xd0}51Testing软件测试网Qf_8wn[6X

c2k

if(text.length()>10){

FWHw+^q8XA0FileUtil.appendToFile(Config.tempdatafile, text);

Ff(M m#Ub&`3bW0}

ltFN/En%_\Lg0lastrow+=onerun;

\C5BYE0}

dj9z$j5W;o j%tI1?0}

N ^V0Z4z0} catch (Exception e) {

s TAh"E[+]g {0e.printStackTrace();

h_u(`3p+R`0} finally {

eI7y{-Jdt0closeAll(rs, stat, conn);51Testing软件测试网+ZV-\.^E\m8u

}51Testing软件测试网d:a'oTJu

51Testing软件测试网^#U q+dB5_

}zyiuc[ l051Testing软件测试网Y*b`*{,y%W-~L

-----java一次性查询几十万，几百万数据解决办法C4i.j#VRS,L0

2pv%wj6e9mh,R0

O8F.F?

Kr.x051Testing软件测试网5EZ*[

\uE51Testing软件测试网-kv+JTW,|AT

?&R0Gf"m&W0D{\)tE"w/^.Q }KA0

i#L8_#j9I{*D,VCI0存入临时文件之后，再用读取大量数据文件方法。%TlEX7G6Z&dL0

M.nX\CT\T051Testing软件测试网w`.m_SxFJdo51Testing软件测试网*Lqe4YQjn

设置缓存大小BUFFER_SIZE ，Config.tempdatafile是文件地址51Testing软件测试网)V0zJ`0wF9p[

51Testing软件测试网 I'_z*` hNi)I

j9N ^;N

aT7}uTS[S0来源博客http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/51Testing软件测试网|M%?;bj c)al51Testing软件测试网z_7_ll

bt1@ a6Q

51Testing软件测试网7l"eY2G"c%X

51Testing软件测试网p%Sg6lspackage com.yjf.util;

-G\adSX

\7`F%Vl0import java.io.File;51Testing软件测试网Z2MT2j']u`?

import java.io.RandomAccessFile;51Testing软件测试网3dy9Gn9wm,kSSey

import java.nio.MappedByteBuffer;51Testing软件测试网

Y&V7ysLH

import java.nio.channels.FileChannel;

#Xx'[:Dw&pd6wBZ6ev051Testing软件测试网Ii!Xw1lKdMWq

public class Test {

X `1Wt*N$qbr!zB!\0public static void main(String[] args) throws Exception {51Testing软件测试网+SrL2v6x t

final int BUFFER_SIZE = 0x300000; // 缓冲区为3M

/Z3|.a0f;G!v&?9O*R0File f = new File(Config.tempdatafile);

5{!O"i}3Uk;sMB0// 来源博客http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/

:b"_.t&y&^0int len = 0;

*@@i|F!w1I#G0Long start = System.currentTimeMillis();51Testing软件测试网aHhJ&}'X

for (int z = 8; z >0; z--) {

B4\*\k/W0MappedByteBuffer inputBuffer = new RandomAccessFile(f, "r")51Testing软件测试网"o/GkL@2iXzz`_

.getChannel().map(FileChannel.MapMode.READ_ONLY,

&Bv-k{cR9ZU-B0f.length() * (z-1) / 8, f.length() * 1 / 8);

$l.}r:Z"E m0byte[] dst = new byte[BUFFER_SIZE];// 每次读出3M的内容

T6Ad;V^Jo$bX0for (int ffset = 0; offset < inputBuffer.capacity(); offset += BUFFER_SIZE) {

`*^s8G'|fBH-s0if (inputBuffer.capacity() - offset >= BUFFER_SIZE) {51Testing软件测试网bX2t]1ED&Lmj

for (int i = 0; i < BUFFER_SIZE; i++)51Testing软件测试网x)\G{'H3?6J#fh

dst[i] = inputBuffer.get(offset + i);

O7bu

|Z`z(R0} else {

%vqV

|cB4Cn0for (int i = 0; i < inputBuffer.capacity() - offset; i++)

YJX1U}&I^|0dst[i] = inputBuffer.get(offset + i);

~H,|?6Zm$\LF0}51Testing软件测试网%Ao ur5EE6A

int length = (inputBuffer.capacity() % BUFFER_SIZE == 0) ? BUFFER_SIZE51Testing软件测试网f7CaVPvE{&M0T

: inputBuffer.capacity() % BUFFER_SIZE;

O$g"S-@K)C1f0len += new String(dst, 0, length).length();51Testing软件测试网Z)_

u?F[2bPG

System.out.println(new String(dst, 0, length).length()+"-"+(z-1)+"-"+(8-z+1));

+E4[K+|:I*Ri0}

cT*J|z0}51Testing软件测试网o-RC4D7K`P5LZ+K

System.out.println(len);

u^2eC)WOU^0long end = System.currentTimeMillis();51Testing软件测试网}E*}WY Nyf

System.out.println("读取文件文件花费：" + (end - start) + "毫秒");

QF4J3f1MO{ O-c0}51Testing软件测试网^*D4Wh7p`

51Testing软件测试网}[P{ z

}Ep9u)B3`0读取大量数据文件方法。51Testing软件测试网^/m[~b"x