www.webdeveloper.com
Results 1 to 5 of 5

Thread: JSP Autocomplete through MySQL Database

  1. #1
    Join Date
    Sep 2011
    Posts
    15

    Smile JSP Autocomplete through MySQL Database

    My auto-complete function not working. Kindly help me?
    Any help I get will very much appreciated.

    Code:
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
        <head>
            <title></title>
            <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>
            <script>
                jQuery(function(){
                    $("#country").autocomplete("list.jsp");
                });
            </script>
        </body>
    </html>
    Code:
    <%@page import="dbFuntions.*, methods.*" %>
    <%@page import="java.util.Iterator"%>
    <%@page import="java.util.List"%>
    <%@page import="java.util.ArrayList"%>
    
    <%
    
        String q = (String) request.getParameter("q");
        AutoCompleteDB reportDB = AutoCompleteDBImpl.getautoCompleteDB();
        ArrayList inits = reportDB.getInitials(q);
    
        Data2010 init;
        for (Iterator a = inits.iterator(); a.hasNext();) {
            init = (Data2010) a.next();
    
            String countries[] = {init.getCustomerName()};
    
            String query = (String) request.getParameter("q");
            response.setHeader("Content-Type", "text/html");
            int cnt = 1;
            for (int i = 0; i < countries.length; i++) {
                if (countries[i].toUpperCase().startsWith(query.toUpperCase())) {
                    out.print(countries[i] + "\n");
                    if (cnt >= 10) {
                        break;
                    }
                    cnt++;
                }
            }
        }
    
    %>


    Code:
    package dbFuntions;
    
    import java.sql.*;
    import java.util.ArrayList;
    import methods.Data2010;
    import methods.Data2010Impl;
    
    public class AutoCompleteDBImpl implements AutoCompleteDB {
    
        // Singleton object
        private static AutoCompleteDB theAutoCompleteDB = null;
        private Connection databaseConnection = null;
    
        // private so that instances can't be created to follow Singleton pattern
        private AutoCompleteDBImpl() {
        }
    
        // only create an instance if one doens't already exist - to follow Singleton pattern
        public static AutoCompleteDB getautoCompleteDB() {
            if (theAutoCompleteDB == null) {
                theAutoCompleteDB = new AutoCompleteDBImpl();
            }
            return theAutoCompleteDB;
        }
    
        private Statement getConnection()
                throws ClassNotFoundException, SQLException {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String sourceURL = "jdbc:mysql://localhost:3306/asiaone";
                databaseConnection = DriverManager.getConnection(sourceURL, "root", "");
                return databaseConnection.createStatement();
            } catch (ClassNotFoundException cnfe) {
                System.out.println(cnfe);
                throw cnfe;
            } catch (SQLException se) {
                System.out.println(se);
                throw se;
            }
        }
    
        private void closeConnection() throws SQLException {
            try {
                databaseConnection.close();
            } catch (SQLException se) {
                System.out.println(se);
                throw se;
            }
        }
        
        private Data2010 populateCName(ResultSet rs)
                throws ClassNotFoundException, SQLException{
            String CustomerName = rs.getString("CustomerName");
            
            return new Data2010Impl(CustomerName);
        }
        
        public ArrayList<Data2010> getInitials(String q)
                throws ClassNotFoundException, SQLException{
            try {
                ArrayList<Data2010> initials = new ArrayList<Data2010>();
                Statement myStatement = getConnection();
                String query = "SELECT CustomerName FROM 2010_data WHERE CustomerName LIKE '+ q +%'";
                ResultSet rs = myStatement.executeQuery(query);
                
                while (rs.next()){
                    initials.add(populateCName(rs));
                }
                closeConnection();
                return initials;
            }catch(ClassNotFoundException cnfe){
                System.out.println(cnfe);
                throw cnfe;
            }catch(SQLException sqle){
                System.out.println(sqle);
                throw sqle;
            }
        }
        
    }

    Code:
    package dbFuntions;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import methods.Data2010;
    
    public interface AutoCompleteDB {
        
        public ArrayList<Data2010> getInitials(String q)
                throws ClassNotFoundException, SQLException;    
    }

  2. #2
    Join Date
    Jan 2009
    Posts
    3,346
    Are you getting any errors? It is really a lot of work to step through your code looking for a possible issue when "it isn't working" is the only lead to go on.

  3. #3
    Join Date
    Sep 2011
    Posts
    15
    Actually, what I want is - for e.g. when I pressed 'a', there will be a drop down list of all the names start with 'a'. I want to retrieve the data for the drop down list from MySQL database.
    The above code does not work for the drop down list. Thanks a lot for your reply.

  4. #4
    Join Date
    Jan 2009
    Posts
    3,346
    Quote Originally Posted by pote86 View Post
    Actually, what I want is - for e.g. when I pressed 'a', there will be a drop down list of all the names start with 'a'. I want to retrieve the data for the drop down list from MySQL database.
    The above code does not work for the drop down list. Thanks a lot for your reply.
    I understand what the goal of an autocomplete is. What I don't know (and don't feel like compiling your code to find out) is whether you are getting an error message, nothing happens, something unexpected happens, etc... Could you clue us in a bit to what you mean by "the above code does not work"?

  5. #5
    Join Date
    Sep 2011
    Posts
    15
    Sorry for unclear decription. When I typed into my autocomplete text box, nothing happened.

    Code:
    <%@page import="java.util.Iterator"%>
    <%@page import="java.util.List"%>
    <%@page import="java.util.ArrayList"%>
    <%
    	String countries[] = { 
    						 	"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");
    	//System.out.println("1"+request.getParameterNames().nextElement());
    	response.setHeader("Content-Type", "text/html");
    	int cnt=1;
    	for(int i=0;i<countries.length;i++)
    	{
    		if(countries[i].toUpperCase().startsWith(query.toUpperCase()))
    		{
    			out.print(countries[i]+"\n");
    			if(cnt>=10)
    				break;
    			cnt++;
    		}
    	}
    %>
    The above code works fine for the autocompete text box.
    What I want to do is, retrieve the array data from Mysql database instead of writing the code like this.
    Code:
    String countries[] = { 
    						 	"Afghanistan",
    							"Albania",
    							"Algeria" }
    Thanks a lot.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles