Alphabetical DropDown Menu In JSP
In this section, we have developed an User Search application . We created five file adduser.jsp, addform.jsp, userUpdate.jsp and delete.jsp.
Brief description of the flow of the application:
- User opens adduser.jsp in the browser and enter the text to search the user and click on the "Search".
- We have create three buttons Insert, Delete and Update, the user information will display on adduser.jsp page.
- After click on the Insert Button user can add the new User information into the database and after click on the Update Button user can Update the User information in database.
- If user clicks on the Delete Button then application will delete the User information from database.
Step 1: Create a web page ("adduser.jsp") to Search the user.
<%@ page language="java" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "user_register";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int sumcount=0;
Statement st;
String username= "";
if(request.getParameter("username")!=null &&
request.getParameter("username")!="")
{
username = request.getParameter("username").toString();
}
%>
<HTML>
<head>
<script>
function validate(){
if(document.frm.username.value=="")
{
alert("Please enter username");
document.frm.username.focus();
return false;
}
}
function User(val){
var url = "addform.jsp";
var userid = document.getElementById("userid").value;
if(val=="Insert")
{
window.location.href="addform.jsp";
}
else if(val=="Update")
{
if(userid!="-1")
{
url = url + "?id="+userid;
window.location.href=url;
}
else
{
alert("Please select username");
}
}
else if(val=="Delete")
{
if(userid!="-1")
{
url = "delete.jsp?id="+userid;
window.location.href=url;
}
else
{
alert("Please select username");
}
}
}
</script>
</head>
<BODY>
<FORM NAME="frm" METHOD="post" ACTION=""
onsubmit="return validate();">
</BR> </BR>
<H3> <P ALIGN="CENTER"> <FONT SIZE=6> Search User
</FONT> </P> </H3> </BR>
<TABLE CELLSPACING=5 CELLPADDING=5 border=0
BGCOLOR="LIGHTBLUE" COLSPAN=2 ROWSPAN=2
ALIGN="CENTER" width="400px">
<TR>
<TD> <FONT SIZE=4>Search</TD>
<TD><INPUT TYPE="TEXT" NAME="username"
id="username" value="<%=username%>">
</FONT> </TD><td><INPUT TYPE="submit" NAME="search"
VALUE="Search"></td>
</TR>
<TR >
<td><FONT SIZE=4>Username</font></td>
<TD colspan=2 align="left"> <FONT SIZE=5 >
<select name="userid" id="userid">
<option value="-1">Select</option>
<%
if(request.getParameter("username")!=null &&
request.getParameter("username")!="")
{
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection
(url+dbName,userName,password);
String query = "select * from register
where username LIKE '"+username+"%'";
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
>
<option value="<%=rs.getInt(1)%>">
<%=rs.getString(2)%></option>
<%
}
}
catch (Exception e) {
e.printStackTrace();
}
}
%>
</select>
</TD>
</FONT> </TR>
<TR> <FONT SIZE=6>
<TD colspan=3 align="center">
<INPUT TYPE="button" NAME="Insert" VALUE="Insert"
onclick="User(this.value);"><INPUT TYPE="button" NAME="Delete" VALUE="Delete" onclick="User(this.value);">
<INPUT TYPE="button" NAME="Update" VALUE="Update" onclick="User(this.value);"> </TD> </TR> </FONT> </FORM> </BODY> </HTML>
Step:2Create a
form ("addform.jsp") to add and Update the User.
<%@ page language="java" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%
String username="";
String user_pass="";
String firstname="";
String lastname="";
String email="";
String state="";
String city="";
String country="";
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "user_register";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int sumcount=0;
Statement st;
String id="";
String bttn_value = "Add";
if(request.getParameter("id")!=null && request.getParameter("id")!="")
{
bttn_value = "Update";
id = request.getParameter("id");
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
String query = "select * from register where id='"+id+"'";
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
username=rs.getString(2);
user_pass=rs.getString(3);
firstname=rs.getString(4);
lastname=rs.getString(5);
email=rs.getString(6);
state=rs.getString(7);
city=rs.getString(8);
country=rs.getString(9);
}
}
catch (Exception e) {
e.printStackTrace();
}
}
%>
<HTML>
<head>
<script>
function validate(){
if(document.frm.username.value=="")
{
alert("Please enter username");
document.frm.username.focus();
return false;
}
if(document.frm.user_pass.value=="")
{
alert("Please enter password");
document.frm.user_pass.focus();
return false;
}
if(document.frm.firstname.value=="")
{
alert("Please enter first name");
document.frm.firstname.focus();
return false;
}
if(document.frm.lastname.value=="")
{
alert("Please enter last name");
document.frm.lastname.focus();
return false;
}
if(document.frm.email.value=="")
{
alert("Please enter email");
document.frm.email.focus();
return false;
}
if(document.frm.state.value=="")
{
alert("Please enter state");
document.frm.state.focus();
return false;
}
if(document.frm.city.value=="")
{
alert("Please enter city");
document.frm.city.focus();
return false;
}
if(document.frm.country.value=="")
{
alert("Please enter country");
document.frm.country.focus();
return false;
}
return true;
}
</script>
</head>
<BODY >
<FORM NAME="frm" METHOD="post" action=
"userUpdate.jsp" onsubmit="return validate();">
</BR>
<INPUT TYPE="hidden" NAME="id" value="<%=id%>">
<H3> <P ALIGN="CENTER"> <FONT SIZE=6>
EMPLOYEE DETAILS </FONT> </P> </H3>
<BR>
<TABLE CELLSPACING=5 CELLPADDING=5 border=0 BGCOLOR="LIGHTBLUE" COLSPAN=2
ROWSPAN=2 ALIGN="CENTER" width="400px">
<TR>
<TD> <FONT SIZE=3><b>Username</b></TD>
<TD><INPUT TYPE="TEXT" NAME="username" id="username" value="<%=username%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>Password</b></TD>
<TD><INPUT TYPE="password" NAME="user_pass" id="user_pass" value="<%=user_pass%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>First Name</b></TD>
<TD><INPUT TYPE="TEXT" NAME="firstname" id="firstname" value="<%=firstname%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>Last Name</b></TD>
<TD><INPUT TYPE="TEXT" NAME="lastname" id="lastname" value="<%=lastname%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>Email</b></TD>
<TD><INPUT TYPE="TEXT" NAME="email"
id="email" value="<%=email%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>State</b></TD>
<TD><INPUT TYPE="TEXT" NAME="state"
id="state" value="<%=state%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>City</b></TD>
<TD><INPUT TYPE="TEXT" NAME="city"
id="city" value="<%=city%>">
</FONT> </TD>
</TR>
<TR>
<TD> <FONT SIZE=3><b>Country</b></TD>
<TD><INPUT TYPE="TEXT" NAME="country"
id="country" value="<%=country%>">
</FONT> </TD>
</TR>
<TR> <FONT SIZE=6>
<TD colspan=3 align="center"> <INPUT TYPE=
"Submit" NAME="submit" VALUE="<%=bttn_value%>">
</TD>
</TR> </FONT>
</FORM>
</BODY>
</HTML>
Step 3: "userUpdate.jsp" to add and Update the details of selected user.
<%@ page import="java.sql.*" %>
<%
String username = request.getParameter("username").toString();
String bttn = request.getParameter("submit").toString();
String user_pass=request.getParameter("user_pass").toString();
String firstname=request.getParameter("firstname").toString();
String lastname=request.getParameter("lastname").toString();
String email=request.getParameter("email").toString();
String state=request.getParameter("state").toString();
String city=request.getParameter("city").toString();
String country=request.getParameter("country").toString();
ResultSet rs_update = null;
int count_update=0;
int count=0;
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "user_register";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int id = 0;
int sumcount=0;
Statement st;
String query_update="";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
String query = "select count(*) from register where
username='"+username+"'";
st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next()){
count = rs.getInt(1);
}
if(bttn.equals("Add") && count==0){
query = "insert into register set username='"+username+"',
password='"+user_pass+"',
firstname='"+firstname+"',lastname='"+lastname+"',email='"
+email+"',state='"+state+"',
city='"+city+"',country='"+country+"'";
st= conn.createStatement();
}
else if(bttn.equals("Update")){
id = Integer.parseInt(request.getParameter("id").toString());
query_update = "select count(*) from register where
username='"+username+"' and id!='"+id+"'";
// out.println(query_update);
st = conn.createStatement();
rs_update = st.executeQuery(query_update);
while(rs_update.next())
{
count_update = rs_update.getInt(1);
//out.println(rs_update.getInt(1));
}
// out.println(count_update);
if(count_update==0)
{
query = "update register set username='"+username+"',
password='"+user_pass+"',
firstname='"+firstname+"',lastname='"+lastname+"',
email='"+email+"',state='"+state+"',
city='"+city+"',country='"+country+"' where id="+id;
}else
{
response.sendRedirect("addform.jsp?id="+id);
}
}
else
{
response.sendRedirect("addform.jsp");
}
int i = st.executeUpdate(query);
if(i>0)
{
response.sendRedirect("adduser.jsp");
}
}
catch (Exception e) {
e.printStackTrace();
}
%>
Step 4:Create a web page
(delete.jsp) to delete the user.
<%@ page import="java.sql.*" %>
<%
int id = 0;
if(request.getParameter("id")!=null){
id = Integer.parseInt(request.getParameter("id").toString());
int count=0;
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "user_register";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
int sumcount=0;
Statement st;
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
String query = "delete from register where id="+id;
st = conn.createStatement();
int i = st.executeUpdate(query);
if(i>0)
{
response.sendRedirect("adduser.jsp");
}
}
catch (Exception e) {
e.printStackTrace();
}
}
%>
Successful Output of the program:
To Add new User:
When the user click on the Insert button.
To Update the User:
When the user click on the Update button.
Download the full web application shows here.