jQuery Datatable Ajax Tutorial With Example Project

Introduction

Hi,
The goal of this blog is to show how you can implement fully functional, high-performance tables using the jQuery DataTables plug-in.

Basically, There are many ways to get your data into DataTables, and if you are working with large databases, you might want to consider using the server-side options that DataTables provides. Basically all of the filtering, paging, sorting, changing page length, etc. and DataTables is just an event and display module. The preferred two ways for JQuery Datatable processing are Client-side processing and Server-side processing. This is a JavaScript plug-in implemented using the jQuery library that handles all the necessary interaction with the user on the client-side. So, Here I came up with a blog for jQuery Datatable as I really liked this control. This article shows how the jQuery DataTables plug-in can be integrated into Java web applications.

This sample shows how you can generate a table of students and add the jQuery DataTables plug-in to the HTML table. In that case, Everything is implemented using JavaScript functionalities so you do not need to do anything else. The only thing you need to do is generate a plain table as in the initial case. The new, enhanced table is shown in the following figure:

Using the code

This sample shows how you can generate a table of Students and add the jQuery DataTables plug-in to the HTML table. The code is logically organized in the MVC structure:

1. Model represents classes that contain data and that will be shown in the browser.
2. View are pages that are used to show data to the client – in this example, a JSP and a plain HTML page are used as views.
3. Controller is the logic of the application and represents servlets that handle web requests and utility functions.

There is also one utility package containing classes transforming Java objects into JSON (this is required because the jQuery DataTables plug-in communicates with server-side code via JSON objects but not required for client-side) and We are using it with both Client-side and Server-side.

The Model is a Student class that contains the following properties:
1. Id of the Student
2. Name of the Student
3. Gender
4. Address
5. Grade

In this example, I have used a MYSQL Database named MyDB(for create command of database and student table I have attached studentDB.sql file. Please use this file for creation of database and student table), so I have used class called StudentModel (POJO) that contains a setter-getter for above student parameters as follows :

StudentModel.java

package com;

public class StudentModel {

private long sttudentId;

private String firstName;

private String lastName;

private String address;

private String gender;

private String grade;

public long getSttudentId() {
return sttudentId;
}

public void setSttudentId(long sttudentId) {
this.sttudentId = sttudentId;
}

public String getFirstName() {

return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

public String getGender() {
return gender;
}

public void setGender(String gender) {
this.gender = gender;
}

public String getGrade() {
return grade;
}

public void setGrade(String grade) {
this.grade = grade;
}

}

The following sections are presented with two major cases of usage of DataTables in Java web applications.

A) Client side processing mode ( Default usage )

In this default mode, a minimum amount of code is required – the web server can generate a plain HTML table in standard format. The client-side JavaScript component will use whatever gets generated and add client-side functionalities. In this client-side mode, DataTables takes all the table rows from the

<tbody></tbody>

section and performs filtering, paging, and sorting directly on these elements as with in-memory objects. This is the fastest way to use DataTables, but it requires that the server returns all data in a single call, loads all these rows as in-memory JavaScript objects, and renders them dynamically in DOM. This might cause performance issues with the server call, and memory usage on the client. However, this minimizes the number of requests sent to the server because once the table is loaded, the server is not used at all.

This is the small code I have Generated for Jquery Datatable to be implememted:

1) table-demo.js

var studentTable;

jQuery(document).ready(function() {

studentTable = jQuery('#studentListTable').dataTable({
"bJQueryUI" : true,
"sPaginationType" : "full_numbers",
"bRetrieve" : true,
"bFilter" : true,
"iDisplayLength": 10,
"bProcessing" : true,
"bServerSide" : false,
"aoColumns" : [ { "bSearchable" : false,"bVisible" : false,
"asSorting" : [ "asc" ] },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true }
]
});

jQuery(".ui-corner-br").addClass('ui-widget-header_custom ');
});

In the document-ready JavaScript event handler, the plain generated table is enhanced with the jQuery DataTables plug-in. There are parameters that are passed to the plug-in initialization function (these are parameters I always use):

