Jsf2 primefaces menubar from database

Question

I am new on Java. I am generating a menubar from database table, i am able to generate three levels submenus but i was wondering what if one day we decided to add a level.I know that my logic for the menu generation is bad but I can't find the logic to generate the childs on primefaces. Here's the sql result of my query.Can anyone help for getting the solution? Or any idea for creating a loop instead of repeating the 2 queries? Any tutorial would be really appreciated or any source code example.

Here's my source code for generating 3 levels menu

connexion c1 = new connexion();
    Connection con = c1.getConnected();

    PreparedStatement ps = con
            .prepareStatement("SELECT * FROM menu_options where parent_option_id is null");

    // get first level menu options from database
    ResultSet result = ps.executeQuery();

    while (result.next()) {
        DefaultSubMenu firstSubmenu = new DefaultSubMenu(
                result.getString("name"));


        PreparedStatement ps2 = con
                .prepareStatement("SELECT * FROM menu_options where parent_option_id="
                        + result.getInt("id"));

        // get menu second level options from database
        ResultSet result2 = ps2.executeQuery();
        DefaultMenuItem item = null;

        while (result2.next()) {

            PreparedStatement ps3 = con
                    .prepareStatement("SELECT * FROM menu_options where parent_option_id="
                            + result2.getInt("id"));

            // get menu third level options from database
            ResultSet result3 = ps3.executeQuery();
            int rowcount = 0;
            if (result3.last()) {
                rowcount = result3.getRow();
                result3.beforeFirst();
            }

            if (rowcount == 0) {
                item = new DefaultMenuItem(result2.getString("name"));
                item.setUrl(result2.getString("url"));
                item.setIcon("ui-icon-arrowreturnthick-1-e");
                // item.setId("fils");
                firstSubmenu.addElement(item);

            } else {
                DefaultSubMenu firstSubmenu2 = null;
                firstSubmenu2 = new     DefaultSubMenu(result2.getString("name"));
                while (result3.next()) {

                    item = new DefaultMenuItem(result3.getString("name"));
                    item.setUrl(result3.getString("url"));
                    item.setIcon("ui-icon-arrowreturnthick-1-e");
                    // item.setId("fils");
                    firstSubmenu2.addElement(item);

                }
                firstSubmenu.addElement(firstSubmenu2);
            }
        }
        menu.addElement(firstSubmenu);
    }

Answer 1

A possible solution might be implementing theses methods:

// Returns the item with no parent
private List<Item> searchParentlessItems();

// Returns the item with the given item as parent
private List<Item> searchItemChildren(Item item);

// Returns the children count
private Long countItemChildren(Item item);

// Build a SubMenu for the specified item
private SubMenu createSubMenu(Item item);

// Build a MenuItem for the specified item
private MenuItem createMenuItem(Item item);

Then generation might look like

private MenuModel createMenuModel() {
    MenuModel model = new DefaultMenuModel();
    List<Item> rootItems = searchParentlessItems();
    for (Item item : rootItems) {
        boolean hasChildren = countItemChildren(item) > 0;
        if (hasChildren) {
            SubMenu subMenu = createSubMenu(item);
            model.addElement(subMenu);
            appendChildren(subMenu, item);
        } else {
            MenuItem menuItem = createMenuItem(item);
            model.addElement(menuItem);
        }
    }
}

private void appendChildren(MenuGroup parentMenuItem, Item parentItem) {
    List<Item> children = searchItemChildren(parentItem);
    for (Item child : children) {
        boolean hasChildren = countItemChildren(child) > 0;
        if (hasChildren) {
            SubMenu subMenu = createSubMenu(child);
            parentMenuItem.addElement(subMenu);
            appendChildren(subMenu, child);
        } else {
            MenuItem menuItem = createMenuItem(child);
            parentMenuItem.addElement(menuItem);
        }
    }
}