| |
原理请参见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;
}
}
|
|
