javaZ: Ajax autocomplete source code in jsp retrieving data from database

This project is done in netbeans7.0.1

Steps for setting up the project
1.Select Java Web in categories directory in the left
2.Select Web Application in the project directory
3.Set the project name as "autocomplete"
4.Then click next
5.Set the JavaEE version: Java EE 6 Web and click finish

For this two javascript files and two jsp pages (index.jsp and list.jsp) and one css( style.css )

1.jquery-1.4.2.min.js

2.jquery.autocomplete.js

Place the above js file in a single folder by name "JS" under Web Pages(right click Web Pages and New -> Other -> Other -> folder and name it JS) directory of the project by name "autocomplete" in netbeans

3.In the index.jsp, index.jsp will be present in Web Pages folder by default,dont delete it, remove the existing code and paste the following the code :

-----------------------------------------------index.jsp-----------------------------------------------------

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"
>

<html>
<head>
<link rel="stylesheet" type="text/css" href="css/style.css" />
<script type="text/javascript" src="JS/jquery-1.4.2.min.js"></script>
<script src="JS/jquery.autocomplete.js"></script>
</head>
<body>
<div style="width: 300px; margin: 50px auto;">
<b>Country</b> : <input type="text" id="country" name="country" class="input_text"/>
</div>

</body>
<script>
jQuery(function(){
$("#country").autocomplete("list.jsp");
});
</script>
</html>
--------------------------------------end of index.jsp------------------------------------------------------

4.In the list.jsp ,remove the existing code and paste the following the code:

-------------------------------------------list.jsp-----------------------------------------------------------


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

<%
try{
Connection con;
Statement stmt;
ResultSet rs;
String host="jdbc:mysql://localhost:3306/books";
//books is the database name
String uName="root";
String uPass="enter your password";
con=DriverManager.getConnection(host,uName,uPass);
stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

/* copy the countries name in the table "country" with a field name countries
countrieslist =
"Afghanistan",
"Albania",
"Algeria",
"Andorra",
"Angola",
"Antigua and Barbuda",
"Argentina",
"Armenia",
"Australia",
"Austria",
"Azerbaijan",
"Bahamas",
"Bahrain",
"Bangladesh",
"Barbados",
"Belarus",
"Belgium",
"Belize",
"Benin",
"Bhutan",
"Bolivia",
"Bosnia and Herzegovina",
"Botswana",
"Brazil",
"Brunei",
"Bulgaria",
"Burkina Faso",
"Burundi",
"Cambodia",
"Cameroon",
"Canada",
"Cape Verde",
"Central African Republic",
"Chad",
"Chile",
"China",
"Colombi",
"Comoros",
"Congo (Brazzaville)",
"Congo",
"Costa Rica",
"Cote d'Ivoire",
"Croatia",
"Cuba",
"Cyprus",
"Czech Republic",
"Denmark",
"Djibouti",
"Dominica",
"Dominican Republic",
"East Timor (Timor Timur)",
"Ecuador",
"Egypt",
"El Salvador",
"Equatorial Guinea",
"Eritrea",
"Estonia",
"Ethiopia",
"Fiji",
"Finland",
"France",
"Gabon",
"Gambia, The",
"Georgia",
"Germany",
"Ghana",
"Greece",
"Grenada",
"Guatemala",
"Guinea",
"Guinea-Bissau",
"Guyana",
"Haiti",
"Honduras",
"Hungary",
"Iceland",
"India",
"Indonesia",
"Iran",
"Iraq",
"Ireland",
"Israel",
"Italy",
"Jamaica",
"Japan",
"Jordan",
"Kazakhstan",
"Kenya",
"Kiribati",
"Korea, North",
"Korea, South",
"Kuwait",
"Kyrgyzstan",
"Laos",
"Latvia",
"Lebanon",
"Lesotho",
"Liberia",
"Libya",
"Liechtenstein",
"Lithuania",
"Luxembourg",
"Macedonia",
"Madagascar",
"Malawi",
"Malaysia",
"Maldives",
"Mali",
"Malta",
"Marshall Islands",
"Mauritania",
"Mauritius",
"Mexico",
"Micronesia",
"Moldova",
"Monaco",
"Mongolia",
"Morocco",
"Mozambique",
"Myanmar",
"Namibia",
"Nauru",
"Nepal",
"Netherlands",
"New Zealand",
"Nicaragua",
"Niger",
"Nigeria",
"Norway",
"Oman",
"Pakistan",
"Palau",
"Panama",
"Papua New Guinea",
"Paraguay",
"Peru",
"Philippines",
"Poland",
"Portugal",
"Qatar",
"Romania",
"Russia",
"Rwanda",
"Saint Kitts and Nevis",
"Saint Lucia",
"Saint Vincent",
"Samoa",
"San Marino",
"Sao Tome and Principe",
"Saudi Arabia",
"Senegal",
"Serbia and Montenegro",
"Seychelles",
"Sierra Leone",
"Singapore",
"Slovakia",
"Slovenia",
"Solomon Islands",
"Somalia",
"South Africa",
"Spain",
"Sri Lanka",
"Sudan",
"Suriname",
"Swaziland",
"Sweden",
"Switzerland",
"Syria",
"Taiwan",
"Tajikistan",
"Tanzania",
"Thailand",
"Togo",
"Tonga",
"Trinidad and Tobago",
"Tunisia",
"Turkey",
"Turkmenistan",
"Tuvalu",
"Uganda",
"Ukraine",
"United Arab Emirates",
"United Kingdom",
"United States",
"Uruguay",
"Uzbekistan",
"Vanuatu",
"Vatican City",
"Venezuela",
"Vietnam",
"Yemen",
"Zambia",
"Zimbabwe"
};*/

String query = (String)request.getParameter("q");
String sql="select countries from country where countries like '"+query+"%'";
rs=stmt.executeQuery(sql);
rs.last();
int length=rs.getRow();
response.setHeader("Content-Type", "text/html");
int cnt=1;

if(rs.first()){
for(int i=0;i<length;i++)
{
if(rs.getString(1).toUpperCase().startsWith(query.toUpperCase()))
{
out.print(rs.getString(1)+"\n");
if(cnt>=10)
break;
cnt++;
rs.next();
}

}
}
else{
out.println("country name not in the database");
}

%>
<%
}
catch(Exception e1){
out.println("cannot display the records");
}
%>

