Implement Auto suggest in JSP using Ajax call in Jquery

This solution is: When user enters any data on the input field, the entered data is taken and using ajax call servlet method will be triggered, which returns the matched records in DB as JSON string. Then the response data will be shown as suggestion under input text field.

Files Created to implement this functionality:

autoSuggest.jsp, searchServlet.java.

Code for autoSuggest.jsp:


<!DOCTYPE html>
<%@ page contentType="text/html;charset=windows-1252"%>
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
         <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
  $( function() {
  var val = $("#project").val();
  var projects;

    $( "#project" ).autocomplete({
      minLength: 1,
      source: function( request, response ) {
       $.ajax({
            type: "Get",
            url:"searchservlet",            
            contentType : "application/json", dataType : 'json',
            data:{"empName":$("#project").val()},
            success: function(data){
                projects = data;
                response(data);
            },
            failure: function(){
                alert("failed..");
            }
         });
      },
      focus: function( event, ui ) {
        $( "#project" ).val( ui.item.EmpName );
        return false;
      },
      select: function( event, ui ) {
        $( "#project" ).val( ui.item.EmpName );        
        return false;
      }
    })  
   
    .data("ui-autocomplete")._renderItem = function (ul, item) {
                return $("
	<li style='font-size:16px;color:#777;padding:1px;'>")
                    .data("ui-autocomplete-item", item)
                    .append("<a> " + item.EmpName + "
 </a>")
                    .appendTo(ul);
            };
            
    /*  [This code also works similar as above [you can change the item name. But you have to use only "instance" with .autocomplete or
     * "ui-autocomplete" with .data] 
    * .autocomplete( "instance" )._renderItem = function( ul, listitem ) {
      return $( "
	<li>" )
        .append( "
<div>" + listitem.EmpName + "
" + "</div>
" )
        .appendTo( ul );        
    };*/        
  } );
  </script>
</head>
<body>
<div id="project-label">Search an Employee (type "E" for a start)::</div>
<input style="padding:10px;font-size:18px;margin:10px;" id="project">
<!--<input type="hidden" id="project-id"> -->
</body>

</html>

searchServlet.java


package model;

import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

@WebServlet(name = "searchServlet", urlPatterns = { "/searchservlet" })
public class searchServlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

public void init(ServletConfig config) throws ServletException {
super.init(config);
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("entered servlet...");
String empName = request.getParameter("empName").concat("%");
System.out.println("request value:"+empName);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
PreparedStatement ps = conn.prepareStatement("select empname from emp where empname like ? order by empname");
ps.setString(1, empName);
ResultSet rs = ps.executeQuery();
JSONArray jarray = new JSONArray();

int i =1;
while(rs.next()){
JSONObject jobj = new JSONObject();
String lable = "EmpName";
jobj.put(lable, rs.getObject(1));
jarray.add(jobj);
i++;
}

String JsonString = jarray.toJSONString();
System.out.println("JsonString:"+JsonString);
conn.close();
response.getWriter().write(JsonString);
} catch (Exception e) {
}

}
}

 

UI Page:

3

 

 

 

 

 

Advertisements

Ajax Call in JSP to fetch and display data from Database

We knew that with the help of JQuery we can dynamically display data on a webpage. So here in this post we are using  JQuery to make an Ajax call to Servlet, [Servlet connects to DB, fetches data and returns that data to Ajax call] and display the returned data as a table on JSP web page.

Create Servlet first

Create dataServlet.java

package model;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

import org.eclipse.persistence.oxm.json.JsonArrayBuilderResult;
import org.eclipse.persistence.oxm.json.JsonGeneratorResult;
import org.eclipse.persistence.oxm.json.JsonObjectBuilderResult;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

@WebServlet(name = "dataServlet", urlPatterns = { "/dataservlet" })
public class dataServlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

public void init(ServletConfig config) throws ServletException {
super.init(config);
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
System.out.println("enterred servlet...");
String empID = "";
try {
empID = request.getParameter("empID");
System.out.println("empID is:" + empID);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
PreparedStatement ps = con.prepareStatement("select * from emp where empid != ? order by empid");
ps.setString(1, empID);
ResultSet rs = ps.executeQuery();
System.out.println("got response....");

JSONArray json = new JSONArray();
ResultSetMetaData metadata = rs.getMetaData();
int numColumns = metadata.getColumnCount();

while (rs.next()) //iterate rows
{
JSONObject obj = new JSONObject(); //extends HashMap
for (int i = 1; i <= numColumns; ++i) //iterate columns
{
String column_name = metadata.getColumnName(i);
obj.put(column_name, rs.getObject(column_name));
System.out.println("rs.getObject('" + column_name + "')........." + rs.getObject(column_name));
}
json.add(obj);
System.out.println("Added JSON object to JSON Array..");

}

response.setContentType("application/json;charset=UTF-8");
String jsonString = json.toJSONString();
System.out.println("jsonString:" + jsonString);
response.getWriter().write(jsonString);

Map<String, Object> jsonMap = new HashMap<String, Object>();
jsonMap.put("Status", "Success");
jsonMap.put("Rows", 100);

con.close();
} catch (Exception e) {
e.printStackTrace();
}

}
}

 

Create empData.jsp

  <!DOCTYPE html>
<%@ page contentType="text/html;charset=windows-1252"%>
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
         <script type="text/javascript">
          $.ajax( {
              type : "Get", url : "dataservlet", contentType : "application/json", dataType : 'json', data :  {
                  "empID" : "31"
              },
              success : function (data) {
                  $("#mytbl").append('

<tr id="headerRow"></tr>

');
                 /* In below function you can take data[any row number] data[0].
                    (a, b) are (key, value) for 0th json object. as we are shoiwng only Keys so we used only 'a' below.
                 */
                  $.each(data[0], function (a, b) {
                      $("#headerRow").append('

<td style="padding:10px;background:orange;color:white;">' + a + '</td>

');
                  });
                 /* now we have to display data [multiple row with multiple column data].
                 * Here you have to observe one thing is we have multiple rows of data [multiple JSON objects in a JSON Array &
                 * Each JSON object contains multiple key value pairs]. So we must loop through each row and and then loop through each column[key value].
                 * function $.each(data, function(a, b) will get all JSON objects inside JSON Array, where 'a' indicates JSON object number.
                 * 'b' indicates the 'JSON object'. similarly
                 * function $.each(data[a], function(c, d) will get 'a'th JSON object, And here 'c' indicates Key where as 'd' indicates value.
                 * */
                  $.each(data, function (a, b) {
                      $("#mytbl").append('

<tr id="' + a + '"></tr>

');
                      var dataRowId = '#' + a;
                      $.each(data[a], function (c, d) {
                          $(dataRowId).append('

<td style="padding:10px;background:green;color:white;">' + d + '</td>

');
                      });
                  });
              },
              failure : function () {
                  alert("error...");
              }
          });
        </script>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>first</title>
</head>
<body>
<?audit suppress oracle.ide.xml.mismatched-end-tag?>
<table id='mytbl'></table>
<div style="background:Green;width:100%;padding:5px 0px;margin:10px 0px; ">
<p style="color:#fff;margin-left:10px;"> copyrights @teja.com</p>

</div>
</body>
</html>

 

Data in the DB is like below:

1

JSON Array that the servlet returns to Ajax call will be like below.

JSON_Array.png

The JSP page will look like below.

2