Selection With Ajax and JSP

I am working at a jsp page using ajax for country , state, city selection. so if he select country it will populate the state and city selection (both). After selecting country if he select city it will populate the state selection and if he select the state it will populate the city. I am doing it through two jsp pages only. Country1.jsp :

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*"%>

Insert title here

      </head>  
      <body>  
      <table border="1">
      <tr><th>Country</th><th>State</th><th>City</th></tr>
      <tr><td>
      <select id='count' name='country' onchange="showState()">  
       <option value="none">Select</option>
       <%
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       Connection con = DriverManager.getConnection("jdbc:odbc:senior");
       Statement stat = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
       ResultSet rs = stat.executeQuery("Select country from hotel");

       while(rs.next())
    {
           String co=rs.getString("country");

           %>

        <option value="co"><%=co%></option>
  <%
  }
 %>
  </select> 
  </td>
  <td id='change'><select id='stat' name='state' >  
  <option value='-1'></option>  
  </select>
  <select id='cit' name='city' >  
  <option value='-1'></option>  
  </select>

  </tr>
  </table>
  </body> 
  </html>


**Country2.jsp**



 <%@page import="java.sql.*"%>
<%
String country=request.getParameter("count");
String city=request.getParameter("city");
String state=request.getParameter("state");
 String buffer="<select id='stat' name='state' onchange='showCity();'><option value='-1'>Select</option>"; 
 String buffer2="<select id='cit' name='city' onchange='showstate();'><option value='-1'>Select</option>";  
 try{
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     Connection con = DriverManager.getConnection("jdbc:odbc:senior");
     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
 ResultSet rs=null;
 ResultSet rs2=null;
 if(country!=null && city==null && state==null ){
     rs = stmt.executeQuery("Select state from hotel where country='"+country+"' ");

     while(rs.next()){
           buffer=buffer+"<option value='"+rs.getString("state")+"'>"+rs.getString("state")+"</option>"; 
           }

     rs = stmt.executeQuery("Select city from hotel where country='"+country+"' ");
     while(rs.next()){
           buffer2=buffer2+"<option value='"+rs.getString("city")+"'>"+rs.getString("city")+"</option>"; 
           }


 }
 else
     if(country!=null && city!=null && state==null ){
     rs = stmt.executeQuery("Select state from hotel where country='"+country+"' AND city='"+city+"' "); 
     while(rs.next()){
           buffer=buffer+"<option value='"+rs.getString("state")+"'>"+rs.getString("state")+"</option>";  
           }

     }
 else
     if(country!=null && city==null && state!=null ){
     rs = stmt.executeQuery("Select city from hotel where country='"+country+"' AND tate='"+state+"' ");
       while(rs.next()){
           buffer2=buffer2+"<option value='"+rs.getString("city")+"'>"+rs.getString("city")+"</option>";  
           }

     } 
 buffer=buffer+"</select>";
 buffer2=buffer2+"</select>";
 buffer=buffer+buffer2;
 response.getWriter().println(buffer); 

 }
 catch(Exception e){
     System.out.println(e);
 }

 %>

The database is called senior on Microsoft access , with table called hotel(ID,Country,State,city).

View Answers

April 2, 2012 at 10:50 AM

1)country.jsp:

 <%@page import="java.sql.*"%>
 <html>
      <head>  
      <script language="javascript" type="text/javascript">  
      var xmlHttp  
      var xmlHttp
      function showState(str){
      if (typeof XMLHttpRequest != "undefined"){
      xmlHttp= new XMLHttpRequest();
      }
      else if (window.ActiveXObject){
      xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
      }
      if (xmlHttp==null){
      alert("Browser does not support XMLHTTP Request")
      return;
      } 
      var url="state.jsp";
      url +="?count=" +str;
      xmlHttp.onreadystatechange = stateChange;
      xmlHttp.open("GET", url, true);
      xmlHttp.send(null);
      }

      function stateChange(){   
      if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){   
      document.getElementById("state").innerHTML=xmlHttp.responseText   
      }   
      }

      function showCity(str){
      if (typeof XMLHttpRequest != "undefined"){
        xmlHttp= new XMLHttpRequest();
        }
      else if (window.ActiveXObject){
        xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
        }
      if (xmlHttp==null){
      alert("Browser does not support XMLHTTP Request")
      return;
      } 
      var url="city.jsp";
      url +="?count=" +str;
      xmlHttp.onreadystatechange = stateChange1;
      xmlHttp.open("GET", url, true);
      xmlHttp.send(null);
      }
      function stateChange1(){   
      if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){   
      document.getElementById("city").innerHTML=xmlHttp.responseText   
      }   
      }
      </script>  
      </head>  
      <body>  
      <select name='country' onchange="showState(this.value)">  
       <option value="none">Select</option>  
    <%
 Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from country");
 while(rs.next()){
     %>
      <option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>  
      <%
 }
     %>
      </select>  
      <br>  
      <div id='state'>  
      <select name='state' >  
      <option value='-1'></option>  
      </select>  
      </div>  

      <div id='city'>  
      <select name='city' >  
      <option value='-1'></option>  
      </select>  
      </div>
      </body> 
      </html>

2)state.jsp:

<%@page import="java.sql.*"%>
<%
String country=request.getParameter("count");  
 String buffer="<select name='state' onchange='showCity(this.value);'><option value='-1'>Select</option>";  
 try{
 Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from state where countryid='"+country+"' ");  
   while(rs.next()){
   buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString(3)+"</option>";  
   }  
 buffer=buffer+"</select>";  
 response.getWriter().println(buffer); 
 }
 catch(Exception e){
     System.out.println(e);
 }

 %>

April 2, 2012 at 10:51 AM

continue..

3)city.jsp:

<%@page import="java.sql.*"%>
<%
String state=request.getParameter("count");  
 String buffer="<select name='city'><option value='-1'>Select</option>";  
 try{
 Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
 Statement stmt = con.createStatement();  
 ResultSet rs = stmt.executeQuery("Select * from city where stateid='"+state+"' ");  
   while(rs.next()){
   buffer=buffer+"<option value='"+rs.getString(2)+"'>"+rs.getString(3)+"</option>";  
   }  
 buffer=buffer+"</select>";  
 response.getWriter().println(buffer); 
 }
 catch(Exception e){
     System.out.println(e);
 }
 %>

We have created 3 dependent dropdown. You can create the fourth one similarly.

For the above code, we have used 3 database tables:

1)country

CREATE TABLE `country` (                                 
           `countryid` bigint(255) NOT NULL auto_increment,       
           `countryname` varchar(255) default NULL,               
           PRIMARY KEY  (`countryid`)                             
     )

2)state

CREATE TABLE `state` (                                   
          `stateid` bigint(255) NOT NULL auto_increment,         
          `countryid` int(255) default NULL,                     
          `state` varchar(255) default NULL,                     
          PRIMARY KEY  (`stateid`)                               
        )

3)city

CREATE TABLE `city` (                                    
          `cityid` bigint(255) NOT NULL auto_increment,          
          `stateid` int(255) default NULL,                       
          `city` varchar(255) default NULL,                      
          PRIMARY KEY  (`cityid`)                                
        )