How to get all sub categories from database in java

Question

I am having a tree kind structure in my database like below.

mysql> select * from categories;
+----+---------------+-----------+
| id | category_name | parent_id |
+----+---------------+-----------+
|  1 | footwear      |        -1 |
|  2 | kids footwear |         1 |
|  3 | for girls     |         2 |
|  4 | sandals       |         3 |
|  5 | electronics   |        -1 |
|  6 | kids          |        -1 |
+----+---------------+-----------+
6 rows in set (0.00 sec)

i want to read all the sub categories of a parent category,-1 in the table shows the category as parent or upper most category.There might be any level in the tree.

As soon as users click on the parent category (-1),i need to display all of its subcategories.i don't want to hit database again again on each & every click. I am having some idea like this might be done by converting the table entries into tree & than traverse the tree but it is getting too much complex in face retrieving from the table itself a difficult task.

I have got some idea from this link to do it another way (2nd Method) http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ but in this method if i want to insert a new sub-category than i have to modify the whole table.

Any other suggestions please ?

Answer 1

you have to use recursive function to do this kind of problem

    public String findcategoryList() {
    List<Category> results = dao.findAllCategory();
    LinkedHashMap<String, List<String>> map = new LinkedHashMap();
    LinkedHashMap<Integer, List<Integer>> mapKey = new LinkedHashMap();
    for (Object object : results) {
        Category ee = (Category) object;
        Set<Category> stsd = ee.getSubCategories();

        if (stsd != null && stsd.size() > 0 && ee.getCategory() == null) {
            dispaly(stsd, ee, map, mapKey);
        } else if (ee.getCategory() == null) {
            List<String> list = new ArrayList<String>();
            List<Integer> listKey = new ArrayList<Integer>();
            map.put(ee.getCategoryname(), list);
            mapKey.put(ee.getCategoryId(), listKey);
            System.out.println(ee.getCategoryname() + "---- No Child");
            System.out.println(ee.getCategoryId() + "---- No Child");
        }
    }

    StringBuilder menu = new StringBuilder();
    System.out.println("------------------Menu Start----------------");
    Set<String> keys = new TreeSet<String>(map.keySet());
    menu.append("<ul class='drop'>");

    Set<String> sets = new HashSet<String>();
    if(map.size() == mapKey.size()){
        Iterator<Entry<String, List<String>>> iter1 = map.entrySet().iterator();
        Iterator<Entry<Integer, List<Integer>>> iter2 = mapKey.entrySet().iterator();
        while(iter1.hasNext() || iter2.hasNext()) {
              Entry<String, List<String>> e1 = iter1.next();
              Entry<Integer, List<Integer>> e2 = iter2.next();
              String key = e1.getKey();
              List<String> child = map.get(key);
              Integer keyId = e2.getKey();
              List<Integer> childKey = mapKey.get(keyId);

        if (!sets.contains(key)) {
            System.out.println("key : "+key);
            if (child != null && child.size() > 0) {
                menu.append("<li><a href='#'>").append(key).append("<i class='fa fa-caret-down'></i></a>");
                menu.append("\n");
                menu.append("<ul class='sub_menu submenu'>");
                menu.append("\n");
            }else{
                menu.append("<li><a href='./viewVideos-"+keyId+"'>").append(key).append("<i class='fa fa-caret-down'></i></a>");
                menu.append("\n");
            }
            sets.add(key);
            menuCreate(menu, child, childKey, map, mapKey, sets);
            if (child != null && child.size() > 0) {
                menu.append("</ul>");
                menu.append("\n");
            }
             menu.append("</li>");
        }
    }
        }
    menu.append("</ul>");
    //System.out.println(menu.toString());
    return menu.toString();
}

    public static void menuCreate(StringBuilder menu, List<String> child, List<Integer> childKey, Map<String, List<String>> map,
        Map<Integer, List<Integer>> mapKey,
        Set<String> sets) {
    if (child != null && child.size() > 0) {
        // Collections.sort(child, ComparatorUtils.NATURAL_COMPARATOR);
        for (int i=0;i<child.size();i++) {
            List<String> subChild = map.get(child.get(i));
            List<Integer> subChildKey = mapKey.get(childKey.get(i));
            System.out.println("subChild : "+subChild+ " child : " + child + " Value : " + child.get(i));
            menu.append("<li>");
            boolean flag = false;
            if (subChild == null || (child != null && child.size() <= 0)) {
                menu.append("<a href='./viewVideos-"+childKey.get(i)+"'>");
                flag = true;
            }
            if(flag){
                menu.append(child.get(i)).append("<i class='fa fa-caret-right'></i>");
                menu.append("</a>");
            }else{
                menu.append("<a href='#'>").append(child.get(i)).append("<i class='fa fa-caret-right'></i></a>");
            }
            menu.append("\n");
            if (subChild != null && subChild.size() > 0) {
                if (!sets.contains(child.get(i))) {
                    sets.add(child.get(i));
                    menu.append("<ul class='sub_menu submenu'>");
                    menu.append("\n");
                    menuCreate(menu, subChild, subChildKey, map, mapKey, sets);
                    menu.append("</ul>");
                    menu.append("\n");
                }
            }
            menu.append("</li>");
        }
    }
}

    public void dispaly(Set<Category> msp, Category ee, Map<String, List<String>> map, Map<Integer, List<Integer>> mapKey) {
    for (Category category : msp) {
        Set<Category> stsd = category.getSubCategories();
        if (category.getCategory() != null) {
            if (map.get(category.getCategory().getCategoryname()) == null) {
                List<String> list = new ArrayList<String>();
                List<Integer> listKey = new ArrayList<Integer>();
                list.add(category.getCategoryname());
                listKey.add(category.getCategoryId());
                map.put(category.getCategory().getCategoryname(), list);
                mapKey.put(category.getCategory().getCategoryId(), listKey);
            } else {
                List<String> list = map.get(category.getCategory().getCategoryname());
                List<Integer> listKey = mapKey.get(category.getCategory().getCategoryId());
                list.add(category.getCategoryname());
                listKey.add(category.getCategoryId());
                map.put(category.getCategory().getCategoryname(), list);
                mapKey.put(category.getCategory().getCategoryId(), listKey);
            }

            System.out.println(category.getCategory().getCategoryname() + "----" + category.getCategoryname());
            System.out.println(category.getCategory().getCategoryId() + "----" + category.getCategoryId());
        }
        if (stsd != null && stsd.size() > 0) {
            dispaly(stsd, category, map, mapKey);
        }
    }

menu.toString() will give you tree bar in html format.

Answer 2

AFAIK there are only a couple of feasible methods:

• Retrieve the levels in a breadth first manner, e.g. by executing a query several times. This query might retrieve multiple levels at once. This is what we do for shallow trees.
• Use the materialized path / nested sets aproach you linked.
• Read in all rows and build the tree in code, which might be the easiest aproach and would still be feasible for small trees. For large trees you might consider some caching mechanism.

Another aproach might be to use a different database ;)