//DbManager.java
package book.treeview;import java.sql.*;public class DbManager {
//类成员Connection
protected static Connection conn;
//mysql的驱动类,定义为常量
public static final String CLASS_NAME = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
//数据库的连接地址,定义为常量
public static final String CONNET_STR = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books";
//获得Connetion
public static Connection getConnection() {
try {
Class.forName(CLASS_NAME);//使用类反射加载该驱动类
//获得一个Connection
conn = DriverManager.getConnection(CONNET_STR, "niu", "niu");
return conn;//返回该Connection
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
//传入查询数据库的sql语句,返回ResultSet
public static ResultSet getResultSet(String sql) {
boolean bSuccess = true;
Statement stmt = null;//声明Statement stmt
ResultSet rs = null;//声明ResultSet rs
Connection con = getConnection();//调用getConnetion()方法获得一个Connetion
if (con == null)//如果Connection 为null则返回假
bSuccess = false;
if (bSuccess) {
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//通过Connection创建一个Statemet
rs = stmt.executeQuery(sql);//执行查询语句,
} catch (SQLException e) {
e.printStackTrace();
bSuccess = false;
}
}
if (bSuccess)//如果执行成功,则返回rs
return rs;
else
return null;
}
//传入执行数据更新的语句,返回更新结果,真为成功执行
public static boolean excute(String sql) {
boolean bSuccess = true;
Statement stmt = null;//声明Statement stmt
Connection con = getConnection();//调用getConnetion()方法获得一个Connetion
if (con == null)//如果Connection 为null则返回假
bSuccess = false;
if (bSuccess) {
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//通过Connection创建一个Statemet
bSuccess = stmt.execute(sql);//执行更新数据操作
} catch (SQLException e) {
e.printStackTrace();
bSuccess = false;
}
}
return bSuccess;
}
//释放Connection
public static void releaseConnection() {
try {
if (conn != null)// 如果Connetion 不为null则关闭Connection
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}}
//treeviewRender.javapackage book.treeview;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;public class treeviewRender {
static public String renderStatic(TreeviewElement treeElmt, boolean bHidden) {
StringBuffer content = new StringBuffer();
content.append("<li id=" + treeElmt.getID() + ">");//显示li元素
if (bHidden && treeElmt.canExpand())//如果可以展开并且初始化为隐藏,则显示展开图片,并添加showHide事件
content.append("<img src=images/plus.gif onClick=\"showHide('"
+ treeElmt.getID() + "')\">");
else if (treeElmt.canExpand())//如果可以展开,则显示折叠图片,并添加showHide事件
content.append("<img src=images/minus.gif onClick=\"showHide('"
+ treeElmt.getID() + "')\">");
else//否则显示空白图片
content.append("<img src=images/blank.gif>");
content.append("<img src=images/folder.gif><a href=\"#\"");//添加文件夹图片
if (treeElmt.canExpand())//如果可以展开,为节点添加showHide事件
content.append("onClick=\"showHide('" + treeElmt.getID() + "')\"");
content.append(">" + treeElmt.getNodeName() + "</a>");
if (treeElmt.canExpand()) {//如果可以展开,则用UL标记显示子结点
content.append("<ul");
if (bHidden)
content.append(" style=\"display:none;\" ");
content.append(">");
TreeviewElement[] elmts = treeElmt.getChildren();//获得此结点的子节点
for (int i = 0; i < elmts.length; i++)
content.append(renderStatic(elmts[i], bHidden));//递归调用显示子节点
content.append("</ul>");
}
content.append("</li>");//结束显示<li>元素
return content.toString();
} static public TreeviewElement[] getTopCategory() { String sql = "select category_id from product_category where parent_category_id=0";
ResultSet rs = DbManager.getResultSet(sql);// 执行sql语句并返回ResultSet
try {
rs.last();// 移动到最后一行
int row = rs.getRow();// 得到总记录数
if (row <= 0) {
return null;
} else {
Vector vData = new Vector();
rs.beforeFirst();
while (rs.next())
vData.add("" + rs.getInt("CATEGORY_ID"));
TreeviewElement[] children = new TreeviewElement[vData.size()];
for (int i = 0; i < vData.size(); i++) {
int id = Integer.parseInt((String) vData.get(i));
children[i] = new Category(id);
}
return children;
}
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {// 最后关闭ResutltSet,Statement.并释放连接
if (rs != null)
rs.close();
if (rs.getStatement() != null)
rs.getStatement().close();
DbManager.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
} }
static public String renderSimple(TreeviewElement treeElmt) {
StringBuffer content = new StringBuffer();
content.append("<li id=" + treeElmt.getID() + "><a href=\"#\">");
content.append( treeElmt.getNodeName() + "</a>");
if (treeElmt.canExpand()) {
content.append("<ul>");
TreeviewElement[] elmts = treeElmt.getChildren();
for (int i = 0; i < elmts.length; i++)
content.append(renderSimple(elmts[i]));
content.append("</ul>");
}
content.append("</li>");
return content.toString();
}
static public String renderTreeViewAjax(TreeviewElement treeElmt,boolean bRenderChildren) {
StringBuffer content = new StringBuffer();
content.append("<li id="+treeElmt.getID()+">");
if (treeElmt.canExpand())
content.append("<img src=images/plus.gif onClick=\"getSubTree('"+treeElmt.getID()+"')\">");
else
content.append("<img src=images/blank.gif>");
content.append("<img src=images/folder.gif><a href=\"#\"");
if (treeElmt.canExpand())
content.append(" onClick=\"getSubTree('"+treeElmt.getID()+"')\"");
content.append(">"+treeElmt.getNodeName()+"</a>");
if ( bRenderChildren&&treeElmt.canExpand( ))
{
content.append("<ul>");
TreeviewElement[] elmts = treeElmt.getChildren();
for (int i=0;i<elmts.length;i++)
content.append(renderTreeViewAjax(elmts[i],false));
content.append("</ul>");
}
content.append("</li>");
return content.toString();
}
}
package book.treeview;import java.sql.*;public class DbManager {
//类成员Connection
protected static Connection conn;
//mysql的驱动类,定义为常量
public static final String CLASS_NAME = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
//数据库的连接地址,定义为常量
public static final String CONNET_STR = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books";
//获得Connetion
public static Connection getConnection() {
try {
Class.forName(CLASS_NAME);//使用类反射加载该驱动类
//获得一个Connection
conn = DriverManager.getConnection(CONNET_STR, "niu", "niu");
return conn;//返回该Connection
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
//传入查询数据库的sql语句,返回ResultSet
public static ResultSet getResultSet(String sql) {
boolean bSuccess = true;
Statement stmt = null;//声明Statement stmt
ResultSet rs = null;//声明ResultSet rs
Connection con = getConnection();//调用getConnetion()方法获得一个Connetion
if (con == null)//如果Connection 为null则返回假
bSuccess = false;
if (bSuccess) {
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//通过Connection创建一个Statemet
rs = stmt.executeQuery(sql);//执行查询语句,
} catch (SQLException e) {
e.printStackTrace();
bSuccess = false;
}
}
if (bSuccess)//如果执行成功,则返回rs
return rs;
else
return null;
}
//传入执行数据更新的语句,返回更新结果,真为成功执行
public static boolean excute(String sql) {
boolean bSuccess = true;
Statement stmt = null;//声明Statement stmt
Connection con = getConnection();//调用getConnetion()方法获得一个Connetion
if (con == null)//如果Connection 为null则返回假
bSuccess = false;
if (bSuccess) {
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//通过Connection创建一个Statemet
bSuccess = stmt.execute(sql);//执行更新数据操作
} catch (SQLException e) {
e.printStackTrace();
bSuccess = false;
}
}
return bSuccess;
}
//释放Connection
public static void releaseConnection() {
try {
if (conn != null)// 如果Connetion 不为null则关闭Connection
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}}
//treeviewRender.javapackage book.treeview;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;public class treeviewRender {
static public String renderStatic(TreeviewElement treeElmt, boolean bHidden) {
StringBuffer content = new StringBuffer();
content.append("<li id=" + treeElmt.getID() + ">");//显示li元素
if (bHidden && treeElmt.canExpand())//如果可以展开并且初始化为隐藏,则显示展开图片,并添加showHide事件
content.append("<img src=images/plus.gif onClick=\"showHide('"
+ treeElmt.getID() + "')\">");
else if (treeElmt.canExpand())//如果可以展开,则显示折叠图片,并添加showHide事件
content.append("<img src=images/minus.gif onClick=\"showHide('"
+ treeElmt.getID() + "')\">");
else//否则显示空白图片
content.append("<img src=images/blank.gif>");
content.append("<img src=images/folder.gif><a href=\"#\"");//添加文件夹图片
if (treeElmt.canExpand())//如果可以展开,为节点添加showHide事件
content.append("onClick=\"showHide('" + treeElmt.getID() + "')\"");
content.append(">" + treeElmt.getNodeName() + "</a>");
if (treeElmt.canExpand()) {//如果可以展开,则用UL标记显示子结点
content.append("<ul");
if (bHidden)
content.append(" style=\"display:none;\" ");
content.append(">");
TreeviewElement[] elmts = treeElmt.getChildren();//获得此结点的子节点
for (int i = 0; i < elmts.length; i++)
content.append(renderStatic(elmts[i], bHidden));//递归调用显示子节点
content.append("</ul>");
}
content.append("</li>");//结束显示<li>元素
return content.toString();
} static public TreeviewElement[] getTopCategory() { String sql = "select category_id from product_category where parent_category_id=0";
ResultSet rs = DbManager.getResultSet(sql);// 执行sql语句并返回ResultSet
try {
rs.last();// 移动到最后一行
int row = rs.getRow();// 得到总记录数
if (row <= 0) {
return null;
} else {
Vector vData = new Vector();
rs.beforeFirst();
while (rs.next())
vData.add("" + rs.getInt("CATEGORY_ID"));
TreeviewElement[] children = new TreeviewElement[vData.size()];
for (int i = 0; i < vData.size(); i++) {
int id = Integer.parseInt((String) vData.get(i));
children[i] = new Category(id);
}
return children;
}
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {// 最后关闭ResutltSet,Statement.并释放连接
if (rs != null)
rs.close();
if (rs.getStatement() != null)
rs.getStatement().close();
DbManager.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
} }
static public String renderSimple(TreeviewElement treeElmt) {
StringBuffer content = new StringBuffer();
content.append("<li id=" + treeElmt.getID() + "><a href=\"#\">");
content.append( treeElmt.getNodeName() + "</a>");
if (treeElmt.canExpand()) {
content.append("<ul>");
TreeviewElement[] elmts = treeElmt.getChildren();
for (int i = 0; i < elmts.length; i++)
content.append(renderSimple(elmts[i]));
content.append("</ul>");
}
content.append("</li>");
return content.toString();
}
static public String renderTreeViewAjax(TreeviewElement treeElmt,boolean bRenderChildren) {
StringBuffer content = new StringBuffer();
content.append("<li id="+treeElmt.getID()+">");
if (treeElmt.canExpand())
content.append("<img src=images/plus.gif onClick=\"getSubTree('"+treeElmt.getID()+"')\">");
else
content.append("<img src=images/blank.gif>");
content.append("<img src=images/folder.gif><a href=\"#\"");
if (treeElmt.canExpand())
content.append(" onClick=\"getSubTree('"+treeElmt.getID()+"')\"");
content.append(">"+treeElmt.getNodeName()+"</a>");
if ( bRenderChildren&&treeElmt.canExpand( ))
{
content.append("<ul>");
TreeviewElement[] elmts = treeElmt.getChildren();
for (int i=0;i<elmts.length;i++)
content.append(renderTreeViewAjax(elmts[i],false));
content.append("</ul>");
}
content.append("</li>");
return content.toString();
}
}
package book.treeview;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;public class Category implements TreeviewElement {
protected int ID;//代表数据库中category_id列;
protected int parentID;//代表数据库中parent_category_id列;
protected String categoryName;//代表数据库中category_name列;
protected String activeStatus="Y";//代表数据库中active_status列;
public Category() {//无参的构造方法
this.ID = -1;
}
public Category(int id) {//有参的构造方法,参数为portlet_id
this.ID = id;
if (!FromDb())//如果有找到该id的porlet
this.ID = -1;
}
public boolean FromDb() {//从数据库中读出,并更新bean
int row = -1;
//读记录的sql语句
String sql = "select * from product_category where category_id=" + this.ID
+ " and active_status='Y'";
ResultSet rs = DbManager.getResultSet(sql);//执行sql语句并返回ResultSet
try {
rs.last();//移动到最后一行
row = rs.getRow();//得到总记录数
if (row == 1) {//如果只查询到一条记录,则代表该记录存在并更新该类的属性
this.parentID = rs.getInt("PARENT_CATEGORY_ID");
this.categoryName = rs.getString("CATEGORY_NAME");
this.activeStatus = rs.getString("ACTIVE_STATUS");
return true;
} else
return false;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {//最后关闭ResutltSet,Statement.并释放连接
if (rs != null)
rs.close();
if (rs.getStatement() != null)
rs.getStatement().close();
DbManager.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String getActiveStatus() {
return activeStatus;
}
public void setActiveStatus(String activeStatus) {
this.activeStatus = activeStatus;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public int getID() {
return ID;
}
public void setID(int id) {
ID = id;
}
public int getParentID() {
return parentID;
}
public void setParentID(int parentID) {
this.parentID = parentID;
}
public String getNodeName() {
return getCategoryName();
}
public boolean canExpand() {
String sql ="select category_id from product_category where parent_category_id="+getID();
ResultSet rs = DbManager.getResultSet(sql);//执行sql语句并返回ResultSet
try {
rs.last();//移动到最后一行
int row = rs.getRow();//得到总记录数
if (row <= 0)
return false;
else
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
try {//最后关闭ResutltSet,Statement.并释放连接
if (rs != null)
rs.close();
if (rs.getStatement() != null)
rs.getStatement().close();
DbManager.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public TreeviewElement[] getChildren() {
String sql ="select category_id from product_category where parent_category_id="+getID();
ResultSet rs = DbManager.getResultSet(sql);//执行sql语句并返回ResultSet
try {
rs.last();//移动到最后一行
int row = rs.getRow();//得到总记录数
if (row <= 0) {//如果没有子结点
return null;//返回null
}
else{//如果有子结点
Vector vData = new Vector();
rs.beforeFirst();
while (rs.next())
vData.add(""+rs.getInt("CATEGORY_ID"));
TreeviewElement[] children = new TreeviewElement[vData.size()];
for (int i=0;i<vData.size();i++)
{
int id = Integer.parseInt((String)vData.get(i));
children[i] = new Category(id);
}
return children;//返回该记录的所有子结点
}
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
//最后关闭ResutltSet,Statement.并释放连接
if (rs != null)
rs.close();
if (rs.getStatement() != null)
rs.getStatement().close();
DbManager.releaseConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//treesimple.jap
<%@ page language="java" import="java.util.*"%>
<%@ page import="book.treeview.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>simple tree view</title>
<link rel="stylesheet" href = "css/tree.css">
<script type="text/javascript" src="js/all.js"></script>
</head>
<body>
<ul class="tree">
<%
TreeviewElement[] top = treeviewRender.getTopCategory();
for(int i= 0;i<top.length;i++)
out.println(treeviewRender.renderSimple(top[i]));
%>
</ul>
</body>
</html>
上面源码出自 征服AJAX-WEB2.0技术详解运行环境 TOMCAT 5.0+SQL SERVER2000+JSDK1.4.2出现错误
java.sql.SQLException: [Microsoft][SQLServer JDBC Driver]Object has been closed.
如果有高手解决了这个问题
或需要更多代码我给传过去因为用到了AJAX+XML
我不会
所以代码基本和书本没多大变化
我只是把关键的可能有问题的给大家传上去
怀疑DbManager.java有问题
我以前编的都是动态的
静态不会!
急求解决方案