dao.java

该源代码实现乐拖拽树的功能

package com.dt.test.mytree;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.dt.test.pojo.Dir;
import com.dt.test.pojo.Node;
import com.dt.test.pojo.Point;

public class Dao {
	
	private Connection conn;
	
	public Dao(Connection conn){
		this.conn=conn;
	}
	
	public void dropTable(){
		Statement statement=null;
		String sql="drop table cbTree";
		try {
			statement=conn.createStatement();
			statement.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void createTable(){
		Statement statement=null;
		try {
			statement=conn.createStatement();
			/*
			 * type为0表示节点，1表示目录
			 */
			String sql="create table cbTree(id number primary key,pid number,name varchar2(20),type number(1))";
			statement.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void insertData(){
		Statement statement=null;
		try {
			statement=conn.createStatement();
			String sql1="insert into cbTree(id,pid,name,type) values(1,null,'point1',1)";
			String sql2="insert into cbTree(id,pid,name,type) values(2,1,'point2',1)";
			String sql3="insert into cbTree(id,pid,name,type) values(3,1,'point3',1)";
			String sql4="insert into cbTree(id,pid,name,type) values(4,2,'point4',1)";
			String sql5="insert into cbTree(id,pid,name,type) values(5,3,'point5',0)";
			String sql6="insert into cbTree(id,pid,name,type) values(6,3,'point6',0)";
			String sql7="insert into cbTree(id,pid,name,type) values(7,4,'point7',0)";
			String sql8="insert into cbTree(id,pid,name,type) values(8,4,'point8',0)";
			String sql9="insert into cbTree(id,pid,name,type) values(9,2,'point9',0)";
			String sql10="insert into cbTree(id,pid,name,type) values(10,2,'point10',1)";
			String sql11="insert into cbTree(id,pid,name,type) values(11,2,'point11',1)";
			String sql12="insert into cbTree(id,pid,name,type) values(12,3,'point12',1)";
			String sql13="insert into cbTree(id,pid,name,type) values(13,4,'point13',1)";
			String sql14="insert into cbTree(id,pid,name,type) values(14,4,'point14',1)";
			String sql15="insert into cbTree(id,pid,name,type) values(15,1,'point15',1)";
			String sql16="insert into cbTree(id,pid,name,type) values(16,1,'point16',1)";
			String sql17="insert into cbTree(id,pid,name,type) values(17,3,'point17',1)";
			String sql18="insert into cbTree(id,pid,name,type) values(18,3,'point18',1)";
			statement.executeUpdate(sql1);
			statement.executeUpdate(sql2);
			statement.executeUpdate(sql3);
			statement.executeUpdate(sql4);
			statement.executeUpdate(sql5);
			statement.executeUpdate(sql6);
			statement.executeUpdate(sql7);
			statement.executeUpdate(sql8);
			statement.executeUpdate(sql9);
			statement.executeUpdate(sql10);
			statement.executeUpdate(sql11);
			statement.executeUpdate(sql12);
			statement.executeUpdate(sql13);
			statement.executeUpdate(sql14);
			statement.executeUpdate(sql15);
			statement.executeUpdate(sql16);
			statement.executeUpdate(sql17);
			statement.executeUpdate(sql18);
			conn.commit();
			System.out.println("insert commit");
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public List<Point> queryData(){
		Statement statement=null;
		List<Point> points=new ArrayList<Point>();
		try {
			statement=conn.createStatement();
			String sql="select id,pid,name,type,level from cbTree " +
					"start with pid is null " +
					"connect by prior id=pid";
			//String sql="select * from cbTree";
			ResultSet rs=statement.executeQuery(sql);
			while(rs.next()){
				Long id=Long.parseLong(rs.getString("id"));
				Long pid=null;
				if(rs.getString("pid")==null){
					pid=0L;
				}else{
					pid=Long.parseLong(rs.getString("pid"));
				}
				Long type=Long.parseLong(rs.getString("type"));
				String name=rs.getString("name");
				Long level=Long.parseLong(rs.getString("level"));
				Dir dir=null;
				Node node=null;
				if(type==0){
					node=new Node(id,pid,name,"node",level,"real");
					points.add(node);
				}else{
					dir=new Dir(id,pid,name,"dir",level,"real");
					points.add(dir);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return points;
	}
	
	public void updatePointByPid(Long id,Long pid){
		String sql="update cbTree set pid="+pid+" where id="+id;
		Statement statement=null;
		try {
			statement=conn.createStatement();
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public Boolean deleteById(Long id){
		String sql="select id from cbTree start with pid="+id+" connect by prior id=pid";
		String deleteParent="delete from cbTree where id="+id;
		Statement statement=null;
		Boolean flag=false;
		List<String> allChildId=new ArrayList<String>();
		try {
			statement=conn.createStatement();
			ResultSet rs=statement.executeQuery(sql);
			
			while(rs.next()){
				allChildId.add(rs.getString("id"));
			}
			if(allChildId.size()!=0){
				for(String str:allChildId){
					String deleteSql="delete from cbTree where id="+str;
					statement.execute(deleteSql);
				}
			}
			statement.execute(deleteParent);
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	public Point findById(Long pointId){
		String sql="select * from cbTree where id="+pointId;
		Statement statement=null;
		Point p=null;
		try {
			statement=conn.createStatement();
			ResultSet rs=statement.executeQuery(sql);
			if(rs.next()){
				Long id=Long.parseLong(rs.getString("id"));
				Long pid=null;
				if(rs.getString("pid")==null){
					pid=0L;
				}else{
					pid=Long.parseLong(rs.getString("pid"));
				}
				Long type=Long.parseLong(rs.getString("type"));
				String name=rs.getString("name");
				Long level=Long.parseLong(rs.getString("level"));
				Dir dir=null;
				Node node=null;
				if(type==0){
					p=new Node(id,pid,name,"node",level,"real");
				}else{
					p=new Dir(id,pid,name,"dir",level,"real");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return p;
		
	}
	
	public Point findChildById(Long pointId){
		String sql="select * from cbTree where pid="+pointId;
		Statement statement=null;
		Point p=null;
		try {
			statement=conn.createStatement();
			ResultSet rs=statement.executeQuery(sql);
			while(rs.next()){
				Long id=Long.parseLong(rs.getString("id"));
				Long pid=null;
				if(rs.getString("pid")==null){
					pid=0L;
				}else{
					pid=Long.parseLong(rs.getString("pid"));
				}
				Long type=Long.parseLong(rs.getString("type"));
				String name=rs.getString("name");
				Long level=Long.parseLong(rs.getString("level"));
				Dir dir=null;
				Node node=null;
				if(type==0){
					p=new Node(id,pid,name,"node",level,"real");
				}else{
					p=new Dir(id,pid,name,"dir",level,"real");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return p;
	}
	
}