用sql的start with 和connect by prior 递归构造父子节点树

1、简单介绍SQL的start with 和connect by prior语句

select *        //用视图中选择要查的东西
from Table        //从哪个表中查
start with id = 1    //以哪个ID为开始,即跟节点ID
connect by prior id = parent_id     //链接通过前一个ID和现在的父ID相等，定义子节点和父节点的关系
order by id ;    //排个序

例如：

SELECT * FROM TB_UNIT kpl START WITH kpl.UNITID='001' CONNECT BY PRIOR kpl.UNITID = kpl.SUPERUNITID ORDER BY kpl.UNITID

2、用Java实现json格式构造树

第一步：首先建立个树

    
public class TreeNode{

        private String id;    //ID
	
	private String text;    //内容
	
	private String leaf;    //是否是叶子节点（结束）
	
	private String parent_id;    //父ID
	
	private List children;    //孩子
    
        //一堆set和get方法
    
        public boolean parent(TreeNode node){    //判断是否父子关系
		if(parent_id.equals(node.getId()))    //这里是比较字符串，比较null会报错
			return true;
		else
			return false;
	}

}

第二步：在dao层从数据库中生成树节点结构

public List buildUnitTree(final String rootid){
        String sql = "SELECT * FROM TB_UNIT KPI START WITH KPI.UNITID='"+rootid+"' CONNECT BY PRIOR KPI.UNITID = KPI.SUPERUNITID ORDER BY KPI.UNITID";

        final List<TreeNode> tree = new ArrayList<TreeNode>();//存储节点形成树
        
        jdbc.query(sql,new PreparedStatementSetter() {
                    public void setValues(PreparedStatement ps) throws SQLException {
                    }},
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        TreeNode node = new TreeNode();
                        node.setId(rs.getString("UNITID"));
                        node.setText(rs.getString("UNITNAME"));
                        //这个是因为我的数据库中rootID的父ID是空，
                        //因为在TreeNode类中比较父类是用字符串比较的，如果不转换就会空指针报错
                        if(rs.getString("SUPERUNITID")==null)    
                            node.setParent_id("");
                        else
                            node.setParent_id(rs.getString("SUPERUNITID"));
                        if(isLeafNode(rs.getString("UNITID"))){    //查看是否是叶子节点
                            node.setLeaf("true");
                        }else{
                            node.setLeaf("false");
                        }
                        tree.add(node);
                    }
                }
        );
        return tree;
    }

    public boolean isLeafNode(String code){    //查看是否是叶子节点
        final Vector<Boolean> v = new Vector<Boolean>();
        String schema = DataParamReader.getDataParam("UAP_SCHEMA_NAME");
        String sql = "SELECT * FROM TB_UNIT WHERE SUPERUNITID='"+code+"'";
        //用父ID查，如果没有ID说明是叶子节点
        jdbc.query(sql,new RowCallbackHandler() {    
            public void processRow(ResultSet rs) throws SQLException{
                Boolean bL = new Boolean(false);
                v.add(bL);
            }
        });
        if(v.size()!=0)
            return false;
        else
            return true;
    }

第三步：在service层进行拼接

public String buildTree(String rootid){
        List<TreeNode> tree = this.dao.buildUnitTree(root);
        String josn = "[{";
        for(int i=0;i<tree.size();i++){
            TreeNode node = (TreeNode)tree.get(i);
            if(node.getId().equalsIgnoreCase(rootid)){
                josn+="id:'"+rootid+"',text:'"+node.getText()+"'";
                if(node.getLeaf().equals("true")){
                    josn+=",leaf:true}]";
                    break;
                }else{
                    josn += ",children: [";
                    josn += buildChildren(node,tree);
                    if(",".equals(josn.substring(josn.length()-1)))
                        josn = josn.substring(0, josn.length()-1);
                    josn += "]}]";

                }
            }
        }
        return josn;
    }
    private String buildChildren(TreeNode node,List<TreeNode> tree){
        String josn = "";
        for(int i=0;i<tree.size();i++){
            if(josn.endsWith(",}]")){
                josn = josn.substring(0, josn.length()-3)+"]},";
            }
            TreeNode i_node =  (TreeNode)tree.get(i);
            if(i_node.parent(node)){
                josn +="{ id:'"+i_node.getId()+"',text:'"+i_node.getText()+"',leaf:"+i_node.getLeaf();
                if("true".equals(i_node.getLeaf())){
                    josn += "},";
                }else{
                    josn += ",children: [";
                    josn += buildChildren(i_node,tree);
                    josn += "}]";
                }
            }
        }
        return josn;
    }