---------------------------------------------end of list.jsp---------------------------------------------------
place the two jsp in Web Pages foler directly

5. For the style.css right click the Web Pages directory (New -> Other -> Other -> folder ) and name it "css"
and then right click the css folder (New -> Other -> Other -> CascadingStyleSheet ) and name it "style.css"
In the "style.css", remove the existing code and paste the following the code:
----------------------------------------------style.css-------------------------------------------------------
.ac_results {
padding: 0px;
border: 1px solid #ff7c08;
background-color: white;
overflow: hidden;
}

.ac_results ul {
width: 100%;
list-style-position: outside;
list-style: none;
padding: 0;
margin: 0;
}

.ac_results li {
margin: 0px;
padding: 2px 5px;
cursor: default;
display: block;
color: #972800;
font-family:Arial, Helvetica, sans-serif;
/*
if width will be 100% horizontal scrollbar will apear
when scroll mode will be used
*/
/*width: 100%;*/
font-size: 12px;
/*
it is very important, if line-height not setted or setted
in relative units scroll will be broken in firefox
*/
line-height: 16px;
overflow: hidden;

}

.ac_loading {
background: white url('../images/indicator.gif') right center no-repeat;
}

.ac_odd {
background-color: #fef2d8;
}

.ac_over {
background-color: #febb80;
color: white;
}

.input_text{
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
border:1px solid #FF7C08;
padding:2px;
width:150px;
color:#802900;
background:white url(../images/search.png) no-repeat 3px 2px;
padding-left:17px;
}

-------------------------------------------end of style.css---------------------------------------------------

For this two images are required:

1. indicator.gif

2. search.png

--------------------------------------------screenshot-------------------------------------------------------

Srcreenshots

------------------------------------------------sql command------------------------------------------------
databsename : books

CREATE TABLE country (countries VARCHAR(20) NOT NULL, PRIMARY KEY (countries));
INSERT INTO books.country (countries) VALUES ('Angola');

javaZ

Pages

Ajax autocomplete source code in jsp retrieving data from database

4 comments:

Blog Archive

Chapters