1. sPagination- Instructing the plug-in to generate pagination with numbers instead of two previous-next buttons, as is generated by default.
2. bJQueryUI- Applying standard jQueryUI styles.
3. sajaxSource- You can instruct DataTables to load data from an external source using this parameter (use aData if you want to pass data in you already have). Simply provide a url a JSON object can be obtained from. This object must include the parameter ‘aaData’ which is the data source for the table.
4. bRetrieve- Retrieve the DataTables object for the given selector.
5. bProcessing- Enable or disable the display of a ‘processing’ indicator when the table is being processed .
6. bServerSide- Configure DataTables to use server-side processing. Note that the sAjaxSource parameter must also be given in order to give DataTables a source to obtain the required data for each draw.
7. aoColumns- This array allows you to target a specific column, multiple columns, or all columns, using the aTargets property of each object in the array.
8. bsortable- Enable or disable sorting on this column.
9. sWidth- Defining the width of the column.
10. bSearchable- Enable or Disable filtering on the data in this column.
11. bVisible- Enable or Disable the display of this column.
12. asSorting- You can control the default sorting direction, and even alter the behaviour of the sort handler(i.e. Only aloow ascending sort etc. ) using this parameter.

As mentioned above, the jQuery DataTables plug-in can be applied on a static HTML structure in the browser. I have generated a table structure using a JSP page to make a call to above table-demo.js shown in the listing below:

2) table-demo.jsp

<%@ page
import="com.*,java.util.*"%><!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript">
var basePath = &#039;${pageContext.request.contextPath}&#039;;
</script>
<link type="text/css" rel="stylesheet" media="all" href="${pageContext.request.contextPath}/assets/datatable/css/demo_table_jui.css" >
<link type="text/css" rel="stylesheet" media="all" href="${pageContext.request.contextPath}/assets/jquery-ui/css/redmond/jquery-ui-1.8.11.custom.css" >
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/js/jquery-1.5.2.min.js" ></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/js/jquery-ajax-form-plugin/jquery.form.js" ></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/datatable/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/jquery-ui/js/jquery-ui-1.8.11.custom.min.js" ></script>

<script type="text/javascript" src="${pageContext.request.contextPath}/assets/js/table-demo.js"></script>

<title>Student Form</title>
</head>
<body>
<form>
<div class="titleDiv">Student List</div>
<div class="clearfix"></div>
<div class="formDiv">
<table border="0" margin="0" padding="0" width="100%"
class="dataTables_wrapper" id="studentListTable">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Address</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
<%
if(request.getAttribute("studentList") != null){
List<StudentModel> studentList = (List<StudentModel>)
request.getAttribute("studentList");
%>
<%
if( studentList != null ){
for (StudentModel student: studentList){
%>
<tr>
<td><%=student.getSttudentId()%></td>
<td><%=student.getFirstName()%></td>
<td><%=student.getLastName()%></td>
<td><%=student.getGender()%></td>
<td><%=student.getAddress()%></td>
<td><%=student.getGrade()%></td>

</tr>
<%}
}

}%>

</tbody>
</table>
</div>
</form>
</body>
</html>

In this JSP page is included all the necessary JavaScript libraries, and HTML code for the student table is generated. For demonstration purposes, a simple loop generates a TR for each student that is returned by the TableDemoAction class. However, in your applications, you can use any server-side processing you want (JavaBeans, JSTL components, etc.) because the jQuery DataTables plug-in is independent of the applied server-side technology.

This is my action (controller) class called TableDemoAction which make a call to list of students which in turn returns the list of students. Also forword request to JSP page table-demo.jsp.

3) TableDemoAction.java

