AJAX Drop down Example with Database ~ My Journey on Java

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.

Click Next .
Enter the name of the Web project into the Project Name field.
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 NULL, PRIMARY KEY (`countryid`) ) ;

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

CREATE TABLE `city`( `cityid` BIGINT(255) NOT NULL AUTO_INCREMENT, `stateid` INT(255) DEFAULT NULL,`city` VARCHAR(255) DEFAULT NULL, PRIMARY 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, '2', 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, '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;

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

My Journey on Java - Ranga Reddy

Wednesday, August 1, 2012

AJAX Drop down Example with Database

1 comments:

Labels

Blog Archive