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.

In this section, we have developed an User Search application . We created five file adduser.jsp, addform.jsp, userUpdate.jsp and delete.jsp.

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: