retrieve dropdown list from mysql database and insert to database in jsp

Question

This thing is really bothering me. How can I get my dropdown list from MySQL database and then submit it to another table in JSP. I only know how to create a static dropdown with html and but how can I make it dynamic. I am thinking of a form that links to a servlet and the servlet connects to the database and fetches an array of strings from a database table and then sends it to the JSP to populate the options and when an option is submitted, it send to a servlet which then inserts to the database. someone please give me some sample code that can do this. Most specifically I need the code of the JSP used in the tag and the code for sending from the servlet. I've really checked with google but there is no clear answer. Hope I get an answer here

Answer 1

You've it almost right. To get the dropdown values from a database you should first call the servlet which does the preprocessing job and then let the servlet display the JSP with the dropdown.

Since a normal HTTP request (clicking a link, a bookmark or entering the URL in address bar) fires per definition a GET request, you'd like to do the job in the doGet() method. Here's an example which gets the dropdown values in flavor of a List<Product>.

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    List<Product> products = productService.list();
    request.setAttribute("products", products); // It'll be available as ${products}.
    request.getRequestDispatcher("/WEB-INF/products.jsp").forward(request, response);
}

In /WEB-INF/products.jsp you can display it as follows:

<form action="order" method="post">
    <select name="productId">
        <c:forEach items="${products}" var="product">
            <option value="${product.id}">${product.name}</option>
        </c:forEach>
    </select>
    <input type="submit" value="Order" />
</form>

Map this servlet on an URL pattern of /products and invoke it by http://example.com/context/products. It'll load the products from the DB, store it in the request scope, forward to the JSP to let it present it.

When you submit the form, then the doPost() method of a servlet which is mapped on an URL pattern of /order will be invoked. When you submit a HTML form, then the name=value pair of every input element will be available as a HTTP request parameter. So you can get the product ID as follows:

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String productId = request.getParameter("productId");
    // ... do your job here to insert the data.

    request.getRequestDispatcher("/WEB-INF/someresult.jsp").forward(request, response);
}

See also:

• Our JSP wiki page
• Our Servlets wiki page