Java與JDBC問題

bingley

Java與JDBC問題
從數據庫Server中抓出資料生成XML文件.數據量小的時候程序沒有問題,如果把所有資料抓出來(大約有50000筆.)時,程序就會運行很慢.而且數據庫Server的效率也會下降一半左右.幾乎快down機了.不知道哪里出了問題.SQL語法應該問題不大,可能是connection的什麼地方有問題吧.代碼如下.哪位高人帮帮忙了,从昨天一直搞到现在了不起.晕死了......

try{
rafwrite = new RandomAccessFile(g_writepath,"rw";

FileOutputStream fos = null;
Writer fp = null;
try {
fos = new FileOutputStream("c:\\qssbig5_jz.xml";
fp = new OutputStreamWriter(fos, "BIG5";
}
catch (IOException ioex) {
ioex.printStackTrace();
}

//就診資料--就診單據編號,工號,就診日期
l_sql = "select ade01,ade02,ade04 from ade_file,adf_file " +
" where ade01 = adf01" +
" and adeconf = 'Y'" +
//" and adedate between '20050320'and '20050408'" + (加上日期,抓資料就沒問題,去掉就是把所有資料全部抓出來)
" group by 1,2,3" +
" order by 1,2,3";

// Write XML File
try {
fp.write("<?xml version=\"1.0\" encoding=\"gb2312\" standalone=\"yes\"?>;\r\n";
fp.write("<NewDataSet>;\r\n";
}
catch (IOException ex3) {
}

int rowCount = 0;
int degCount = 0;
try {
Statement st = dmBase.myDM.conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(l_sql);

rs.last();
rowCount = rs.getRow();
rs.first();
msgDlg.setMaxDeg(rowCount);
msgDlg.setMinDeg(0);
while (rs.next()) {
String temp_str1 = "";
String temp_str2 = "";
degCount++;
msgDlg.setDegValue(degCount);
String ADR = rs.getString(1);
PreparedStatement stk;
PreparedStatement stt;

//根據單據編號抓出疾病代碼
stk = dmBase.myDM.conn.prepareStatement("select add04 from adf_file,add_file" +
" where adf02 = add01" +" and addacti = 'Y' and adf01 = ?";

//根據單據編號抓出用藥名稱&&數量
stt = dmBase.myDM.conn.prepareStatement(" select ima02,adh04" +
" from adh_file,ima_file "+" where ima01 = adh03 and adh01 = ?";
try{
stk.setString(1,ADR);
ResultSet rsk = stk.executeQuery();
while(rsk.next()){
if(rsk.getString(1) == null){
temp_str2 = "N"+";";
}else{
temp_str2 = rsk.getString(1)+";"+temp_str2;
}
}
}catch(Exception ex){
ex.printStackTrace();
}

try{
stt.setString(1,ADR);
ResultSet rss = stt.executeQuery();
while(rss.next()){
temp_str1 = rss.getString(1)+"-"+rss.getString(2)+";"+temp_str1;
}
}catch(Exception ex){
ex.printStackTrace();
}
//.trim().substring(0,temp_str2.length()-1) .trim().substring(0,temp_str1.length()-1)
try {
fp.write(" <QSS>;\r\n";
fp.write(" <EMPLID>;"+rs.getString(2).trim()+"</EMPLID>;\r\n";
fp.write(" <ZJDATE>;"+dfkk.format(rs.getDate(3))+"</ZJDATE>;\r\n";
fp.write(" <ZJILL>;" + temp_str2 +"</ZJILL>;\r\n");
fp.write(" <ZJMED>;"+temp_str1+"</ZJMED>;\r\n");
fp.write(" </QSS>;\r\n");
}
catch (IOException ex4) {
ex4.printStackTrace();
rafwrite.seek(rafwrite.length());
rafwrite.writeBytes(current+"\t"+"Error:Write record error!"+"\n");
rafwrite.close();
System.exit(0);
}
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
try {
fp.write("</NewDataSet>;\r\n");
fp.close();
}
catch (IOException ex5) {
}

andijo

指出一点：
读写文件时，应该使用缓冲操作类，能够大幅的提高程序的执行效率。
不要使用OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream("");
应该使用BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));
BufferedWriter bw = new BufferedOutputStream(out);
这样来操作文件。

jinijxta

返回大于1000行的数据网络流量就很大了,也不一定有人认真去看.返回50000行其实可以在服务器端用存储过程生成XML文档,然后下载.否则服务器与客户端维护数据流的开销还是很大的,可以在服务器端使用SQLJ,应用java.nio包来提高写到服务器的XML文件效率.然后进行下载.

perryhg

rs.last();
rowCount = rs.getRow();
rs.first();

你干嘛要用scrollable的cursor，用scrollable的cursor占用内存非常大的。就为了计算有多少条记录而用scrollable的resultset是非常不值得的。