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());
}
}