fetching the data from data base by using jsp

Question

how to fetch the states from the the states table in mssql server db by using jsp as a drop down box

Answer 1

Add these imprts to the page

<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql_rt" %>

Then add an sql query similar to this

<sql:query var="stateList" dataSource="your dataSource">
select state
from statetable
</sql:query>

then use the tags to create the list

<select name="stateNames" id="stateNames" style="width:160px;height:125px">
<option value="ALL" selected="selected">ALL States</option>
  <c:forEach items="${stateList.rows}" var="row">    
    <option value="${row.state}">${row.state}</option>
      </c:forEach>
</select>

Answer 2

To start, create a javabean representing a state with code and name (basically the same data as you have in the DB table). Then create a DAO class which does the database interaction task. Then create a Servlet class which retrieves the list from the DAO, puts it in the request scope and forwards the request to the JSP. Finally create a JSP file which displays the list using JSTL's c:forEach.

Basic example of the state bean:

public class State {
    private String code;
    private String name;
    // Add (generate) public getters and setters.
}

Basic example of the DAO class:

public List<State> list() {
    List<State> states = new ArrayList<State>();
    // Do your JDBC thing here.
    return states;
}

Basic example of the Servlet class:

protected void doGet(HttpServletRequest request, HttpServletResponse response) {
    List<State> states = stateDAO.list();
    request.setAttribute("states", states);
    request.getRequestDispatcher("/WEB-INF/page.jsp").forward(request, response);
}

Basic example of JSP file:

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<select name="state">
    <c:forEach items="${states}" var="state">
        <option value="${state.code}">${state.name}</option>
    </c:forEach>
</select>

Map the servlet on the url-pattern to your taste in web.xml and invoke this servlet by your browser.

To get more insights and ideas around the DAO pattern, you may find this article useful as well.