Search records using Servlet Jsp mysql

This tutorial will teach you how to make search using Servlet Jsp mysql step by step.we have created the file index.jsp

After run the page you will see the above sceenshot page. when the page is loaded all the city data will be retrieved from the database and pass to the dropdownbox. you have to select the city from the dropdown according to the result will be displayed on the servlett.jsp page.Paste the code inside the index.jsp

<%@page import="java.io.PrintWriter"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
        <title>TODO supply a title</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
        <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
        
        
    </head>
    <body>
        <div class="container">
            
            
                    <div class="form-group">
                        <h2>Find City</h2>
                    </div>
                <br>
                <div class="form-group">
            <form method="get" action="servlett">     
                      <div class="form-group">
                          <h3>Select the City</h3>
       <%
            Connection con;
            PreparedStatement pst;
            ResultSet rs;
         try
 {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost/melwa","root","");
            pst = con.prepareStatement("select distinct city from employee");
     rs=pst.executeQuery();
            
            if(rs.next())
    {
       out.println("<tr>");
              out.println("<td>Choose City Name</td>");
       out.println("<td>");
       out.println("<select name='combo'>");
   do{
            String cityname = rs.getString("city");
     out.println("<option value='"+ cityname   +"'>" +  cityname +  "</option>");
          }
            while(rs.next());
    out.println("</select>");
           rs.close();
    out.println("</td></tr>");
           out.println("</tr><td colspan=2 align=center><input type=submit  value=Retrieve></td></tr>");
           }
           else
            {
           out.println("<tr>");
     out.println("<td colspan=2 align=right>");
    out.println("Sorry table is Empty");
           out.println("</td>");
            } 

    out.println("</form>");
   }
        catch(Exception e)
        {
                 e.printStackTrace(); // Or System.out.println(e);
        }  
                              
        %>
         </div>
                    
           
                    
          </form>                     
                    
              </div>      
               
     </div>
</div>

    </body>
</html>

Servlett.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/servlett")
public class serveltt extends HttpServlet 
{
     int i;
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
    {
        try {
            i++;
            PrintWriter out = response.getWriter();
            response.setContentType("text/html");
            String cityname= request.getParameter("combo");

            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost/melwa","root","");
            pst = con.prepareStatement("select empid,fname,title,address from employee where city=?");
            pst.setString(1, cityname);
            rs = pst.executeQuery();
             
             out.println("<table width=60% border= 1   >");
             out.println("<tr><td colspan=4 " );
             out.println("<center><h2>Result of Search Page</h2></center>"); 
             out.println("</td></tr>");
             out.println("<tr>");
             out.println("<th>Employee id</th>");
             out.println("<th>Employee name</th>");
             out.println("<th>Title</th>");
             out.println("<th>Address</th>");
             out.println("</tr>");
              
              while(rs.next())
              {
                  out.println("<tr>");
                  out.println("<td>" + rs.getString("empid") + "</td> ");
                  out.println("<td>" + rs.getString("fname") + "</td> ");
                  out.println("<td>" + rs.getString("title") + "</td> ");
                  out.println("<td>" + rs.getString("address") + "</td> ");
                  out.println("</tr>");

                  
              }
              out.println("</table>");
        
               } 
            catch (ClassNotFoundException ex) {
           
        }catch (Exception e) 
        { throw new ServletException("error", e); }
    }
    
    public void destory()
    {
        i = 0;
    }

    
}