原理请参见www.chinaASP.com/sqlbbs中的ASP论坛版精华区
本例采用Access做为实例，所有还有很多要改进的地方。
请读者自已改进
package prodUCt;
import Java.sql.*;
public class ConnectDB{
String strDBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
String strConnstr="jdbc:odbc:product";
Connection conn=null;
ResultSet rs=null;
Statement stmt=null;
// Statement stmt=null;
//构造函数
public ConnectDB(){
try{
Class.forName(strDBDriver); conn=DriverManager.getConnection(strConnstr);
stmt=conn.createStatement();
}
catch(Exception e){
System.out.println(e);
}
}
public ResultSet execute(String sql){
rs=null;
try{
rs=stmt.executeQuery(sql);
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return rs;
}
public Connection getConnection(){
return conn;
}
public boolean close(){
try{
if(this.rs!=null){
this.rs.close();
}
if(stmt!=null){
this.stmt.close();
}
if(conn!=null){
this.conn.close();
}
return true;
}catch(Exception err){
return false;
}
}
}
//=======================================
package product;
import java.sql.*;
public class catalog
{
ConnectDB conn=new ConnectDB();
String str=null;
String sql=null;
ResultSet rs=null;
String AncestorID,LinkStr;
public String getCatalog(){
str="<select name="FatherID" class="p9"> "+
"<option value="-1" selected>请选择父类</option> ";
String sql=null;
sql="select * from catalog order by AncestorID,Linkstr";
ResultSet rs=conn.execute(sql);
try{
while (rs.next()) {
str=str+"<option value="+rs.getString("Productid")+">";
int nbspCount=rs.getString("LinkStr").length()-1;
for(int i=0;i<nbspCount;i++){
str=str+" ";
}
if(nbspCount>0)
str=str+"┠"+rs.getString("title")+"</option> ";
else
str=str+rs.getString("title")+"</option> ";
}
str=str+"</select>";
rs.close();
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return str;
}
public void setCatalog(String CatalogName,String FatherID){
int ChildNum=0;
if(FatherID.compareTo("-1")!=0){
sql="select AncestorID,ChildNum,linkstr from catalog where productid="+FatherID;
//out.println(sql);
try{
rs=conn.execute(sql);
if(rs.next()){
AncestorID=rs.getString("AncestorID");
ChildNum=rs.getInt("ChildNum");
LinkStr=rs.getString("linkstr");
}
else{
AncestorID="";
LinkStr="1";
ChildNum=0;
}
LinkStr=LinkStr+(ChildNum+1);
//out.println(AncestorID+":"+LinkStr+":"+ChildNum);
sql="insert into catalog(title,fatherID,AncestorID,LinkStr) values(′′"+CatalogName+"′′,′′"+FatherID+"′′,′′"+AncestorID+"′′,′′"+LinkStr+"′′)";
//out.println(sql);
conn.execute(sql);
sql="update catalog set childNum="+(ChildNum+1)+" where productID="+FatherID;
//out.println("<br>"+sql);
conn.execute(sql);
}catch(Exception e){
System.out.println(e);
}
}else{
sql="insert into catalog(title,fatherID,LinkStr) values(′′"+CatalogName+"′′,′′"+FatherID+"′′,′′1′′)";
//out.println(sql);
try{
conn.execute(sql);
sql="select top 1 productID from catalog order by productID desc ";
rs=conn.execute(sql);
if(rs.next()){
AncestorID=rs.getString("productID");
}
else{
AncestorID="";
}
rs.close();
sql="update catalog set AncestorID=′′"+AncestorID+"′′ where productID="+AncestorID;
//out.println(sql);
conn.execute(sql);
}catch(Exception e){
System.out.println(e);
}
}//end if
if(rs!=null){
try{
rs.close();
}catch(Exception e){
System.out.println(e);
}
}
}
public void deleteCatalog(String FatherID){
int tempData=0;
sql="select fatherID,linkstr,AncestorID from catalog where productID="+FatherID;
rs=conn.execute(sql);
try{
if(rs.next()){
tempData=rs.getInt("fatherID");
LinkStr=rs.getString("linkstr");
AncestorID=rs.getString("AncestorID");
}
try{rs.close();}
catch(Exception e){
System.out.println(e);
}
sql="delete from catalog where ProductID="+FatherID;
// out.println(sql);
try{
conn.execute(sql);
}catch(Exception e){
System.out.println(e);
}
sql="delete from catalog where linkstr like ′′"+LinkStr+"%′′ and AncestorID="+AncestorID;
//out.println(sql);
try{
conn.execute(sql);
}catch(Exception e){
System.out.println(e);
}
sql="update catalog set ChildNum=ChildNum-1 where productID="+tempData;
//out.println(sql);
try{
conn.execute(sql);
}catch(Exception e){
System.out.println(e);
}
}catch(Exception e){
System.out.println(e);
}
} //end public
public void updateCatalog(int id){
}
public String getCatalog(int id){
str="<select name="FatherID" class="p9"> "+
"<option value="-1" >请选择父类</option> ";
String sql=null;
sql="select * from catalog order by AncestorID,Linkstr";
ResultSet rs=conn.execute(sql);
try{
while (rs.next()) {
int ProductID=rs.getInt("productID");
if (ProductID==id) {
str=str+"<option value="+ProductID+" selected>";
}
else{
str=str+"<option value="+ProductID+">";
}
int nbspCount=rs.getString("LinkStr").length()-1;
for(int i=0;i<nbspCount;i++){
str=str+" ";
}
if(nbspCount>0)
str=str+"┠"+rs.getString("title")+"</option> ";
else
str=str+rs.getString("title")+"</option> ";
}
str=str+"</select>";
rs.close();
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return str;
}
public String getCatalogList(){
str="<select name="FatherID" class="p9" size="20" onClick="MM_jumpMenu(′′parent.mainFrame′′,this,0)"> "+
"<option value="-1" selected>请选择父类</option> ";
String sql=null;
sql="select * from catalog order by AncestorID,Linkstr";
ResultSet rs=conn.execute(sql);
try{
while (rs.next()) {
str=str+"<option value="+rs.getString("Productid")+">";
int nbspCount=rs.getString("LinkStr").length()-1;
for(int i=0;i<nbspCount;i++){
str=str+" ";
}
if(nbspCount>0)
str=str+"┠"+rs.getString("title").trim()+"</option> ";
else
str=str+rs.getString("title")+"</option> ";
}
str=str+"</select>";
rs.close();
}
catch(SQLException e){
System.out.println(e.getMessage());
}
return str;
}
}
