Wednesday, August 1, 2012

AJAX Drop down Example with Database

The following are the steps for creating a dropdown list by using ajax with database.

1. Create Dynamic Web Project : Open File -> New -> Other... -> Web -> Dynamic Web Project to create a dynamic Web project.
  1. Click Next .
  2. Enter the name of the Web project into the Project Name field.
  3. Proceed to one of the following steps:
    • No server runtime installed.
      • If you have not previously configured a server runtime, use the New... button beside the Target runtime field to do so.
      • Proceed to next step depending on whether target runtime is automatically selected.
    • Target runtime automatically selected
      • If you had previously installed a Tomcat 5.0 server runtime and the workbench default JRE is set to JRE 1.4, then the Target runtime field should be automatically set to Apache Tomcat v5.0 .
2.  Create a database tables and inserting values :

SHOW DATABASES;

USE `test`;

CREATE TABLE `country` ( `countryid` BIGINT(255) NOT NULL AUTO_INCREMENT, `countryname` VARCHAR(255) DEFAULT NULLPRIMARY KEY (`countryid`) ) ;

CREATE TABLE `state` ( `stateid` BIGINT(255) NOT NULL AUTO_INCREMENT, `countryid` INT(255) DEFAULT NULL, `state` VARCHAR(255) DEFAULT NULLPRIMARY KEY (`stateid`) ) ;

CREATE TABLE `city`( `cityid` BIGINT(255) NOT NULL AUTO_INCREMENT, `stateid` INT(255) DEFAULT NULL,`city` VARCHAR(255) DEFAULT NULLPRIMARY KEY (`cityid`));

INSERT INTO `test`.`country` (`countryid`, `countryname`) VALUES ('1''india');
INSERT INTO `test`.`country` (`countryid`, `countryname`) VALUES ('2''sri-lanka');
INSERT INTO `test`.`country` (`countryid`, `countryname`) VALUES (NULL'america');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'1''andhrapradesh');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'1''karnataka');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'1''tamilnadu');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'1''kerala');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'1''arunachal pradesh');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'2''Colombo');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'2''Kandy');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'2''Bandarawela');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'3''California');
INSERT INTO `test`.`state` (`stateid`, `countryid`, `state`) VALUES (NULL'3''Georgia');
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'1'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'1'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'1'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'1'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'2'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'3'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'3'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'3'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'4'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'4'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'4'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'4'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'5'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'5'NULL); 
INSERT INTO `test`.`city` (`cityid`, `stateid`, `city`) VALUES (NULL'5'NULL); 
UPDATE `test`.`city` SET `city` = 'chittoor' WHERE `cityid` = '1';
UPDATE `test`.`city` SET `city` = 'anantapur' WHERE `cityid` = '2';
UPDATE `test`.`city` SET `city` = 'kurnool' WHERE `cityid` = '3';
UPDATE `test`.`city` SET `city` = 'nellore' WHERE `cityid` = '4';
UPDATE `test`.`city` SET `city` = 'bangalore' WHERE `cityid` = '5';
UPDATE `test`.`city` SET `city` = 'mangalore' WHERE `cityid` = '6';
UPDATE `test`.`city` SET `city` = 'udipi' WHERE `cityid` = '7';
UPDATE `test`.`city` SET `city` = 'ballari' WHERE `cityid` = '8';
UPDATE `test`.`city` SET `city` = 'mysore' WHERE `cityid` = '9';
UPDATE `test`.`city` SET `city` = 'darmastalam' WHERE `cityid` = '10';
UPDATE `test`.`city` SET `city` = 'chennai' WHERE `cityid` = '11';
UPDATE `test`.`city` SET `city` = 'Coimbatore' WHERE `cityid` = '12';
UPDATE `test`.`city` SET `city` = 'Erode' WHERE `cityid` = '14';
UPDATE `test`.`city` SET `city` = 'Erode' WHERE `cityid` = '13';
UPDATE `test`.`city` SET `city` = 'kannor' WHERE `cityid` = '14';
UPDATE `test`.`city` SET `city` = 'kasrgood' WHERE `cityid` = '15';
UPDATE `test`.`city` SET `city` = 'thrisoor' WHERE `cityid` = '16';
UPDATE `test`.`city` SET `city` = 'kollam' WHERE `cityid` = '17';
UPDATE `test`.`city` SET `city` = 'tirap' WHERE `cityid` = '18';
UPDATE `test`.`city` SET `city` = 'siang' WHERE `cityid` = '19';

3. Create a Jsp pages



country.jsp


<%@page import="java.sql.*"%>

 <html>
      <head> 
            <script type="text/javascript"> 
            var xmlHttp;
            var xmlHttp;
            function showState(str){
                  if (typeof XMLHttpRequest != "undefined"){
                  xmlHttp= newXMLHttpRequest();
            }
            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);
            }
     
            functionstateChange(){  
                  if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {  
                  document.getElementById("state").innerHTML=xmlHttp.responseText   
                  }  
                }
           
            function showCity(str){
            if (typeof XMLHttpRequest != "undefined"){
              xmlHttp= newXMLHttpRequest();
              }
            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);
            }
            functionstateChange1(){  
                  if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){  
                        document.getElementById("city").innerHTML=xmlHttp.responseText   
                  }  
            }
            </script> 
      </head> 
      <body> 
      <br/>
      Country : <select name='country'onchange="showState(this.value)"> 
       <option value="none">Select Country</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'> 
            State :  <selectname='state' > 
                  <option value='-1'>Select State</option> 
            </select> 
      </div> 

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

state.jsp
<%@page import="java.sql.*"%>
<%
      String country=request.getParameter("count"); 
      String buffer="State : <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);
      }
%>

city.jsp
<%@page import="java.sql.*"%>
<%
      String state=request.getParameter("count"); 
      String buffer="City : <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);
      }
%>

Output:


Run the country.jsp

 Select the country

 Select the state
 Select the city





2 comments:

anusha said...



MEAN Stack Training in Chennai MEAN Stack Training in Chennai with real time projects. We are Best MEAN Stack Training Institute in Chennai. Our Mean Stack courses are taught by Industrial Experts which would help you to learn MEAN Stack development from the scratch.

Unknown said...

list not show state and city plz referece code