[mysql] How to fetch the dropdown values from database and display in jsp

I have two dropdowns in jsp and have to get dropdown list from database and show it in jsp. I am using jsp for the first time . Can you give me an idea to fetch the dropdown list from database and display the values in jsp dropdown element.Thanks in advance

I made this in my code to do that

note: I am a beginner.

It is my jsp code.

<%
java.sql.Connection Conn = DBconnector.SetDBConnection(); /* make connector as you make in your code */
Statement st = null;
ResultSet rs = null;
st = Conn.createStatement();
rs = st.executeQuery("select * from department"); %>
<tr> 
    <td> 
        Student Major  : <select name ="Major">
        <%while(rs.next()){ %>
        <option value="<%=rs.getString(1)%>"><%=rs.getString(1)%></option>
                        <%}%>           
                         </select> 
   </td> 

You can learn some tutorials for JSP page direct access database (mysql) here

Notes:

• import sql tag library in jsp page

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

• then set datasource on page

  <sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://<yourhost>/<yourdb>" user="<user>" password="<password>"/>
  

• Now query what you want on page

  <sql:query dataSource="${ds}" var="result"> //ref  defined 'ds'
      SELECT * from <your-table>;
  </sql:query>
  

• Finally you can populate dropdowns on page using c:forEach tag to iterate result rows in select element

  <c:forEach var="row" items="${result.rows}"> //ref set var 'result' <option value='<c:out value="${row.key}"/>'><c:out value="${row.value}"/</option> </c:forEach>

1. Make the database connection and retrieve the query result.
2. Traverse through the result and display the query results.

The example code below demonstrates this in detail.

<%@page import="java.sql.*, java.io.*,listresult"%> //import the required library

<%

String label = request.getParameter("label"); // retrieving a variable from a previous page

Connection dbc = null; //Make connection to the database
Class.forName("com.mysql.jdbc.Driver");
dbc = DriverManager.getConnection("jdbc:mysql://localhost:3306/works", "root", "root");
if (dbc != null) 
{
    System.out.println("Connection successful");
}

ResultSet rs = listresult.dbresult.func(dbc, label); //This function is in the end. The function is defined in another package- listresult

%>

<form name="demo form" method="post">

    <table>
        <tr>
            <td>
                Label Name:
            </td>

            <td>
                <input type="text" name="label" value="<%=rs.getString("labelname")%>">
            </td>

            <td>
                <select name="label">
                <option value="">SELECT</option>

                <% while (rs.next()) {%>

                    <option value="<%=rs.getString("lname")%>"><%=rs.getString("lname")%>
                    </option>

                <%}%>
                </select>
            </td>
        </tr>
    </table>

</form>

//The function:

public static ResultSet func(Connection dbc, String x)
{
    ResultSet rs = null;
    String sql;
    PreparedStatement pst;
    try
    {
        sql = "select lname from demo where label like '" + x + "'";
        pst = dbc.prepareStatement(sql);
        rs = pst.executeQuery();
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
        String sqlMessage = e.getMessage();
    }
    return rs;
}

I have tried to make this example as detailed as possible. Do ask if you have any queries.