Category Archives: AJAX

Retrive data from Database using AJAX

Scenario:

I have a home page, that is, index page.  The page contains 2 drop-down boxes.  If I select a value from 1st drop-down box, based on that, set of values should be populated into the 2nd drop-down box.  The datas to be populated has to be retrived from database.

In the following example, the 2 drop-down boxes represents “select client” and “projects”.  If I select particular client from “select client” drop-down box, the list of projects of that client should be populated into the “projects” drop-down box from the database.

index.html

<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”&gt;

<html>

<head>

<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-1″>

<title>Fetch Data from database using AJAX</title>

<script type=”text/javascript”>

var xmlhttp;

function showProjects(givenString)

{

xmlhttp = GetXmlHttpObject();

if (xmlhttp==null)

{

alert (“Your browser does not support AJAX!”);

return;

}

if(givenString == “select”)

{

return;

}

//Whome to contact

var url = “GetProjects?client=” + givenString;

alert(url);

xmlhttp.open( “GET”, url, true );

xmlhttp.send( null );

xmlhttp.onreadystatechange=stateChanged;

}

//Once the data is ready, what action has to be done

function stateChanged()

{

if(xmlhttp.readyState==4)

{

if(xmlhttp.status == 200)

{

var resp=xmlhttp.responseText;

if(resp == “Class Not Found Exception”)

{

document.getElementById(“error”).innerHTML= “<font color=’red’>Class Not Found Exception at server</font>”;

}

else if(resp == “SQL Exception”)

{

document.getElementById(“error”).innerHTML= “<font color=’red’>SQL Exception at server</font>”;

}

else

{

var options=resp.split(“,”);

removeAllOptions(document.getElementById(“projects”));

for(i=0;i<options.length – 1 ;i++)

{

addOption(document.getElementById(“projects”),options[i],options[i]);

}

}

}

else

{

document.getElementById(“error”).innerHTML = “<font color=’red’>”+xmlhttp.status+”</”;

}

}

}

//Creating XMLHttpRequest Object

function GetXmlHttpObject()

{

if (window.XMLHttpRequest)

{

// code for IE7+, Firefox, Chrome, Opera, Safari

return new XMLHttpRequest();

}

if (window.ActiveXObject)

{

// code for IE6, IE5

return new ActiveXObject(“Microsoft.XMLHTTP”);

}

return null;

}

function removeAllOptions(selectbox)

{

var i;

for(i = selectbox.options.length – 1; i >= 0; i — )

{

// selectbox.options.remove(i);

selectbox.remove(i);

}

}

function addOption(selectbox, value, text )

{

var optn = document.createElement(“OPTION”);

optn.text = text;

optn.value = value;

selectbox.options.add(optn);

}

</script>

</head>

<body>

<form>

Select a Client:

<select id=”client” onchange=”showProjects(this.value)”>

<option value=”select”>Select…</option>

<option value=”clientx”>Client X</option>

<option value=”clienty”>Client Y</option>

</select><br/><br/>

Projects:

<select id=”projects”>

</select>

<span id=”error”></span>

</form>

</body>

</html>

<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”&gt;
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-1″>
<title>Fetch Data from database using AJAX</title>
<script type=”text/javascript”>
var xmlhttp;
function showProjects(givenString)
{
xmlhttp = GetXmlHttpObject();
if (xmlhttp==null)
{
alert (“Your browser does not support AJAX!”);
return;
}
if(givenString == “select”)
{
return;
}
//Whome to contact
var url = “GetProjects?client=” + givenString;
alert(url);
xmlhttp.open( “GET”, url, true );
xmlhttp.send( null );
xmlhttp.onreadystatechange=stateChanged;
}
//Once the data is ready, what action has to be done
function stateChanged()
{
if(xmlhttp.readyState==4)
{
if(xmlhttp.status == 200)
{
var resp=xmlhttp.responseText;
if(resp == “Class Not Found Exception”)
{
document.getElementById(“error”).innerHTML= “<font color=’red’>Class Not Found Exception at server</font>”;
}
else if(resp == “SQL Exception”)
{
document.getElementById(“error”).innerHTML= “<font color=’red’>SQL Exception at server</font>”;
}
else
{
var options=resp.split(“,”);
removeAllOptions(document.getElementById(“projects”));
for(i=0;i<options.length – 1 ;i++)
{
addOption(document.getElementById(“projects”),options[i],options[i]);
}
}
}
else
{
document.getElementById(“error”).innerHTML = “<font color=’red’>”+xmlhttp.status+”</”;
}
}
}
//Creating XMLHttpRequest Object
function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
{
// code for IE7+, Firefox, Chrome, Opera, Safari
return new XMLHttpRequest();
}
if (window.ActiveXObject)
{
// code for IE6, IE5
return new ActiveXObject(“Microsoft.XMLHTTP”);
}
return null;
}
function removeAllOptions(selectbox)
{
var i;
for(i = selectbox.options.length – 1; i >= 0; i — )
{
// selectbox.options.remove(i);
selectbox.remove(i);
}
}
function addOption(selectbox, value, text )
{
var optn = document.createElement(“OPTION”);
optn.text = text;
optn.value = value;
selectbox.options.add(optn);
}
</script>
</head>
<body>
<form>
Select a Client:
<select id=”client” onchange=”showProjects(this.value)”>
<option value=”select”>Select…</option>
<option value=”clientx”>Client X</option>
<option value=”clienty”>Client Y</option>
</select><br/><br/>
Projects:
<select id=”projects”>
</select>
<span id=”error”></span>
</form>
</body>
</html>

eclipse->project->

src\servletPackage\GetProjects.java

package servletPackage;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

/**

* Servlet implementation class GetProjects

*/

public class GetProjects extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

* @see HttpServlet#HttpServlet()

*/

public GetProjects() {

super();

// TODO Auto-generated constructor stub

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException

{

// TODO Auto-generated method stub

try

{

System.out.println(“Server preparing response…”);

String resp=””;

PrintWriter writer = response.getWriter();

response.setHeader(“Cache-Control”, “no-cache”);

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Connection con = DriverManager.getConnection(“JDBC:ODBC:dbaccess_ajax_dsn”);

System.out.println(“Database connection Success!”);

Statement st = con.createStatement();

String sql = “select project from ProjectDetails where client='”+ request.getParameter(“client”).toLowerCase() +”‘”;

System.out.println(sql);

ResultSet rs = st.executeQuery(sql);

if(rs!=null)

{

while(rs.next())

{

resp+=rs.getString(1)+”,”;

}

resp+=rs.getString(1)+”,”;

rs.close();

st.close();

con.close();

System.out.println(“Server finished preparing response…”);

}

response.getWriter().print(resp);

}

catch(ClassNotFoundException cfexp)

{

response.getWriter().print(“Class Not Found Exception”);

}

catch(SQLException sqlexp)

{

response.getWriter().print(“SQL Exception”);

}

}

}