In this example, Access is used as an instance, and there are still many improvements.
Please improve yourself
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;
// Constructor
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 {
Rs1_stmt.exe cuteQuery (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> select the parent class </option>
";
String SQL = null;
SQL = "select * from catalog order by AncestorID, Linkstr ";
ResultSet rs1_conn.exe cute (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 + "& nbsp ;";
}
If (nbspCount> 0)
Str = str + "response" + 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 {
Rsw.conn.exe cute (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.exe cute (SQL );
SQL = "update catalog set childNum =" + (ChildNum + 1) + "where productID =" + FatherID;
// Out. println ("<br>" + SQL );
Conn.exe cute (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.exe cute (SQL );
SQL = "select top 1 productID from catalog order by productID desc ";
Rsw.conn.exe cute (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.exe cute (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;
Rsw.conn.exe cute (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.exe cute (SQL );
} Catch (Exception e ){
System. out. println (e );
}
SQL = "delete from catalog where linkstr like" "+ LinkStr +" % "and AncestorID =" + AncestorID;
// Out. println (SQL );
Try {
Conn.exe cute (SQL );
} Catch (Exception e ){
System. out. println (e );
}
SQL = "update catalog set ChildNum = ChildNum-1 where productID =" + tempData;
// Out. println (SQL );
Try {
Conn.exe cute (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 "> select the parent class </option>
";
String SQL = null;
SQL = "select * from catalog order by AncestorID, Linkstr ";
ResultSet rs1_conn.exe cute (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 + "& nbsp ;";
}
If (nbspCount> 0)
Str = str + "response" + 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> select the parent class </option>
";
String SQL = null;
SQL = "select * from catalog order by AncestorID, Linkstr ";
ResultSet rs1_conn.exe cute (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 + "& nbsp ;";
}
If (nbspcount> 0)
STR = STR + "response" + 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;
}
}