java拼接树形结构数据

java拼接树形结构

大部分的项目树状结构前台js都是用的异步查询，需要一次加载的时候都是递归查询，

但是 这个时候层数一多sql多次打开连接就太慢，找了个一次加载的参照参照http://www.iteye.com/topic/1122125

首先要sql层抽出所有树状结构数据使用的是oracle的树状查询，用多了orm框架就不太喜欢原生sql但是很多时候还是原生sql来的方便快捷

select m1.* from
(SELECT t1.NODE_ID,
  NVL(t1.PARENT_NODE_ID,0) PARENT_NODE_ID,
  T1.TAG_ID,
  T3.TAG_NM
FROM MS_KOJI_KAISHA t1,
  MS_KOJI t2,
  MS_TAG t3
WHERE t1.tag_id        = t2.tag_id
AND t2.tag_id          = t3.tag_id
AND T1.PARENT_NODE_ID IS NULL
UNION ALL
SELECT t1.NODE_ID,
  NVL(t1.PARENT_NODE_ID,0) PARENT_NODE_ID,
  T1.TAG_ID,
  T3.TAG_NM
FROM MS_KOJI_KAISHA t1,
  MS_KAISHA t2,
  MS_TAG t3
WHERE t1.tag_id        = t2.tag_id
AND t2.tag_id          = t3.tag_id
AND T1.PARENT_NODE_ID IS NOT NULL 
)m1 ,(SELECT t1.NODE_ID,
  NVL(t1.PARENT_NODE_ID,0) PARENT_NODE_ID,
  T1.TAG_ID,
  T3.TAG_NM
FROM MS_KOJI_KAISHA t1,
  MS_KOJI t2,
  MS_TAG t3
WHERE t1.tag_id        = t2.tag_id
AND t2.tag_id          = t3.tag_id
AND T1.PARENT_NODE_ID IS NULL
UNION ALL
SELECT t1.NODE_ID,
  NVL(t1.PARENT_NODE_ID,0) PARENT_NODE_ID,
  T1.TAG_ID,
  T3.TAG_NM
FROM MS_KOJI_KAISHA t1,
  MS_KAISHA t2,
  MS_TAG t3
WHERE t1.tag_id        = t2.tag_id
AND t2.tag_id          = t3.tag_id
AND T1.PARENT_NODE_ID IS NOT NULL 
)m2
where m1.NODE_ID= m2.PARENT_NODE_ID(+)

层次没有原生sql的层次排的好，想要排的好需要对表加些辅助排序字段做些修改。实际已经在使用的项目不太可能去改动，所以还是用原生的sql了

StringBuffer sb = new StringBuffer();
		sb.append("SELECT m1.NODE_ID, ");
		sb.append("       nvl(m1.PARENT_NODE_ID,0), ");
		sb.append("       m1.TAG_ID, ");
		sb.append("       m1.TAG_NM ");
//		sb.append("       SUBSTR(SYS_CONNECT_BY_PATH(m1.TAG_NM,'->'),3)   NAME_PATH ");
		sb.append("FROM (");
		sb.append("       SELECT t1.NODE_ID, ");
		sb.append("              t1.PARENT_NODE_ID, ");
		sb.append("              T1.TAG_ID, ");
		sb.append("              T3.TAG_NM ");
		sb.append("       FROM   MS_KOJI_KAISHA t1, ");
		sb.append("              MS_KOJI t2, ");
		sb.append("              MS_TAG t3 ");
		sb.append("       WHERE  t1.tag_id          = t2.tag_id ");
		sb.append("       AND    t2.tag_id          = t3.tag_id ");
		sb.append("       AND    T1.PARENT_NODE_ID IS NULL ");
		sb.append("       UNION ALL ");
		sb.append("       SELECT t1.NODE_ID, ");
		sb.append("              t1.PARENT_NODE_ID, ");
		sb.append("              T1.TAG_ID, ");
		sb.append("              T3.TAG_NM ");
		sb.append("       FROM   MS_KOJI_KAISHA t1, ");
		sb.append("              MS_KAISHA t2, ");
		sb.append("              MS_TAG t3 ");
		sb.append("       WHERE  t1.tag_id          = t2.tag_id ");
		sb.append("       AND    t2.tag_id          = t3.tag_id ");
		sb.append("       AND    T1.PARENT_NODE_ID IS NOT NULL ");
		sb.append("       ) m1 ");
		sb.append("START WITH m1.PARENT_NODE_ID IS NULL CONNECT BY PRIOR m1.NODE_ID = m1.PARENT_NODE_ID ");
		sb.append("ORDER SIBLINGS BY m1.TAG_ID ");
		  
		Query query = em.createNativeQuery(sb.toString());
		
		List objecArraytList = query.getResultList();
		for (int i = 0; i < objecArraytList.size(); i++) {
			Object[] obj = (Object[]) objecArraytList.get(i);
			KojiKaishaResult kojiKaishaResult=new KojiKaishaResult();
			kojiKaishaResult.setNodeid(obj[0].toString());
			kojiKaishaResult.setParentid(obj[1].toString());
			kojiKaishaResult.setTagid((String) obj[2]);
			kojiKaishaResult.setTagnm((String) obj[3]);
			list.add(kojiKaishaResult);
		}  

然后前台java处理成前台js需要的json格式

public List<TagResult> MultipleTree(List<KojiKaishaResult> dataList) {
		 // 无限层的实现 拼凑好的json格式的数据
		List<TagResult> list = new ArrayList<TagResult>();
                // 节点列表（散列表，用于临时存储节点对象）
		HashMap<String, TagResult> nodeMap = new HashMap<String, TagResult>();
		// parentnodes存放所有的父节点
		List<TagResult> parentnodes = new ArrayList<TagResult>();
		//TagResult root = null;
                // 根据结果集构造节点列表（存入散列表）
		for (Iterator it = dataList.iterator(); it.hasNext();) {
			KojiKaishaResult dataRecord = (KojiKaishaResult) it.next();
			TagResult node = new TagResult();
			node.setId(dataRecord.getNodeid());
			node.setParentId(dataRecord.getParentid());
			node.setText(dataRecord.getTagnm());
			if(dataRecord.getParentid().equals("0")){
				node.setIconCls("icon-work");
			}else{
				node.setIconCls("icon-shop");
			}
			HashMap<String, String> attributes = new HashMap<String, String>();
			attributes.put("relid", dataRecord.getTagid());
			node.setAttributes(attributes);
			nodeMap.put(node.getId(), node);
		}
                // 构造无序的多叉树
                for (Map.Entry<String, TagResult> m : nodeMap.entrySet()) {
			TagResult node = (TagResult) m.getValue();
			if (node.getParentId() == null || node.getParentId().equals("")
					|| node.getParentId().equals("0")) {
				//root = node;
				parentnodes.add(node);
			} else {
				((TagResult) nodeMap.get(node.getParentId())).getChildren().add(node);
                                //横向(同一层)排序
                                Collections.sort(((TagResult) nodeMap.get(node.getParentId())).getChildren(), new NodeIDComparator());
			}
		}
                // 对多叉树进行横向(同一层)排序
                Collections.sort(parentnodes, new NodeIDComparator());
		list.addAll(parentnodes);
		return list;

	}

	/**
	 * Comparator
	 */
	class NodeIDComparator implements Comparator {
		public int compare(Object o1, Object o2) {
			int retval = ((TagResult) o1).getAttributes().get("relid").compareTo(((TagResult) o2).getAttributes().get("relid"));
			if (retval != 0) {
				return retval;
			}
			return ((TagResult) o1).getId().compareTo(((TagResult) o2).getId());

		}
	}