【IT168 技术】java一次性查询几十万，几百万数据解决办法。很早的时候写工具用的一个办法。
当时是用来把百万数据打包 成rar文件。所以用了个笨办法。 希望高手指导一下，有什么好方法没有啊
1、先批量查出所有数据，例子中是一万条一批。
2、在查出数据之后把每次的数据按一定规则存入本地文件。
3、获取数据时，通过批次读取，获得大批量数据。此方法参见：http://yijianfengvip.blog.163.com/blog/static/175273432201191354043148/
以下是查询数据库。按批次查询
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 <= 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
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);
}
}
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 <= 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
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);
}
}
