SQL树形结构查询ById

CREATE FUNCTION [dbo].[fn_FindColumnTree] (@columnID INTEGER)
RETURNS @FindColumnTree TABLE 
(
    ColumnID int  NOT NULL,
    ColumnName varchar(256) NOT NULL,
    p_id int  NULL,
	p_name varchar(256)  null
)
AS
BEGIN
   WITH Col_Recursive(ColumnName, ColumnID, T_C_ColumnID) 
    AS
	(SELECT ColumnName, ColumnID,T_C_ColumnID  FROM T_Column WHERE ColumnID = @columnID
	UNION ALL
	SELECT p.ColumnName, p.ColumnID, p.T_C_ColumnID
	FROM T_Column  P  INNER JOIN
	Col_Recursive A ON A.ColumnID = P.T_C_ColumnID 
	)
-- copy the required columns to the result of the function 
   INSERT @FindColumnTree
   SELECT a.ColumnID,a.ColumnName,a.T_C_ColumnID,b.ColumnName as p_name
   FROM Col_Recursive as a left join T_Column as b on a.T_C_ColumnID=b.ColumnID
   RETURN
END;

 

 

其中ColumnName 代表栏目名字 ColumnID 栏目 ID T_C_ColumnID 父栏目Id

usage:

 

public List<Node> buildTree(String rootId) {
		final String sql=	"select ColumnID, ColumnName ,T_C_ColumnID FROM T_Column where ColumnID in (select ColumnID from dbo.fn_FindColumnTree("+
		rootId+
		"))";     //sql调用存储过程查询树的语句.
		List<Node> result = new ArrayList<Node>();
		List tree=	this.hibernateTemplate.executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session)
			throws HibernateException, SQLException {
				return session
				.createSQLQuery(sql)
				.list();
			}
		});
		
		for (Iterator iterator = tree.iterator(); iterator.hasNext();) {
			Node node = new Node();
			Object[] obj = (Object[]) iterator.next();
			node.setId(obj[0].toString());
			node.setName(obj[1].toString());
			node.setPid(obj[2]!=null?obj[2].toString():"0");
			node.setUrl("http://www.baidu.com");
			if(node.getId().equals(rootId)){
				node.setPid("0");
			}
			result.add(node);
		}
		return result;
	}