查询属性结构数据

1. Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾，在MySQL的目前版本中还没有对应的功能。 在MySQL中如果是有限的层次，比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4，则我们可以直接通过left join 来实现。

例如：

sql = "select organ,totalSum,parentId,yybmc,showindex from (" +
"select yybdm as organ,nvl(totalSum,0) as totalSum,ldepat as parentId,yybmc as yybmc,showindex from YYBDM left JOIN (select yybdm as organ,to_char(sum(nvl(txtTotalSum1,0))+sum(nvl(txtTotalSum2,0))+sum(nvl(txtTotalSum3,0))+sum(nvl(txtTotalSum4,0))+sum(nvl(txtTotalSum5,0))) as totalSum from YYBDM  LEFT JOIN appofcsupp  on ddlorgan = yybdm"
+ " group by yybdm) on yybdm = organ  where dvaldflg = '1'"
+ ") temp connect by prior organ=parentid start with parentid ='-1' ORDER siblings BY showindex";

2. mysql 目前没有类似的功能，所以只能借助其他方法实现

①java 方法

private void findChildList(AssetType parent,List<AssetType> list){
        String hql = "from AssetType a where a.parentAssetType.assetTypeId=? ORDER BY a.sort,a.assetTypeName asc";
        
        List<AssetType> childList = this.assetTypeDao
                                    .getEntityManager()
                                    .createQuery(hql)
                                    .setParameter(1, parent.getAssetTypeId())
                                    .getResultList();
        int size = childList.size();
        if(size>0){
            for (int i = 0; i < size; i++) {
                AssetType assetType = childList.get(i);
                List<AssetType> childs = assetType.getChildAssetType();
                if(childs.size()>0){
                    list.addAll(childs);
                    this.findChildList(assetType, list);//递归查询节点的子节点
                }
            }
        }
    }