jQuery Autocomplete from Database in JSP - onlyxcodes

Friday, 30 November 2018

jQuery Autocomplete from Database in JSP

In today’s post, I’m going to show you jQuery Autocomplete from Database in JSP. Autocomplete widget features save the user's time because the suggested value was highlighting as according to frequently input type.

This widget helps the websites more valuable when the user search for any type such as products, categories, topics or something else, triggers this widget and gets the value of suggestions from the database and displays pretty and user friendly.

This example contains a text box as the user type in this box and the suggestion which arrives automatically from the JSP script on the server-side that returns the user-based terms they enter. Implementation of jQuery and Ajax codes achieves this Autocomplete search functionality. Let's get the tutorial started much easier.

jQuery Autocomplete from Database in JSP

Table Content

1. What is jQuery Autocomplete?

2. Draw Table

3. Build Project In NetBeans

4. Design Text Box

5. jQuery and Ajax Codes

6. record.jsp

7. index.jsp file Full Codes


1. What is jQuery Autocomplete?

The jQuery AutoComplete is aimed to help users seek the choice quickly from a during the populated list of words based on the user-entered text. It mainly focuses on continuing to give users suggestions if typing into the field.

2. Draw Table

See the sample table called country which contains only the name field, just paste this SQL command into your Oracle SQL prompt to quickly draw the table.

create table country(
id number,
name varchar2(20)
);

create country named table . jQuery Autocomplete from Database in JSP

Next In this table we attach 10 country name dumping records, execute the SQL insert query command below to immediately store below whole records.

insert into country(id,name)values(1,'australia');
insert into country(id,name)values(2,'brazil');
insert into country(id,name)values(3,'chaina');
insert into country(id,name)values(4,'denmark');
insert into country(id,name)values(5,'egypt');
insert into country(id,name)values(6,'france');
insert into country(id,name)values(7,'india');
insert into country(id,name)values(8,'united states');
insert into country(id,name)values(9,'united kingdom');
insert into country(id,name)values(10,'united arab');

insert 10 country name dumping records . jQuery Autocomplete from Database in JSP


3. Build Project In NetBeans

Open NetBeans, go to section top and select File->New Project option.

Open NetBeans, go to section top and select File->New Project

Look open, configure the popup window, select Java Web option on the left-hand side and select Web Application on the right-hand side, and click next.

select Java Web option on the left-hand side and select Web Application on the right-hand side,

See again opening a new popup window, in this section that you add the project name "jQuery_Autocomplete_from_database_JSP", and click to the next button.

add the project name "jQuery_Autocomplete_from_database_JSP"

Finally, the last step to click the Finish button to build a project entirely, see the structure of the project directory below.

click the Finish button to build a project entirely,

Complete the project directory below.

Complete project directory structure of jQuery Autocomplete from database in JSP with NetBeans IDE

jQuery –  To help ajax in auto-populating text box value, you'll need to use a jQuery library.

Bootstrap – I use the bootstrap classes for the text box design.

Note:- I created an index.jsp page. I included Bootstrap CSS, JS, and jQuery library in the specific location below on this page.

Below bootstrap.min.css file included before closing of </head> tag. 

<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css"> 

The version library jQuery-1.12.4-jquery.min.js and the bootstrap.min.js library included after the < /HTML > tag is closed.

<script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script> 

4. Design Text Box

I design a simple text box with bootstrap just for a good look. Just below in this text box, I added < div > and < ul >, these two tags helping to display automatically suggested value in pretty look.

<div class="container">
            <label>Enter Country Name</label>
            <input type="text" id="txtCountry" class="form-control" placeholder="enter country name">
            <div id="showList">
                <ul class="list-group">
                </ul>
            </div>
</div>

5. jQuery and Ajax Codes

See the jQuery and Ajax code below, when the user types the few country name words in the text box then the keyup() method event was first triggered. And the inputted word or value request will be sent to 'record.jsp' page by ajax POST method and highlight the list of the country name suggested.

#txtCountry is the id attribute of the text box field and the .val() method uses that attribute to get this box inputted value.

When the user clicks any country value populated then the text() method will add this value to the text box.