package com;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@SuppressWarnings("serial")
public class TableDemoAction extends HttpServlet {

private String STUDENT_ID_LIST = "studentList";

public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {

List<StudentModel> studentList = null;
try {
studentList = selectStudentInfo();
} catch (SQLException e) {
e.printStackTrace();
}

String destination = "/WEB-INF/views/com/table-demo.jsp";
request.setAttribute(STUDENT_ID_LIST, studentList);

RequestDispatcher rd = request.getRequestDispatcher(destination);
rd.forward(request, response);

}

public List<StudentModel> selectStudentInfo() throws SQLException {

List<StudentModel> al = new ArrayList<StudentModel>();

// @formatter:off

String query =

" SELECT id,firstName,lastName,gender,address,grade " +

" FROM " +

" student " +

" order by id asc ";

// @formatter:on
String connectionURL = "jdbc:mysql://localhost:3306/MyDB?
user=root&password=mobisoft";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = DriverManager.getConnection(connectionURL);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

while (resultSet.next()) {

StudentModel student = new StudentModel();
student.setSttudentId(resultSet.getLong("id"));
student.setFirstName(resultSet.getString("firstName"));
student.setLastName(resultSet.getString("lastName"));
student.setGender(resultSet.getString("gender"));
student.setAddress(resultSet.getString("address"));
student.setGrade(resultSet.getString("grade"));

al.add(student);
}

resultSet.close();
statement.close();

return al;

}

}

Instead of the plain HTML table, the following component is shown on the client side:

All actions you see in the component are implemented on the client-side (e.g., when you enter text in the text box, TR elements are filtered). This is the fastest way for the user, under the assumption that the time required to load the table is not too big. If you have a huge amount of data, you might consider using DataTables in AJAX mode where only partial results are returned to the plug-in.

You can see a complete list of additional DataTable plugins on the DataTables site. All these plug-ins are additional add-ons for DataTables, but you can also use the powerful DataTables API to customize behavior. An example of the plain table enhanced with a few mentioned plugins is shown in the following figure:

If you have a reasonable amount of records in the table you do not need to worry about these functionalities. Just render a plain table and apply some combination of plug-ins that you need.

However, if you have a lot of records that are shown in the table and you want to avoid complex JavaScript processing in the browser, you can implement the processing logic on the server-side (e.g., in some servlet), and setup DataTables to query the server via AJAX calls in order to take information that should be displayed. This mode is described in the following section.

do my essay for me free

B) Jquery Datatable with Server-side processing

The jQuery DataTables plug-in is an excellent client-side component that can be used to create rich-functional tables in a web browser. This plug-in adds a lot of functionalities to plain HTML tables. Although, by default it is pure client side, it might be configured to use data from the server via AJAX calls in order to improve performance. However, to integrate DataTables with server-side code, the developer must know the protocols and parameters that are sent by DataTables and how to use them on the server side.

In this mode, instead of taking the complete page at once, several smaller requests are sent whenever new information is required, and minimal amount of data is returned from the server. This example, calls the /DatatableDemoAction URL and sends information about the student.

Lets Start with the Jquery :
This is the small amount of code that I have Generated for Jquery Datatable with Server-Side Processing to be implememted:

1) datatable-demo.js

var studentTable;

jQuery(document).ready(function() {

studentTable = jQuery('#studentListTable').dataTable({

"bJQueryUI" : true,
"sPaginationType" : "full_numbers",
"iDisplayLength": 10,
"bProcessing" : true,
"bServerSide" : true,
"sAjaxSource" : basePath +"/com/DatatableDemoAction.java",

"aoColumns" : [{"bSearchable" : false, "bVisible" : false, "asSorting" : [ "asc" ] },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "10%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true },
{"sWidth" : "20%","bSortable" : true }
]
});
jQuery(".ui-corner-br").addClass('ui-widget-header_custom ');

});

In the document-ready JavaScript event handler, the plain generated table is enhanced with the jQuery DataTables plug-in. The parameters that are passed to the plug-in initialization function I already have discussed above the only paramter I would like to tell you is :
1. bServerSide- Configure DataTables to use server-side processing. Note that the sAjaxSource parameter must also be given in order to give DataTables a source to obtain the required data for each draw. It must be true for server-side processing.
As mentioned above, the jQuery DataTables plug-in can be applied on a static HTML structure in the browser. I have generated a table structure using a JSP page to make a call to above datatable-demo.js shown in the listing below:

2) datatable-demo.jsp

