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





1 comments:

Unknown said...

list not show state and city plz referece code