我用oracle9i数据库+tomcat5.0.28,自己实现的树状菜单:
关键代码如下:
tree.jsp<%!boolean has_child(String parentid, Cn conn) throws Exception {
//Cn conn = new Cn(request);
ResultSet rs = conn.executeQuery("select * from v_ress_menutree where pid='"
+ parentid + "'");
boolean rt = rs.next();
rs.close();
conn.close();
return rt;
} void buildTree(javax.servlet.jsp.JspWriter out, String parentid, int level,
String pids, Cn conn)
throws Exception { level++;
//Cn conn = new Cn(request); String sql = "";
ResultSet rs = null;
String pic = ""; sql = "select * from v_ress_menutree where pid='" + parentid + "'";
rs = conn.executeQuery(sql);
while (rs.next()) {
out.println("<div>");
for (int i = 0; i < level - 1; i++)
out.print("<img src=\"../images/T.gif\"> "); if ((level == 1) || (has_child(rs.getString("module_id"), conn))) {
pic = "minus.gif";
} else { pic = "wj3.gif";
}
out.print("<img alt=\"展开\" style=\"cursor:hand;\" onclick=\"turnit('"
+ rs.getString("module_id")
+ "','"
+ rs.getString("module_fname")
+ "');\" id=\"img"
+ rs.getString("module_id")
+ "\" src=\"../images/" + pic + "\">");
out.print("<span onclick=\"turnit('"
+ rs.getString("module_id") + "','"
+ rs.getString("module_fname")
+ "');\" style=\"cursor:hand;\" id=\"span"
+ rs.getString("module_id") + "\">"
+ rs.getString("module_name") + "</span>");
out.println("<div style=\"display:;\" id=\"div"
+ rs.getString("module_id") + "\" pa='" + pids + ","
+ rs.getString("module_id") + "'>");
buildTree(out, rs.getString("module_id"), level, pids + ","
+ rs.getString("module_id"), conn);//递归调用
out.println("</div>"); out.println("</div>");
}
rs.close();
conn.close();
}%>
Cn cn = new Cn(request);
buildTree(out, "00", 0, "0", cn);
cn.close();
连接数据库bean代码:
Cn.java
package com.teg.ress;import java.sql.*;
import java.util.*;import java.io.FileInputStream;
import javax.servlet.http.HttpServletRequest;
public class Cn {
String jdbcDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; //jdbc驱动 String connectionString = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=ress"; //数据库连接字符串 String user = "sa"; //数据库用户名 String pass = ""; //数据库密码 public Connection conn = null; ResultSet rs = null;
public Statement stmt = null;
public void LoadConfig(HttpServletRequest req) {
Properties prop = new Properties();
FileInputStream fis;
try {
fis = new FileInputStream(req.getSession().getServletContext().getRealPath("\\")+"WEB-INF\\classes\\config.properties");
prop.load(fis);
jdbcDriver = prop.getProperty("jdbcDriver",jdbcDriver);
connectionString = prop.getProperty("connectionString",connectionString);
user = prop.getProperty("user",user);
pass = prop.getProperty("pass",pass);
} catch (Exception e) {
e.printStackTrace();
}
}
public Cn(HttpServletRequest req) throws Exception {
LoadConfig(req);
try {
Class.forName(jdbcDriver);
} catch (ClassNotFoundException e) {
System.err.println(e.toString());
}
}
public ResultSet executeQuery(String sql) throws Exception {
rs = null;
try {
conn = DriverManager.getConnection(connectionString, user, pass);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.err.println(e.toString());
}
return rs;
} public void executeUpdate(String sql) {
try {
conn = DriverManager.getConnection(connectionString, user, pass);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
System.err.println(e.toString());
}
}
public void close()
{
try
{
if(conn != null)
{
conn.close();
conn = null;
}
}
catch(Exception e)
{
System.out.println(e);
}
try
{
if(rs != null)
rs.close();
}
catch(Exception e)
{
System.out.println(e);
}
try
{
if(stmt != null)
stmt.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
每次执行了这个页面就可以看到oracle后台的session不断增加没有释放,但是有时访问了别的页面后,会话session链接数又会降下来,怎么回事啊?
我的代码中都close了啊,不知哪里还有泄露,希望高手指点!
关键代码如下:
tree.jsp<%!boolean has_child(String parentid, Cn conn) throws Exception {
//Cn conn = new Cn(request);
ResultSet rs = conn.executeQuery("select * from v_ress_menutree where pid='"
+ parentid + "'");
boolean rt = rs.next();
rs.close();
conn.close();
return rt;
} void buildTree(javax.servlet.jsp.JspWriter out, String parentid, int level,
String pids, Cn conn)
throws Exception { level++;
//Cn conn = new Cn(request); String sql = "";
ResultSet rs = null;
String pic = ""; sql = "select * from v_ress_menutree where pid='" + parentid + "'";
rs = conn.executeQuery(sql);
while (rs.next()) {
out.println("<div>");
for (int i = 0; i < level - 1; i++)
out.print("<img src=\"../images/T.gif\"> "); if ((level == 1) || (has_child(rs.getString("module_id"), conn))) {
pic = "minus.gif";
} else { pic = "wj3.gif";
}
out.print("<img alt=\"展开\" style=\"cursor:hand;\" onclick=\"turnit('"
+ rs.getString("module_id")
+ "','"
+ rs.getString("module_fname")
+ "');\" id=\"img"
+ rs.getString("module_id")
+ "\" src=\"../images/" + pic + "\">");
out.print("<span onclick=\"turnit('"
+ rs.getString("module_id") + "','"
+ rs.getString("module_fname")
+ "');\" style=\"cursor:hand;\" id=\"span"
+ rs.getString("module_id") + "\">"
+ rs.getString("module_name") + "</span>");
out.println("<div style=\"display:;\" id=\"div"
+ rs.getString("module_id") + "\" pa='" + pids + ","
+ rs.getString("module_id") + "'>");
buildTree(out, rs.getString("module_id"), level, pids + ","
+ rs.getString("module_id"), conn);//递归调用
out.println("</div>"); out.println("</div>");
}
rs.close();
conn.close();
}%>
Cn cn = new Cn(request);
buildTree(out, "00", 0, "0", cn);
cn.close();
连接数据库bean代码:
Cn.java
package com.teg.ress;import java.sql.*;
import java.util.*;import java.io.FileInputStream;
import javax.servlet.http.HttpServletRequest;
public class Cn {
String jdbcDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; //jdbc驱动 String connectionString = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=ress"; //数据库连接字符串 String user = "sa"; //数据库用户名 String pass = ""; //数据库密码 public Connection conn = null; ResultSet rs = null;
public Statement stmt = null;
public void LoadConfig(HttpServletRequest req) {
Properties prop = new Properties();
FileInputStream fis;
try {
fis = new FileInputStream(req.getSession().getServletContext().getRealPath("\\")+"WEB-INF\\classes\\config.properties");
prop.load(fis);
jdbcDriver = prop.getProperty("jdbcDriver",jdbcDriver);
connectionString = prop.getProperty("connectionString",connectionString);
user = prop.getProperty("user",user);
pass = prop.getProperty("pass",pass);
} catch (Exception e) {
e.printStackTrace();
}
}
public Cn(HttpServletRequest req) throws Exception {
LoadConfig(req);
try {
Class.forName(jdbcDriver);
} catch (ClassNotFoundException e) {
System.err.println(e.toString());
}
}
public ResultSet executeQuery(String sql) throws Exception {
rs = null;
try {
conn = DriverManager.getConnection(connectionString, user, pass);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.err.println(e.toString());
}
return rs;
} public void executeUpdate(String sql) {
try {
conn = DriverManager.getConnection(connectionString, user, pass);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
System.err.println(e.toString());
}
}
public void close()
{
try
{
if(conn != null)
{
conn.close();
conn = null;
}
}
catch(Exception e)
{
System.out.println(e);
}
try
{
if(rs != null)
rs.close();
}
catch(Exception e)
{
System.out.println(e);
}
try
{
if(stmt != null)
stmt.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
每次执行了这个页面就可以看到oracle后台的session不断增加没有释放,但是有时访问了别的页面后,会话session链接数又会降下来,怎么回事啊?
我的代码中都close了啊,不知哪里还有泄露,希望高手指点!
然后每次构造treeview菜单时直接在内存里面取就可以了,而不是每次去与数据库交互
我的connection是在 executeQuery 和 executeUpdate 方法里创建的:conn = DriverManager.getConnection(connectionString, user, pass);
没调用一次这两个方法就建立一个连接,所以会话会过多,解决方式是把创建放到构造函数里了:
public Cn(HttpServletRequest req) throws Exception {
LoadConfig(req);
try {
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(connectionString, user, pass);
} catch (ClassNotFoundException e) {
System.err.println(e.toString());
}
搞定!!!呵呵