需求

在看到之前的CSDN所有有关级联查询后，到只有部分到sql，并无所谓的sql之后的操作，下面我提供一个完整版的案例。我这有一个表，里面只有id,categoryCode,categoryName,superId这么几个字段，其中superId:父极id 他是和和id是有关联的

如图

解决方法

先查找级别为1也就是说superid为null的1级别目录

sql 语句

select distinct b1.category_name as categoryName,b1.category_code as categoryCode,b1.id,b1.level as levels from category as b1 , category as b2 ,category AS b3
where b1.id=b2.super_category_id
and b2.id=b3.super_category_id where b1.categorylevel=1

得到结果

再查找级别为2也就是说superid为1的2级别目录

sql:SELECT distinct b2.category_name AS categoryName,b2.category_code as categoryCode,b2.id,b2.level as levels FROM category AS b1 , category AS b2 ,category AS b3
WHERE b1.id=b2.super_category_id
AND b2.id=b3.super_category_id
AND b1.superId =1 （1就是这些二级目录的父级id）

得到结果

最后查询最后一级儿子级别的目录（举例父级别为2)

sql:SELECT DISTINCT b3.super_category_id,b3.category_name AS categoryName,b3.id,b3.category_level AS levels,b3.category_code AS categoryCode FROM category AS b1 , category AS b2 ,category AS b3
WHERE b1.id=b2.super_category_id
AND b2.id=b3.super_category_id
AND b3.super_category_id=2

3级目录结果

定义1级别目录代码

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CategoryDTOLeve1 {
    private String categoryName;
    private String categoryCode;
    private BigInteger id;
    private List<CategoryDTOLeve2> children = Lists.newArrayList();
};

定义2级别目录代码

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CategoryDTOLeve2 {
    private String categoryName;
    private String categoryCode;
    private BigInteger id;
    private List<CategoryDTOLeve3> children = Lists.newArrayList();
};

定义3级别目录代码

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CategoryDTOLeve3 {
    private String categoryName;
    private String categoryCode;
    private BigInteger id;
 
};

service

  List<CategoryDTO> cateGory = brandDao.getCateGory(at.getBasicTable());
            for (CategoryDTO categoryDTO : cateGory) {
                CategoryDTOLeve1 c1 = new CategoryDTOLeve1();
                List<CategoryDTOLeve2> categoryDTOLeve2s = c1.getChildren();
                List<CategoryDTO> categoryByLeve2 = brandDao.getCategoryByLeve2(at.getBasicTable(), categoryDTO.getId());
                for (CategoryDTO categoryDTO2 : categoryByLeve2) {
                    CategoryDTOLeve2 c2 = new CategoryDTOLeve2();

                    List<CategoryDTOLeve3> categoryDTOLeve3s = c2.getChildren();
                    List<CategoryDTO> categoryByLeve3 = brandDao.getCategoryByLeve3(at.getBasicTable(), categoryDTO2.getId());
                    for (CategoryDTO categoryDTO3 : categoryByLeve3) {
                        CategoryDTOLeve3 c3 = new CategoryDTOLeve3();
                        c3.setId(categoryDTO3.getId());
                        c3.setCategoryCode(categoryDTO3.getCategoryCode());
                        c3.setCategoryName(categoryDTO3.getCategoryName());
                        categoryDTOLeve3s.add(c3);
                    }
                    c2.setId(categoryDTO2.getId());
                    c2.setCategoryCode(categoryDTO2.getCategoryCode());
                    c2.setCategoryName(categoryDTO2.getCategoryName());
                    categoryDTOLeve2s.add(c2);
                }

                c1.setId(categoryDTO.getId());
                c1.setCategoryCode(categoryDTO.getCategoryCode());
                c1.setCategoryName(categoryDTO.getCategoryName());
                categoryListDTOS.add(c1);
            }

 
};

Dao

   public List<CategoryDTO> getCateGory(String basicTable) {


        String sql = " select distinct b1.category_name as categoryName , b1.category_code as categoryCode,b1.id as id from "+basicTable+" as b1 , "+basicTable+" as b2 ,"+basicTable+" AS b3\n" +
                " where b1.id=b2.super_category_id  \n" +
                " and  b2.id=b3.super_category_id ";
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(CategoryDTO.class));
        List<CategoryDTO> categoryDTOList= query.getResultList();


        return categoryDTOList;

    }

    public List<CategoryDTO> getCategoryByLeve2(String basicTable,BigInteger id) {

        String sql = "SELECT distinct b2.category_name  AS categoryName,b2.category_code as categoryCode,b2.id  as id FROM "+basicTable+" AS b1 , "+basicTable+" AS b2 ,"+basicTable+" AS b3\n" +
                "WHERE b1.id=b2.super_category_id  \n" +
                "AND  b2.id=b3.super_category_id  \n" +
                "AND b1.id="+id;
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(CategoryDTO.class));
        List<CategoryDTO> categoryDTOList  = query.getResultList();


        return categoryDTOList;

    }

    public List<CategoryDTO> getCategoryByLeve3(String basicTable,BigInteger id) {

        String sql = "SELECT distinct b3.category_name  AS categoryName,b3.category_code as categoryCode,b3.id as id FROM "+basicTable+" AS b1 , "+basicTable+" AS b2 ,"+basicTable+" AS b3\n" +
                " WHERE b1.id=b2.super_category_id  \n" +
                " AND  b2.id=b3.super_category_id  \n" +
                " and b2.id="+id;
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(CategoryDTO.class));
        List<CategoryDTO> categoryDTOList  = query.getResultList();




        return categoryDTOList;
    }

返回Json

"categoryDTOS": [
      {
        "categoryName": "家用电器",
        "categoryCode": "",
        "id": 1,
        "children": [
          {
            "categoryName": "生活电器",
            "categoryCode": "",
            "id": 2,
            "children": [
              {
                "categoryName": "空气净化器",
                "categoryCode": "",
                "id": 13
              }
           ]
             }
         ]
}
 
};