树形结构的sql语句

1：进行数据查询

SELECT DATAKEY id,

AGENCYID agencyId,
CTLG_NAME name,
PID pId,
CTLG_CODE code FROM FA_B_ASSCTLG START WITH pid = '#'

CONNECT BY PRIOR DATAKEY = pid ORDER SIBLINGS BY code

2：拼接树形结构的数据格式

             //通过单位id获取资产目录树
List<CommonTreeModel> assetlist = assetinfoservice.getassetTreedata(agencyId);
List<String> strList = new ArrayList<String>();
//将获取的资产目录树按着Ztree的结构进行拼接
strList.add("{id:'#',name:'资产目录',open:true}");
if (assetlist.size() > 0 && assetlist != null) {
for (CommonTreeModel ctm : assetlist) {
strList.add("{\"id\":\"" + ctm.getId() + "\",\"pId\":\""
+ ctm.getpId() + "\",code:\"" + ctm.getCode()+ "\",agencyId:\"" + ctm.getAgencyId()
+ "\",name:\"" + ctm.getName() + "\"}");
    }
       }
JSONArray jsonarr = JSONArray.fromObject(strList);

3：输出到前台进行解析。