用weblogic数据库连接池递归调用实现树形目录出错

04-04-19 zyc21st
public class menu_tree implements Serializable{

DBConn myDBConn = new DBConn();

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

public ResultSet executeQuery(String sql){

try {

conn = myDBConn.getConnection();

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

rs = stmt.executeQuery(sql);

}

catch (SQLException e) {

System.err.println(e.toString());

}

return rs;

}

}

public void buildTree(javax.servlet.jsp.JspWriter out,int parentid,int level)

throws Exception{

menu_tree conn=new menu_tree();

level++;

ResultSet rs = conn.executeQuery(

"select * from MENU_TREE where PARENTID=" + parentid + "order by ID");

while (rs.next()) {

out.println("<div>");

for (int i = 0; i < level - 1; i++)

out.print("<img src=\"images/white.gif\"> ");

id1=rs.getInt("ID");

boolean resu=has_child(id1);

out.println(resu);

if (resu) {

out.print("<img alt=\"展开\" style=\"cursor:hand;\" onclick=\"myClick('"+rs.getInt("ID") + "');\" ID=\"img" + rs.getInt("ID") + "\" src=\"images/plus.gif\"> <img ID=\"im" + rs.getInt("ID")+"\" src=\"images/closedfold.gif\"> ");

out.print("<span onclick=\"myClick1('" + rs.getInt("id") +

"');\" style=\"cursor:default;\" ID=\"span" +

rs.getInt("ID") + "\">" + rs.getString("MENU") + "</span>");

out.println("<div style=\"display:none;\" ID=\"div" + rs.getInt("ID") +

"\">");

// out.println(rs.getInt("ID") ) ;

buildTree(out,id1,level); //递归调用

out.println("</div>");

}

else

out.print("<img src=\"images/minus.gif\"> <img src=\"images/openfold.gif\"> <span onclick=\"myClick1('" +

rs.getInt("id") +

"');\" style=\"cursor:default;\" ID=\"span" +

rs.getInt("ID") + "\">" + rs.getString("MENU") + "</span>");

out.println("</div>");

}

rs.close();

rs=null;

}

private boolean has_child(int parentid) throws Exception {

ResultSet rs=conn.executeQuery("select * from MENU_TREE where PARENTID="+parentid+"order by ID");

return rs.next();

}

其中DBConn是用weblogic数据库连接池连接oracle数据库,但是该段代码在递归调用处出错,但是如果不用连接池,DBConn直接连接oracle数据库,则没有出错,能正常显示树形目录,是不是用连接池需要清理内存,该如何解决?

swordfly
2004-04-20 10:35
建议不要递归访问数据库。这样做的效率会比较低,而且会对数据库造成较大的负担,尤其是用户并发访问的时候。所以,在访问数据库是,尽量通过一条sql语句来实现,而不要使用递归,循环等。

对于Oracle数据库,树形结构可以使用如下语句获取:

select menu,level from menu_tree start with parentid=1 connect by prior id=parentid;

其中level是oracle数据库生成的伪列,存储的是每个节点的深度。parentid=1,可根据需要改变它的值。通过该语句你得到的结果集已经是树形化的数据集了。你可以使用该结果集和level通过循环来显示树,而不再需要用递归

猜你喜欢