JSP Get Data Into Dropdown list From Database
In this section we will discuss about how to fetch data dynamically into the dropdown list in JSP.
This tutorial explains you that how to fetch data from database and set it into the dropdown list dynamically. In this example we will explain the dynamically fetching of data into the dropdown list in JSP using Eclipse IDE and the Tomcat 7 server.
For creating a Dropdown list there is a tag in HTML given below that you can embed it into your JSP page.
<select> <option value=""> </option> <option value=""> </option> </select>
Size of the options is controlled by the browser and if you want to limit the size of list to be shown you can use the 'size' attribute of <select> tag.
Example
An example is being given below for fetching data from the database and set it into the dropdown list in JSP using MySQL. To accomplish this task we will first create a database table in MySQL and then we will make a jsp page where we will write the code for making a connection with database and the code for executing sql query, then we will write the code for getting the value of resultset and then for set it into the <option> tag.
Database table
CREATE TABLE `users` ( `userid` int(5) NOT NULL, `firstname` varchar(20) DEFAULT NULL, `lastname` varchar(20) DEFAULT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Source Code
getDataIntoDropDownlist.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*;" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Drop Downlist</title>
</head>
<body>
<%! String driverName = "com.mysql.jdbc.Driver";%>
<%!String url = "jdbc:mysql://localhost:3306/record";%>
<%!String user = "root";%>
<%!String psw = "root";%>
<form action="#">
<%
Connection con = null;
PreparedStatement ps = null;
try
{
Class.forName(driverName);
con = DriverManager.getConnection(url,user,psw);
String sql = "SELECT * FROM users";
ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
%>
<p>Select Name :
<select>
<%
while(rs.next())
{
String fname = rs.getString("firstname");
%>
<option value="<%=fname %>"><%=fname %></option>
<%
}
%>
</select>
</p>
<%
}
catch(SQLException sqe)
{
out.println(sqe);
}
%>
</form>
</body>
</html>
web.xml
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>jspDeleteData</display-name> <welcome-file-list> <welcome-file>getDataIntoDropDownlist.jsp</welcome-file> </welcome-file-list> </web-app>
Output
Data in the table is as follows :
When you will execute the above example you will get the output as follows :
you can download the above example as in WAR file from the link given below :