Forum:

Show menu and submenu on homepage from database using servlet and jsp

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi All,
I want subcategory dropdown menu on my home page.I am using jsp and servlet.database is mysql. tables are as below
1.tbl_category(category_id(pk),category_name)
2.tbl_subcategory(subcategory_id(pk),category_id(fk),subcategory_name)
There is a servlet for fetch record. code is as below

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class Category extends HttpServlet {

/**
*/
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet Category</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet Category at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }catch(ClassNotFoundException cne){
        } finally {            
            out.close();
        }
    }

@Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
        ArrayList list=new ArrayList();
        try{
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=shilpa&password=password");
            String query="select category_id,category_name from tbl_category";
            Statement st1=con.createStatement();
            ResultSet rs=st1.executeQuery(query);
            while (rs.next()) {
                //list.add(rs.getInt("category_id"));
                list.add(rs.getString("category_name"));          
             }
            request.setAttribute("data",list);
          RequestDispatcher rd=request.getRequestDispatcher("showcategory.jsp");
          rd.forward(request, response);
        processRequest(request, response);
        }catch(Exception e){
            
        }
    }

@Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

/**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

This can fetch records and display with the help of jsp

output is as below
Home Sports Kitchen Furniture

The problem is that It shows only menus ont the submenu what can I do for that?
Thank you in adv.

Palak Mathur

Ranch Hand

Posts: 343

I like...

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Why do you think it would show submenu? Where are you trying to display that? The code is showing what you are doing in the code. If you want to display the submenu, try to implement that part.

Palak Mathur | What is JavaRanch? | List of All FAQs |
My LinkedIn Profile

shilpa deshmukh-kadam

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Palak Mathur wrote:Why do you think it would show submenu? Where are you trying to display that? The code is showing what you are doing in the code. If you want to display the submenu, try to implement that part.

Actually sir,
This is working code which shows output as

Home Sports Kitchen Furniture
Which are store in my table data but the output which I want is dropdown submenus over above menu for that I tried following code too

Servlet code

@Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out=response.getWriter();
        ArrayList sublist=new ArrayList();
        ArrayList list1=new ArrayList();
        try{
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=shilpa&password=password");
            String query="select subcategory_name from tbl_subcategory where category_id='3'";
            Statement st1=con.createStatement();
            ResultSet rs=st1.executeQuery(query);
            while (rs.next()) {
                //list.add(rs.getInt("category_id"));
                sublist.add(rs.getString("subcategory_name"));          
             }
            
            String query1="select category_name from tbl_category";
            Statement st2=con.createStatement();
            ResultSet rs1=st2.executeQuery(query1);
            while (rs1.next()) {             
                list1.add(rs1.getString("category_name"));          
             }
            request.setAttribute("data1",list1);
            request.setAttribute("subdata",sublist);
          RequestDispatcher rd=request.getRequestDispatcher("logic.jsp");
          rd.forward(request, response);
        processRequest(request, response);
        }catch(Exception e){
            
        }
    }

and jsp code is as below

and css code is ---------

Now no error is shown it shows output 80% how I want that means it shows categories as mentioned above, subcategories on mouse over but in case of subcategory It shows subcategory of category id='3' where I want each related subcategory of related parent category.
I know the mistake in my query but don't know which query I have to fired...?

Palak Mathur

Ranch Hand

Posts: 343

I like...

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Check following line of your doGet() method:-

So here you are fetching the subcategory_name from table tbl_subcategory for the category_id = 3 only. In place of this you should pass the category id corresponding to each category.

Palak Mathur | What is JavaRanch? | List of All FAQs |
My LinkedIn Profile

shilpa deshmukh-kadam

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

This is code which display subcategory_name of category id 3. but I have different-different categories and those having more subcategories so is there any option like as loop to get subcategory_name . I mean to say as category_id=i;
i++; just asking if I expected funny Then please guide me.

If It's wrong So..
Do I write different jsp for each category_id ...?

Palak Mathur

Ranch Hand

Posts: 343

I like...

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Why will you use a loop? I think you will like to use a join while querying. Fetch the category_id from tbl_category and use it to fetch data from tbl_subcategory.

Your query would look like this (you will have to modify to optimize this query following the rules of using a join):

Palak Mathur | What is JavaRanch? | List of All FAQs |
My LinkedIn Profile

shilpa deshmukh-kadam

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I tried that like as below

The show.jsp contains following code

Which shows following output

•Sports
◦Table Tennis
•Sports
◦Pool Cues
•Sports
◦Foot-Ball
•Exercise and Fitness
◦Yoga
•Exercise and Fitness
◦Bars

and so on....here categories too repeated which I don't want.

help me..?

Palak Mathur

Ranch Hand

Posts: 343

I like...

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

When you are adding to the list, what you are doing is you are adding category_name as it comes in the RS. Do not add category name and subcategory name to the same list. I would suggest that you use a Map with category name as the key and list of subcategoryname as value. Display the key and corresponding subcategory list.

Palak Mathur | What is JavaRanch? | List of All FAQs |
My LinkedIn Profile

shilpa deshmukh-kadam

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

can you give me example...?
I don't know about how to use map...
Even I didn't know about Iterator class while doing code I search on google and make various code snippet. Iterator, Array are the examples which gives me output .
So would you give me an example or give me just syntax I will try..Please suggest me reference book for this topic.
Thank you

Palak Mathur

Ranch Hand

Posts: 343

I like...

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

shilpa deshmukh-kadam wrote:can you give me example...?
I don't know about how to use map...
Even I didn't know about Iterator class while doing code I search on google and make various code snippet. Iterator, Array are the examples which gives me output .
So would you give me an example or give me just syntax I will try..Please suggest me reference book for this topic.
Thank you

Refer this tutorial: http://docs.oracle.com/javase/tutorial/collections/interfaces/map.html

Palak Mathur | What is JavaRanch? | List of All FAQs |
My LinkedIn Profile

shilpa deshmukh-kadam

Ranch Hand

Posts: 38

posted 10 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thank you :)