<%@ page
import="java.util.*"%><!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript">
var basePath = &#039;${pageContext.request.contextPath}&#039;;
</script>
<link type="text/css" rel="stylesheet" media="all" href="${pageContext.request.contextPath}/assets/datatable/css/demo_table_jui.css" >
<link type="text/css" rel="stylesheet" media="all" href="${pageContext.request.contextPath}/assets/jquery-ui/css/redmond/jquery-ui-1.8.11.custom.css" >
<script type="text/javascript" src=" $pageContext.request.contextPath}/assets/js/jquery-1.5.2.min.js" ></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/js/jquery-ajax-form-plugin/jquery.form.js" ></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/datatable/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/assets/jquery-ui/js/jquery-ui-1.8.11.custom.min.js" ></script>

<script type="text/javascript" src="${pageContext.request.contextPath}/assets/js/datatable-demo.js"></script>

<title>Student Form</title>
</head>
<body>
<form>
<div class="titleDiv">Student List</div>
<div class="clearfix"></div>

<div class="formDiv">
<table border="0" margin="0" padding="0" width="100%"
class="dataTables_wrapper" id="studentListTable">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Address</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</form>
</body>
</html>

In this JSP page is included all the necessary JavaScript libraries, and HTML code for the student table to be generated. For demonstration purposes, I have used server-side processing because the jQuery DataTables plug-in is independent of the applied server-side technology.

In the Third step we have created class DemoAction.java which makes a call to the JSP page (datatable-demo.jsp) which in turn call to JS file (datatable-demo.js) and this JS in turn makes call to DatatableDemoAction.java.

3) DemoAction.java

package com;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@SuppressWarnings("serial")
public class DemoAction extends HttpServlet {

@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {

String destination = "/WEB-INF/views/com/datatable-demo.jsp";

RequestDispatcher rd = request.getRequestDispatcher(destination);
rd.forward(request, response);
}

}

Last step would be to create a handler/web service which we would be passing in the argument sAjaxSource of datatable. Remember that the response should be a JSON formatted text as datatable only reads JSON in aData parameter.

4) DatatableDemoAction.java

package com;

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.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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

@SuppressWarnings("serial")
public class DatatableDemoAction extends HttpServlet {

private String SEARCH_TERM;

private String COL_NAME;

private String DIR;

private int START;

private int AMOUNT;

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

String[] cols = { "id", "firstName", "lastName", "gender", "address",
"grade" };

JSONObject result = new JSONObject();
int amount = 10;
int start = 0;
int col = 0;
String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");

if (sStart != null) {
start = Integer.parseInt(sStart);
if (start < 0) {
start = 0;
}
}
if (sAmount != null) {
amount = Integer.parseInt(sAmount);
if (amount < 10 || amount > 50) {
amount = 10;
}
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 5)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}

String colName = cols[col];
int total = -1;
try {
total = getTotalRecordCount();
} catch (SQLException e1) {
e1.printStackTrace();
}

AMOUNT = amount;
SEARCH_TERM = request.getParameter("sSearch");
COL_NAME = colName;
DIR = dir;
START = start;

try {
result = getStudentData(total, request);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
PrintWriter out = response.getWriter();

out.print(result);

}

public JSONObject getStudentData(int total, HttpServletRequest request)
throws SQLException, ClassNotFoundException {

int totalAfterFilter = total;
JSONObject result = new JSONObject();
JSONArray array = new JSONArray();
String searchSQL = "";

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String connectionURL = "jdbc:mysql://localhost:3306/MyDB?
user=root&password=mobisoft";
Connection connection = DriverManager.getConnection(connectionURL);
// @formatter:off
String sql = "SELECT " + "id, firstName, lastName, gender, address, "
+ "grade " + "FROM " + "student " + "WHERE "
+ "is_deleted = ? ";

// @formatter:on

String globeSearch = " AND (firstName like &#039;%" + SEARCH_TERM + "%&#039;"
+ "or lastName like &#039;%" + SEARCH_TERM + "%&#039;"
+ "or gender like &#039;%" + SEARCH_TERM + "%&#039;"
+ "or address like &#039;%" + SEARCH_TERM + "%&#039;"
+ "or grade like &#039;%" + SEARCH_TERM + "%&#039;)";

if (SEARCH_TERM != "") {
searchSQL = globeSearch;
}

sql += searchSQL;
sql += " order by " + COL_NAME + " " + DIR;

sql += " limit " + START + ", " + AMOUNT;

PreparedStatement statement = connection.prepareStatement(sql);
statement.setBoolean(1, false);

ResultSet rs = statement.executeQuery();

while (rs.next()) {
JSONArray ja = new JSONArray();

ja.put(rs.getString("id"));
ja.put(rs.getString("firstName"));
ja.put(rs.getString("lastName"));
ja.put(rs.getString("gender"));
ja.put(rs.getString("address"));
ja.put(rs.getString("grade"));

array.put(ja);
}
statement.close();
rs.close();

// @formatter:off
String query = "SELECT " + "COUNT(*) as count " + "FROM " + "student "
+ "WHERE " + "is_deleted = ? ";
// @formatter:on

if (SEARCH_TERM != "") {
query += searchSQL;

PreparedStatement stmt = connection.prepareStatement(query);
stmt.setBoolean(1, false);

ResultSet resultSet = stmt.executeQuery();

if (resultSet.next()) {
totalAfterFilter = resultSet.getInt("count");
}
stmt.close();
resultSet.close();
connection.close();
}
try {
result.put("iTotalRecords", total);
result.put("iTotalDisplayRecords", totalAfterFilter);
result.put("aaData", array);
} catch (Exception e) {

}

return result;
}

