公司里面开发报表程序,有一种情况是先从一个表中取出数据,然后再循环在两一个表中取得结果集.比如,
sql = "select ano from brank";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
sql = "select yuer,actno,txday from ecl where ano = "+rs.getString(1);
ps2 = con.prepareStatement(sql);
rs2 = ps2.executeQuery();
while(rs2.next()){
....
}
rs2.close();
ps2.close();
}另一种写法是这样一来:
ArrayList rlist = new ArrayList();
String tmpano = null;
sql = "select ano,aname from brank";
ps = con.prepareStatement(sql);
rs = rs.executeQuery();
while(rs.next()){
rlist.add(rs.getString(1));
}
rs.close();
ps.close();
sql = "select yuer,actno,txday from ecl where ano = ";
for(Iterator it = rlist.iterator();it.hasNext();){
tmpano = (String)it.next();
ps = con.prepareStatement(sql);
ps.setString(1, tmpano);
rs = ps.executeQuery();
while(rs.next()){
....
}
ps.close();
rs.close();
}请问大家哪种写法执行效率比较好啊?
sql = "select ano from brank";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
sql = "select yuer,actno,txday from ecl where ano = "+rs.getString(1);
ps2 = con.prepareStatement(sql);
rs2 = ps2.executeQuery();
while(rs2.next()){
....
}
rs2.close();
ps2.close();
}另一种写法是这样一来:
ArrayList rlist = new ArrayList();
String tmpano = null;
sql = "select ano,aname from brank";
ps = con.prepareStatement(sql);
rs = rs.executeQuery();
while(rs.next()){
rlist.add(rs.getString(1));
}
rs.close();
ps.close();
sql = "select yuer,actno,txday from ecl where ano = ";
for(Iterator it = rlist.iterator();it.hasNext();){
tmpano = (String)it.next();
ps = con.prepareStatement(sql);
ps.setString(1, tmpano);
rs = ps.executeQuery();
while(rs.next()){
....
}
ps.close();
rs.close();
}请问大家哪种写法执行效率比较好啊?
应该把这两个sql合并为一个sql执行
你这样写,效率太低下