<script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script> 
<script type="text/javascript">
        $(document).ready(function(){
            $('#txtCountry').keyup(function(){
                var search=$('#txtCountry').val();
                if(search !=='' && search !==null)
                {    
                    $.ajax({ 
                       type:'POST',
                       url:'record.jsp',
                       data:'key='+search,
                       success:function(data)
                       {
                           $('#showList').html(data);
                       }
                    }); 
                }
                else
                {
                    $('#showList').html('');
                }
            });
            $(document).on('click','li',function(){
               $('#txtCountry').val($(this).text());
            });
        });
</script> 

6. record.jsp

This file is for the 'country' table suggestion and works mutely back-end, Whenever the user types in the text box, this file is caused and reverts the data as the auto-suggestion country name. And that data shows pretty look from within the text box exactly like a dropdown.

% this wildcard helps you to match any string that we use in condition Oracle LIKE. Yet I already concatenate this wildcard with wild variable and apply select query on SQL.

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>

<%
    if(request.getParameter("key")!=null) //get "key" variable from jquery & ajax  part this line "data:'key='+search" and check not null 
    {
        String key=request.getParameter("key"); //get "key" variable store in created new "key" variable
        String wild="%" +key+ "%"; //remove "%" for use preparedstatement in query name like, and "key" variable store in "wild" variable for further use
        
        String url="jdbc:oracle:thin:@localhost:1521:XE"; //database url string
        String username="system"; //database username
        String password="tiger"; //database password

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver"); //load driver
            Connection con=DriverManager.getConnection(url,username,password); //create connection

            PreparedStatement pstmt=null; //create statement

            pstmt=con.prepareStatement("SELECT * FROM country WHERE name LIKE ? "); //sql select query
            pstmt.setString(1,wild); //above created "wild" variable set in this
            ResultSet rs=pstmt.executeQuery(); //execute query and set in ResultSet object "rs".
           
            while(rs.next())
            {
                %>
                    <li class="list-group-item"><%=rs.getString("name")%></li>
                <%
            }
                con.close(); //close connection
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
%>
 

Learn More:-



7. index.jsp file full codes

Paste full index.jsp file codes into this tutorial. Separately created text box codes, jQuery Ajax codes, bootstrap CSS & JS and jQuery library codes are easy to find out.

Row no 7 – Included CSS library of bootstrap.

Row no 32 – I put the text box codes, when you type the country name then the text box will automatically populate the country name exactly just below.

Row no 41 to 42 – Included the bootstrap JS and jQuery library file.

Row no 43 to 68 – See the full code of jQuery Ajax.

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Autocomplete Searchbox using jQuery, Ajax, JSP and Oracle Database</title>
        <link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css">
    </head>
    
    <body>    
    <nav class="navbar navbar-default navbar-static-top">
      <div class="container">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
                <span class="sr-only">Toggle navigation</span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
          </button>
            <a class="navbar-brand" href="https://onlyxscript.blogspot.com/">onlyxscript.blogspot.com</a>
        </div>
        <div id="navbar" class="navbar-collapse collapse">
            <ul class="nav navbar-nav">
                <li class="active"><a href="https://onlyxscript.blogspot.com/2018/11/autocomplete-searchbox-using-jquery.html">Back to Tutorial</a></li>
            </ul>       
        </div><!--/.nav-collapse -->
      </div>
    </nav>

        <div class="container">
            <label>Enter Country Name</label>
            <input type="text" id="txtCountry" class="form-control" placeholder="enter country name">
            <div id="showList">
                <ul class="list-group">
                </ul>
            </div>
        </div>
    </body>
</html>

<script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script> 
<script type="text/javascript">
        $(document).ready(function(){
            $('#txtCountry').keyup(function(){
                var search=$('#txtCountry').val();
                if(search !=='' && search !==null)
                {    
                    $.ajax({ 
                       type:'POST',
                       url:'record.jsp',
                       data:'key='+search,
                       success:function(data)
                       {
                           $('#showList').html(data);
                       }
                    }); 
                }
                else
                {
                    $('#showList').html('');
                }
            });
            $(document).on('click','li',function(){
               $('#txtCountry').val($(this).text());
            });
        });
</script>


Download Codes

No comments:

Post a Comment