首先说明数据库的表:表名 category
字段名
id 为自增长主键
name 为分类名称
descr 为说明
pid 为父ID
isleaf 是否为根节点 ? 0为非叶子节点 1是叶子节点
grade 为树状结构的等级
+----+-------------+-------------+------+--------+-------+
| id | name | descr | pid | isleaf | grade |
+----+-------------+-------------+------+--------+-------+
| 1 | 测试1 | 测试1 | 0 | 0 | 1 |
| 2 | 测试2 | 测试2 | 0 | 0 | 1 |
| 3 | 测试3 | 测试3 | 0 | 1 | 1 |
| 4 | 测试3-1 | 测试3-1 | 3 | 0 | 2 |
| 5 | 测试3-2 | 测试3-2 | 3 | 0 | 2 |
| 6 | 测试3-3 | 测试3-3 | 3 | 1 | 2 |
| 7 | 测试3-3-1 | 测试3-3-1 | 6 | 1 | 3 |
| 8 | 测试3-3-2 | 测试3-3-2 | 7 | 1 | 4 |
| 9 | 测试3-3-3 | 测试3-3-3 | 8 | 1 | 5 |
| 10 | 测试3-3-4 | 测试3-3-4 | 9 | 0 | 6 |
+----+-------------+-------------+------+--------+-------+页面表现如下:
所有分类
┗测试1
┗测试2
┗测试3
┗测试3-1
┗测试3-2
┗测试3-3
┗测试3-3-1
┗测试3-3-2(如果我从这里开始删除用递归方法该怎么做?删除后把测试3-3-1设置成非叶子节点0)
┗测试3-3-3
┗测试3-3-4
代码
public static void deleteCategory(int id) {
可以获得ID值!请各位学者学友指点我些办法!小弟我受用不尽!
}
字段名
id 为自增长主键
name 为分类名称
descr 为说明
pid 为父ID
isleaf 是否为根节点 ? 0为非叶子节点 1是叶子节点
grade 为树状结构的等级
+----+-------------+-------------+------+--------+-------+
| id | name | descr | pid | isleaf | grade |
+----+-------------+-------------+------+--------+-------+
| 1 | 测试1 | 测试1 | 0 | 0 | 1 |
| 2 | 测试2 | 测试2 | 0 | 0 | 1 |
| 3 | 测试3 | 测试3 | 0 | 1 | 1 |
| 4 | 测试3-1 | 测试3-1 | 3 | 0 | 2 |
| 5 | 测试3-2 | 测试3-2 | 3 | 0 | 2 |
| 6 | 测试3-3 | 测试3-3 | 3 | 1 | 2 |
| 7 | 测试3-3-1 | 测试3-3-1 | 6 | 1 | 3 |
| 8 | 测试3-3-2 | 测试3-3-2 | 7 | 1 | 4 |
| 9 | 测试3-3-3 | 测试3-3-3 | 8 | 1 | 5 |
| 10 | 测试3-3-4 | 测试3-3-4 | 9 | 0 | 6 |
+----+-------------+-------------+------+--------+-------+页面表现如下:
所有分类
┗测试1
┗测试2
┗测试3
┗测试3-1
┗测试3-2
┗测试3-3
┗测试3-3-1
┗测试3-3-2(如果我从这里开始删除用递归方法该怎么做?删除后把测试3-3-1设置成非叶子节点0)
┗测试3-3-3
┗测试3-3-4
代码
public static void deleteCategory(int id) {
可以获得ID值!请各位学者学友指点我些办法!小弟我受用不尽!
}
封装在了我Dao的最后一个方法package com;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* <p>Title: <pre>数据库操作类Dao</pre></p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2008-2009</p>
* @author 王鑫
* @version
* @create 2009-1-18
*/
public class Dao
{
private String quDong="com.microsoft.jdbc.sqlserver.SQLServerDriver";
private String ip="192.168.0.32";
private String post="1433";
private String kuMing="UserSys";
private String ming="sa";
private String mima="sa";
private void jiaQu()
{
try
{
Class.forName(quDong);
}
catch (ClassNotFoundException e)
{
System.out.println("加载驱动出错 请检查外部包 ");
System.out.println(e.getMessage());
e.printStackTrace();
}
}
private Connection jianLian()
{
try
{
String url = "jdbc:microsoft:sqlserver://"+ip+":"+post+";DatabaseName="+kuMing;
Connection conn=DriverManager.getConnection(url, ming, mima);
return conn;
}
catch (SQLException e)
{
System.out.println("建立连接出错:请检查账号密码 或者 数据库名称 及数据库IP ");
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
private Statement shengMing(Connection conn)
{
try
{
Statement stmt=conn.createStatement();
return stmt;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
private int zhixingzsg(Statement stmt,String sql)
{
try
{
int tiao=stmt.executeUpdate(sql);
return tiao;
}
catch (SQLException e)
{
System.out.println("执行以上SQL语句错误!");
System.out.println(e.getMessage());
e.printStackTrace();
}
return 0;
}
private void guanRs(ResultSet rs)
{
if(rs==null)
{
System.out.println("rs null");
return;
}
try
{
rs.close();
}
catch (SQLException e)
{
System.out.println("guanRs bug"+e.getMessage());
e.printStackTrace();
}
}
private void guanStmt(Statement stmt)
{
if(stmt==null)
{
System.out.println("stmt null");
return;
}
try
{
stmt.close();
}
catch (SQLException e)
{
System.out.println("guanStmt bug"+e.getMessage());
e.printStackTrace();
}
}
private void guanConn(Connection conn)
{
if(conn==null)
{
System.out.println("conn null");
return;
}
try
{
conn.close();
System.gc();//清除垃圾内存
}
catch (SQLException e)
{
System.out.println("guanConn bug"+e.getMessage());
e.printStackTrace();
}
}
/*
* 根据表名和条件查询出记录个数
*/
public int chaGeShu(String biaoMing,String tiaoJian)
{
String sql="Select Count(*) geShu From "+biaoMing+" Where "+tiaoJian;
Map map=this.chaMap(sql);
return (Integer) map.get("geShu");
}
/*
* 根据表名查询出记录个数
*/
public int chaGeShu(String biaoMing)
{
String sql="Select Count(*) geShu From "+biaoMing;
Map map=this.chaMap(sql);
return (Integer) map.get("geShu");
}
/*
* 根据任意SQL语句进行查询,返回首行 Map的结果
* */
public Map chaMap(String sql)
{
Map map=null;
List list=this.chaList(sql);
if(list.size()!=0)
{
map=(Map)list.get(0);
}
return map;
}
/*
* 根据任意SQL语句执行 返回影响条数
*/
public int zhixingSQL(String sql)
{
this.jiaQu();
Connection conn= this.jianLian();
Statement stmt= this.shengMing(conn);
try
{
System.out.println("执行修改数据库SQL:\n"+sql);
return this.zhixingzsg(stmt, sql);
} catch (Exception e) {
System.out.println("执行以上SQL语句错误!");
e.printStackTrace();
}
finally
{
this.guanStmt(stmt);
this.guanConn(conn);
}
return 0;
}
/*
* 根据任意SQL语句进行查询,返回List 加 Map的结果
*
* */
public List chaList(String sql)
{
this.jiaQu();
Connection conn= this.jianLian();
Statement stmt= this.shengMing(conn);
ResultSet rs=null;
try
{
System.out.println("执行查询SQL:\n"+sql);
rs=stmt.executeQuery(sql);
List list=new ArrayList();
while(rs.next())
{
Map map=new HashMap();
ResultSetMetaData rsmd=rs.getMetaData();
for(int i=1;i<=rsmd.getColumnCount();i++)
{
String ming=rsmd.getColumnName(i);
map.put(ming, rs.getObject(ming));
} list.add(map);
}
return list;
}
catch (SQLException e)
{
System.out.println("执行以上SQL语句错误!");
e.printStackTrace();
}
finally
{
guanRs(rs);
guanStmt(stmt);
guanConn(conn);
}
return null;
}
/*
* 按SQL语句查询 返回第pag页的数据
* 每页evel条
* 返回List 里面是Map
* chaList(String 查询SQL语句, String 当前页数, int 每页条数)
*/
public List chaList(String sql,String pag,int evel)
{
int page;
try
{
page = Integer.parseInt(pag);
}
catch (NumberFormatException e)
{
page=1;
}
if(page<1)
{
page=1;
}
List tlist=this.chaList(sql);
List list=new ArrayList();
int i=(page-1)*evel;
while(i<tlist.size()&&i<((page)*evel))
{
Map map=(Map)tlist.get(i);
list.add(map);
i++;
}
tlist=null;
return list;
}
/*
* 事务处理
* 传入SQL String[]
* 返回执行是否成功
*/
public boolean shiWu(String[] sqls){
this.jiaQu();
Connection conn= this.jianLian();
Statement stmt= this.shengMing(conn);
try
{
int chengGong=1;
System.out.println("事务处理开始");
for(int i=0;i<sqls.length;i++)
{
stmt.addBatch(sqls[i]);
}
conn.setAutoCommit(false);
for(int i=0;i<sqls.length;i++)
{
System.out.println("执行的SQL:"+sqls[i]);
chengGong=stmt.executeUpdate(sqls[i]);
if(chengGong==0)
{
System.out.println("失败");
break;
}
}
System.out.println("事务处理结束");
if(chengGong==0)
{
conn.rollback();
System.out.print("事务处理未成功!");
}
else
{
conn.commit();
return true;
}
}
catch (SQLException e)
{
System.out.println("执行以上SQL语句错误!");
e.printStackTrace();
}
finally
{
guanStmt(stmt);
guanConn(conn);
}
return false;
}
/*
* 无极树删除(递归)
*/
public static void deleteCategory(int id)
{
Dao dao=new Dao();
Map map=dao.chaMap("Select isleaf from category where id=" + id);
if((Integer)map.get("isleaf")==1)
{
String sql="Delete From category Where id=" + id;
dao.zhixingSQL(sql);
}
else
{
List list=dao.chaList("Select id from category where pid=" + id);
for(int i=0;i<list.size();i++)
{
Dao.deleteCategory((Integer)((Map)list.get(i)).get("id"));
}
}
}
}
其实一句sql就可以实现