最近使用jdbc方式查询数据，保存为csv文件中。当然你可以在pl/sql中直接查出来，copy to excel就好了。但我想通过程序实现
1 @Test
2 public void test() throws IOException {
3 BufferedReader reader=new BufferedReader(new InputStreamReader(new FileInputStream("C:\Users\yhzh\Desktop\zh_20160913")));
4 String tmp=null;
5 List<String> nos=new ArrayList<String>();
6 while((tmp=reader.readLine()) !=null)
7 nos.add(tmp);
8
9 Connection con = null;// 创建一个数据库连接
10 PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
11 PreparedStatement pre2 = null;
12 ResultSet result = null;// 创建一个结果集对象
13 BufferedWriter csvWriter=null;
14 try
15 {
16 String tag=(new SimpleDateFormat("hhmmss")).format(new Date());
17 csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File("C:\Users\yhzh\Desktop\贷后还款计划_"+tag+".csv")), "utf-8"));
18
19 Class.forName("oracle.jdbc.driver.OracleDriver");
20 //:6006/hotfix
21 String url = "jdbc:oracle:thin:@//*.*.*.*:16030/zcgl",user = "*",password = "*";
22 con = DriverManager.getConnection(url, user, password);// 获取连接
23 String sql="select max(lr_id) from t_loan_request where LR_REQUESTSTATUS ='2' and lr_applyid=?";
24 /*String sql = 27 "select req.LR_APPLYID,CURR_PERIODS,REPAY_DAY,
" +
28 "(NEEDREPAY_PRINCIPAL+NEEDREPAY_INTEREST+NEEDREPAY_ADMIFEE+nvl(RISK_AMOUNT,0)+nvl(SERVICEFEE,0)+nvl(PARKINGFEE,0))NEEDREPAY_TOTAL,
" +
29 "PERIOD_REPAY_AMOUNT
" +
30 "from(
" +
31 " select lr_id,LR_APPLYID from t_loan_request t
" +
32 " where t.LR_REQUESTSTATUS ='2' and t.creater='PostLoanOuterAction'
" +
33 " order by lr_id desc)req
" +
34 "left join t_repay_plan rp
" +
35 "on req.lr_id=rp.lr_id
" +
36 "order by req.lr_id,CURR_PERIODS ";// 预编译语句，“？”代表参数*/
37 pre = con.prepareStatement(sql);
38 pre2=con.prepareStatement("select CURR_PERIODS,REPAY_DAY,
" +
39 "(NEEDREPAY_PRINCIPAL+NEEDREPAY_INTEREST+NEEDREPAY_ADMIFEE+nvl(RISK_AMOUNT,0)+nvl(SERVICEFEE,0)+nvl(PARKINGFEE,0))NEEDREPAY_TOTAL,
" +
40 "PERIOD_REPAY_AMOUNT
" +
41 "from t_repay_plan
" +
42 "where lr_id=? " +
43 "order by CURR_PERIODS");
44 for(String no:nos){
45 pre.setString(1,no);
46 result = pre.executeQuery();
47 if(result.next()) {
48 long lrId=result.getLong(1);
49 pre2.setLong(1,lrId);
50 result = pre2.executeQuery();
51 while (result.next()) {
52 csvWriter.write(no);
53 csvWriter.write(",");
54 csvWriter.write(result.getString(1));
55 csvWriter.write(",");
56 csvWriter.write(result.getString(2));
57 csvWriter.write(",");
58 csvWriter.write(result.getString(3));
59 csvWriter.write(",");
60 csvWriter.write(result.getString(4));
61 csvWriter.newLine();
62 }
63 }
64 }
65
66 csvWriter.flush();
67 }
68 catch (Exception e)
69 {
70 e.printStackTrace();
71 }
72 finally
73 {
74 try
75 {
76 if(csvWriter !=null)
77 csvWriter.close();
78 if (result != null)
79 result.close();
80 if (pre != null)
81 pre.close();
82 if (con != null)
83 con.close();
84 System.out.println("数据库连接已关闭！");
85 }
86 catch (Exception e)
87 {
88 e.printStackTrace();
89 }
90 }
91 }
先读取所有编号形成List，后遍历这个List，先查出id再查详细数据。这样的数据csv文件中大约8万多条
一条条的来肯定慢，如果不按照编号，直接一次查出，数据是9万多条。速度都很慢！！！
后来想提高下，至少要有个明显的提升呀。写文件这块基本排除了，剩下的疑问就是ResultSet是否拿到了所有结果呢？
根据网上查到的资料和实际调试，得出答案：ResultSet默认一次取10条数据，怪不得要慢，如果一次全部读入内存再写入文件就一定很快了。
怎样一次读取所有数据呢？
sql改为读取全部
1 String sql="select req.LR_APPLYID,CURR_PERIODS,REPAY_DAY,
" +
2 "(NEEDREPAY_PRINCIPAL+NEEDREPAY_INTEREST+NEEDREPAY_ADMIFEE+nvl(RISK_AMOUNT,0)+nvl(SERVICEFEE,0)+nvl(PARKINGFEE,0))NEEDREPAY_TOTAL,
" +
3 "PERIOD_REPAY_AMOUNT
" +
4 "from(
" +
5 " select lr_id,LR_APPLYID from t_loan_request t
" +
6 " where t.LR_REQUESTSTATUS ='2' and t.creater='PostLoanOuterAction'
" +
7 " order by lr_id desc)req
" +
8 "left join t_repay_plan rp
" +
9 "on req.lr_id=rp.lr_id
" +
10 "order by req.lr_id,CURR_PERIODS";
设置PreparedStatement：
1 pre = con.prepareStatement(sql);
2 pre.setFetchSize(100000);
3 result = pre.executeQuery();
4 //result.setFetchSize(100000);
主要是PreparedStatement的 setFetchSize 方法，
后来发现ResultSet也有个setFetchSize 方法，也是可行的，只是这个时候resultset中已经有了10条记录直到循环10次后，再次使用result.next()才去取100000，fetchSize才起作用
这样设置后速度飞快！！！