public int getTotalRecordCount() throws SQLException {

int total = -1;
// @formatter:off
String sql = "SELECT " + "COUNT(*) as count " + "FROM " + "student "
+ "WHERE " + "is_deleted = ? ";

// @formatter:on

String connectionURL = "jdbc:mysql://localhost:3306/MyDB?
user=root&password=mobisoft";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = DriverManager.getConnection(connectionURL);

PreparedStatement statement = connection.prepareStatement(sql);

statement.setBoolean(1, false);
ResultSet resultSet = statement.executeQuery();

if (resultSet.next()) {
total = resultSet.getInt("count");
}
resultSet.close();
statement.close();
connection.close();

return total;
}

}

Parameters sent to the server

The following information is sent to the server for each draw request. Your server-side script must use this information to obtain the data required for the draw.

1. iDisplayStart -Define the starting point for data display when using DataTables with pagination.
2. iDisplayLength – Number of rows to display on a single page when using pagination.
3. iSortCol_(int) – Column being sorted on (you will need to decode this number for your database).
4. sSortDir_(int) – Direction to be sorted – “desc” or “asc”.
5. sSearch – Global search field.
6. iColumns- Number of columns being displayed (useful for getting individual column search info).

Reply from the server

In reply to each request for information that DataTables makes to the server, it expects to get a well formed JSON object with the following parameters.
1. iTotalRecords- Total records, before filtering (i.e. the total number of records in the database).
2. iTotalDisplayRecords- Total records, after filtering (i.e. the total number of records after filtering has been applied – not just the number of records being returned in this result set).
3. aaData- The data in a 2D array. Note that you can change the name of this parameter with sAjaxDataProp.

All actions you see in the component are implemented on the Server-side. You can see a complete list of additional DataTable plugins on the DataTables site. All these plug-ins are additional add-ons for DataTables, but you can also use the powerful DataTables API to customize behavior. An example of the plain table enhanced with a few mentioned plugins is shown in the following figure:

In this example we have used the JSON library for serializing Java objects to JSON, but you can use any other JSON serialization library instead.

You can find some example on the DataTables site. The only important thing is that you return a JSON response with objects that match the current state on the table.

Conclusion

This example shows how you can create effective, fully functional tables in a Java web application using the jQuery DataTables plug-in. Using the code examples in the article, you can significantly enhance the look and functionalities of your web application. I recommend that you try it – when you integrate jQuery DataTables in a few tables in your application, you will see that the implementation is straightforward and that you will be, with some practice, able to implement a lot of functionalities with very little effort.

You can download the code example in the attached project that was created in the Eclipse Java EE IDE for Web Developers, that runs on a Tomcat 6.0 web server and I have used Ant for project deployment. I suggest you create a new project, configure it with your tomcat (no need to go with Ant if you do not the setting of Ant, so remove that build.properties file and build.xml file and configure this project with your local tomcat setting), and add classes that you can find in the project. I hope that this example will help you to create better table interfaces.

You can download the Datatable source code from here.

Author's Bio:

Pritam Barhate