Follow

Welcome to TechYari!

Tutorials on webdesign, web development and more...

jQuery autocomplete ajax tutorial with MySql datasource and JSON in J2EE



By  Vipin Dubey     4/14/2014    Labels:,,,,,,, 
In this tutorial I am going to integrate jQuery autocomplete plugin into J2EE application having MySql database. Our input field will receive a JSON response via ajax call to the servlet, which will be displayed as a drop down list when a user types, we will achieve this using jQuery autocomplete plugin. 


Final Application


jquery-autocomplete-demo

Requirements

  • MySql Database ( I use WAMP server as it comes bundled with PhpMyAdmin for MySQL database)
  • Eclipse IDE for J2EE ( it's my preference)
  • JDK 1.7
  • Apache Tomcat 7 (As we will be using annotation based configuration for our servlets)
  • gson 2.2.1 library for JSON conversion
  • mysql connector jar for jdbc connection

Project Structure

Below is a how your project should look after completion. To begin open your Eclipse IDE and create a new Dynamic Web Project ( don't forget to use web module version 3 to support annotations ). 

jquery-autocomplete-project-structure

Database and table setup

In order to move forward launch your favorite tool and create a dummy database with a single table called user with two columns (id,name) and populate the table with some dummy data. 

Creating the home page with autocomplete 

Our home page will feature a text box which is going to be populated by values retrieved from database using autocomplete plugin.This file requires 4 external files to work as expected ( jquery, jquery ui, jquery ui css, style.css and search.js )


<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>jQuery Autocomplete example with J2EE and MySql database</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
<link rel="stylesheet" href="style.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
<script src="search.js"></script>
</head>
<body>
 <div class="wrapper">
 <div class="header">
  <h1>jQuery Autocomplete example with J2EE, JSON and MySql database</h1>
 </div>
 
 <div class="container">
  <div class="instruction">
   <h5>Search for Ajay, Eva or type any character between a to i.</h5>
   <div class="search-container">
    <div class="ui-widget">
     <input type="text" id="search" name="search" class="search" placeholder="search here"/>
    </div>   
   </div>
  </div>
 </div>
 
 <div class="footer">
  &copy; 2014 <a href="http://techyari.in">TechYari</a> All rights reserved. 
 </div>
 </div>
</body>
</html>





Styling the page with css

In order to style our home page we will use css, copy and paste the code given below :

<style type="text/css">
body{
 background-color: #e6e6e6;
 font-family: Helvetica;
 font-size: 17px;
 color: #666;
 margin: 0px;
 padding: 0px;
}
.wrapper{ 
  width:1024px;
     margin: 0 auto;
     background:white;
     }
     
.header{padding:10px;background-color:#4D82CA;color:white}     

h1{
 text-align:center;
 font-size:20px;
 margin:0px;
}

.container{
 min-height:400px;
 border-top: 1px solid gray;
 padding: 50px;
 }
.center{margin-left:43%}

.footer{ text-align:center;border-top: 1px solid gray;padding:10px;background-color:#4D82CA;color:white }

.footer a {color:lavender;text-decoration:none;}
</style>


Getting user data from database using ajax and applying autocomplete

Search.js is being used here to create ajax request to the server/servlet/controller and apply the response to the input box using jQuery autocomplete plugin.


<script type="text/javascript">
  $(function() {
    $( "#search" ).autocomplete({
     source: function (request, response) {
            $.ajax({
                url: "SearchController",
                dataType: 'json',
                data: request,
                success: function (data) {
                 if (typeof Array.prototype.forEach != 'function') {
         Array.prototype.forEach = function(callback){
           for (var i = 0; i < this.length; i++){
             callback.apply(this, [this[i], i, this]);
           }
         };
     }

     var values = data;
     var newArray = new Array(values.length);
     var i = 0;
      values.forEach(function (entry) {
                     var newObject = {
                         label: entry.name
                     };
                     newArray[i] = newObject;
                     i++;
                 });

       response(newArray);
                }   
            }); 
        },  
        minLength: 1
    });
  });
</script>


Creating the controller to handle ajax calls

When a user types a character in text box ,jQuery will fire an ajax request using autocomplete plugin to the controller,  our controller will call the user data access object which in turn creates the necessary connection and returns the user data back as an array list to the controller. After getting the data we convert it to json format and return it back to the success function of our ajax call. Create an servlet named SearcController, use below code for reference.  


package in.techyari.tutorial.controller;

import in.techyari.tutorial.dao.UserDAO;
import in.techyari.tutorial.model.User;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;

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 com.google.gson.Gson;

/**
 * Servlet implementation class SearchController
 */
@WebServlet("/SearchController")
public class SearchController extends HttpServlet {
 private static final long serialVersionUID = 1L;

    /**
     * Default constructor. 
     */
    public SearchController() {
  
    }

 /**
  * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
  */
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 
  String text = request.getParameter("term");
  System.out.println("Hello from Get Method: " +text);
  UserDAO userDAO = new UserDAO();
  try {
   ArrayList<User> users  = userDAO.getUsers(text);
   for(User user: users){
    System.out.println(user.getName());
   }
   
   String searchResult = new Gson().toJson(users);
   
   PrintWriter writer = response.getWriter();
   writer.write(searchResult);
  
  }catch(Exception e){
   e.printStackTrace();
  }
  
 }

 

}


Connecting to database using JDBC

We want to connect to MySql database using jdbc. Create a file called Database.java and replace the url, username and password values with corresponding values of your database.


package in.techyari.tutorial.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Database {
 
 public Connection getConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
  
  Connection connection =  null;
  
  // Store the database URL in a string
  String url = "jdbc:mysql://localhost:3306/searchtutorial"; 
  
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  
  // set the url, username and password for the databse 
  connection = DriverManager.getConnection(url,"root","");   
  return connection;
  
 }
}



Craeting User model class

We are following a forced MVC pattern in this tutorial so we need a model for our user class. Use below code and create a class named User.java


package in.techyari.tutorial.model;

public class User {
 
 private String id;
 private String name;
 
 
 public String getId() {
  return id;
 }

 public void setId(String id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }
 
}


Creating methods to get User data from database

Finally we need a data access object class called UserDAO.java which will interact with mysql database and return the data for autocomplete query made by ajax request. We have used sql like query here to fetch all the matching records for input characters.


package in.techyari.tutorial.dao;

import in.techyari.tutorial.model.User;
import in.techyari.tutorial.dao.Database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.PreparedStatement;


public class UserDAO {
 
 public ArrayList getUsers(String s) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
  
  ArrayList userList = new ArrayList();
  Database db = new Database();
  Connection connection = db.getConnection();
  
  try{
   PreparedStatement ps = connection.prepareStatement("SELECT * FROM user WHERE name like ?");
   ps.setString(1, "%" +s+"%");
   ResultSet rs = ps.executeQuery();
   
   while(rs.next()){
    User user = new User();
    user.setId(rs.getString("id"));
    user.setName(rs.getString("name"));
    userList.add(user);
   }
  }catch(Exception e){
   e.printStackTrace();
  }
  return userList;
 }
}

<.pre>

This is it. We have successfully integrated jQuery autocomplete with J2EE application.

You can download the EAR file which contains the source code from the download link give below:

 Download Source

Like and share this tutorial if you find it useful, kindly use the comment box if you have any queries or suggestions.

About Vipin Dubey

A web developer, blogger, amateur designer and an open source enthusiast.

No comments:

Post a Comment


Contact Form

Name

Email *

Message *

Labels

Follow TechYari on

Translate

About TechYari

Tech Yari is a Technical Blog featuring articles on Web Development, Web Design, Search Engine Optimization, Social Media Marketting and Programming.