Datatables server side processing in Java

This post explains implementing datatables server side processing in java. Check out Datatables with Spring MVC example from my previous blogs to understand in details. In this post I will be explaining server side processing on datatables, that is loading data on demand in datatable using AJAX.

The final structure of our application will be like –

Database Table Structure

Following data is already available in table “students“.

Step 1: Create a Dynamic Web Project and add dependencies and jars in the project’s lib folder present in WEB-INF folder. Here we will need two additional jars apart from Spring jars – mysql connector jar and json-object jar.

JSP View Pages

Step 2: Create welcome.jsp page and myDatatables.jsp page

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>code2java - Datatables Server Side Processing</title>
</head>
<body>
	<label>Welcome to code2java.com</label>
	<br>
	<!-- redirecting to Spring Controller to load the Data table page -->
	<a href="loadDatatable.do">Load the Datatable</a>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>code2java - Datatable Server Side Processing</title>

<!-- Below are the Style Sheets required for Data Tables. These can be customized as required -->
<link rel="stylesheet" type="text/css" href="css/db_site_ui.css">
<link rel="stylesheet" type="text/css" href="css/demo_table_jui.css">
<link rel="stylesheet" type="text/css" href="css/jquery-ui.css">

<!-- Below is the jQuery file required for using any Jquery component. -->
<script type="text/javascript" src="js/jquery-1.11.3.js"></script>

<!-- Below are the jQuery scripts required for Data Tables. -->
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>

<!-- Initialization code of data table at the time of page load. -->
<script>
	$(document).ready(function() {
		$('#myDatatable').DataTable({
			"jQueryUI" : true,
			"pagingType" : "full_numbers",
			"lengthMenu" : [ [ 5, 10, 50, -1 ], [ 5, 10, 50, "All" ] ],
			"processing": true,
			"bServerSide": true,
	        "sAjaxSource" : "loadServerSideData.do",
	        "sServerMethod": "POST"
		});
	});
</script>
</head>
<body>
	<h1>www.code2java.com</h1>
	<h3>Datatable Server Side Processing</h3>
	<div style="width: 80%">
		<!-- Below table will be displayed as Data table -->
		<table id="myDatatable" class="display datatable">
			<thead>
				<tr>
					<th>Sr. No.</th>
					<th>Name</th>
					<th>Email</th>
					<th>City</th>
					<th>Country</th>
				</tr>
			</thead>
			<!-- No need to add body tag here :) -->
		</table>
	</div>
</body>
</html>

In the above JSP, we have added an ajax callback in the data table initialization code. This means while initialization of data table an ajax call will be sent to the controller (loadServerSideData.do) to fetch the data to be loaded in Data Tables. Please make a note, only required data will be fetched and not the complete data.

XML configuration files

Step 3: Configuration files will be –

Web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://java.sun.com/xml/ns/javaee"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
	id="WebApp_ID" version="3.0">
	<display-name>DataTableWithSpring</display-name>
	<servlet>
		<servlet-name>dispatcher</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>dispatcher</servlet-name>
		<url-pattern>*.do</url-pattern>
	</servlet-mapping>

	<welcome-file-list>
		<welcome-file>/WEB-INF/view/welcome.jsp</welcome-file>
	</welcome-file-list>
</web-app>

dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<context:annotation-config />

	<context:component-scan base-package="com.code2java" />

	<bean id="jspViewResolver"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="viewClass"
			value="org.springframework.web.servlet.view.JstlView" />
		<property name="prefix" value="/WEB-INF/view/" />
		<property name="suffix" value=".jsp" />
	</bean>

	<!-- This is Data Base configuration for MySQL -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/code2java" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

</beans>

Controller, Service and DAO classes

Step 4: We will now add the controller class

package com.code2java.controllers;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.code2java.service.DTServiceImpl;


@Controller
public class DTController {
	
	@Autowired
	private DTServiceImpl idtService;
	
	/**
	 * @author code2java
	 * This method will be called when user clicks on link showed on welcome.jsp page
	 * The request is mapped using @RequestMapping annotation. 
	 * @return
	 */
	@RequestMapping(value = "/loadDatatable")
	public ModelAndView loadDatatable()
	{
		/* This will load the myDatatable.jsp page */
		return new ModelAndView("myDatatable");
		
	}
	
	/**
	 * This method will be called from AJAX callback of Data Tables 
	 * 
	 * @param response
	 * @param request
	 * @return
	 */
	@ResponseBody
	@RequestMapping("/loadServerSideData")
	public String loadServerSideData(HttpServletResponse response, HttpServletRequest request)
	{
		/* getting the JSON response to load in data table*/
		String jsonResponse = idtService.getDataTableResponse(request);
		
		/* Setting the response type as JSON */
		response.setContentType("application/json");
		response.setHeader("Cache-Control", "no-store");

		return jsonResponse;
	}

}

Step 5: Now we will create the service method and its implementation.

package com.code2java.service;

import javax.servlet.http.HttpServletRequest;

public interface IDTService {
	
	public String getDataTableResponse(HttpServletRequest request);

}

package com.code2java.service;

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

import javax.servlet.http.HttpServletRequest;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.code2java.dao.DataTableDAO;

@Service("idtService")
public class DTServiceImpl implements IDTService{

	@Autowired
	private DataTableDAO dataTableDao;

	@Override
	public String getDataTableResponse(HttpServletRequest request) {

		String[] cols = { "id", "name", "email", "city", "country" };
		String table = "students";

		/* Response will be a String of JSONObject type */
		JSONObject result = new JSONObject();

		/* JSON Array to store each row of the data table */
		JSONArray array = new JSONArray();

		int amount = 5; /* Amount in Show Entry drop down */
		int start = 0; /* Counter for Paging, initially 0 */
		int echo = 0; /* Maintains the request number, initially 0 */
		int col = 0; /* Column number in the datatable */

		String id = "";
		String name = "";
		String email = "";
		String city = "";
		String country = "";

		/* Below variables store the options to create the Query */
		String dir = "asc";
		String sStart = request.getParameter("iDisplayStart");
		String sAmount = request.getParameter("iDisplayLength");
		String sEcho = request.getParameter("sEcho");
		String sCol = request.getParameter("iSortCol_0");
		String sdir = request.getParameter("sSortDir_0");

		/* Below will be used when Search per column is used. In this example we are using common search. */
		id = request.getParameter("sSearch_0");
		name = request.getParameter("sSearch_1");
		email = request.getParameter("sSearch_2");
		city = request.getParameter("sSearch_3");
		country = request.getParameter("sSearch_4");

		List<String> sArray = new ArrayList<String>();
		if (!id.equals("")) {
			String sId = " id like '%" + id + "%'";
			sArray.add(sId);
		}
		if (!name.isEmpty()) {
			String sName = " name like '%" + name + "%'";
			sArray.add(sName);
		}
		if (!email.isEmpty()) {
			String sEmail = " email like '%" + email + "%'";
			sArray.add(sEmail);
		}
		if (!city.isEmpty()) {
			String sCity = " city like '%" + city + "%'";
			sArray.add(sCity);
		}
		if (!country.isEmpty()) {
			String sCountry = " country like '%" + country + "%'";
			sArray.add(sCountry);
		}

		String individualSearch = "";
		if(sArray.size()==1)
		{
			individualSearch = sArray.get(0);
		}
		else if(sArray.size()>1)
		{
			for(int i=0;i<sArray.size()-1;i++)
			{
				individualSearch += sArray.get(i)+ " and ";
			}
			individualSearch += sArray.get(sArray.size()-1);
		}

		/* Start value from which the records need to be fetched */
		if (sStart != null) {
			start = Integer.parseInt(sStart);
			if (start < 0)
				start = 0;
		}

		/* Total number of records to be fetched */
		if (sAmount != null) {
			amount = Integer.parseInt(sAmount);
			if (amount < 5 || amount > 100)
				amount = 5;
		}

		/* Counter of the request sent from Data table */
		if (sEcho != null) {
			echo = Integer.parseInt(sEcho);
		}

		/* Column number */
		if (sCol != null) {
			col = Integer.parseInt(sCol);
			if (col < 0 || col > 5)
				col = 0;
		}

		/* Sorting order */
		if (sdir != null) {
			if (!sdir.equals("asc"))
				dir = "desc";
		}
		String colName = cols[col];

		/* This is show the total count of records in Data base table */
		int total = 0;
		Connection conn = dataTableDao.getConnection();
		try 
		{
			String sql = "SELECT count(*) FROM "+ table;
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next())
			{
				total = rs.getInt("count(*)");
			}
		}catch(Exception e){
			e.printStackTrace();
		}

		/* This is total number of records that is available for the specific search query */
		int totalAfterFilter = total;

		try {
			String searchSQL = "";
			String sql = "SELECT * FROM "+table;
			String searchTerm = request.getParameter("sSearch");
			String globeSearch =  " where (id like '%"+searchTerm+"%'"
					+ " or name like '%"+searchTerm+"%'"
					+ " or email like '%"+searchTerm+"%'"
					+ " or city like '%"+searchTerm+"%'"
					+ " or country like '%"+searchTerm+"%')";
			if(searchTerm!=""&&individualSearch!=""){
				searchSQL = globeSearch + " and " + individualSearch;
			}
			else if(individualSearch!=""){
				searchSQL = " where " + individualSearch;
			}else if(searchTerm!=""){
				searchSQL=globeSearch;
			}
			sql += searchSQL;
			sql += " order by " + colName + " " + dir;
			sql += " limit " + start + ", " + amount;

			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				JSONArray ja = new JSONArray();
				ja.put(rs.getString("id"));
				ja.put(rs.getString("name"));
				ja.put(rs.getString("email"));
				ja.put(rs.getString("city"));
				ja.put(rs.getString("country"));
				array.put(ja);
			}
			String sql2 = "SELECT count(*) FROM "+table;
			if (searchTerm != "") {
				sql2 += searchSQL;
				PreparedStatement ps2 = conn.prepareStatement(sql2);
				ResultSet rs2 = ps2.executeQuery();
				if (rs2.next()) {
					totalAfterFilter = rs2.getInt("count(*)");
				}
			}
			result.put("iTotalRecords", total);
			result.put("iTotalDisplayRecords", totalAfterFilter);
			result.put("aaData", array);
			result.put("sEcho", echo);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result.toString();
	}

}

Step 6: Lets create the DAO layer to get the connection object.

package com.code2java.dao;

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

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository("dataTableDao")
public class DataTableDAO {

	@Autowired
	DataSource dataSource; /* This is configured in dispatcher-servlet.xml file */

	public Connection getConnection()
	{
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return conn;
	}

}

Output

Step 7: Your application is ready to run :). The output pages will be-

Hope this will help you.

DataTableWithSpring complete application.

Regards,

Nikhil Naoghare.

Java

Abstract Class In JAVA

ByNikhil Naoghare June 30, 2010October 4, 2015

Hello Friends, This tutorial is for all the Java followers. One of the best feature that is widely used is the term ‘Abstract’. This term can be used as either class or a simple method. An abstract method is any method that is just declared but not instantiated. In other words one can just create…

10 Comments

free free says:

October 24, 2016 at 3:10 am

thank you very much , it is easy to understand.

1. admin says:
  
  April 13, 2017 at 4:14 pm
  
  Glad that this helped you. Thanks.
  
literateaspects says:

November 12, 2016 at 11:01 pm

Hi Nikhil,

Can this same example also be re-created as a JSF application?

Literate Aspects says:

November 17, 2016 at 1:40 am

Hello, do you have the same with JSF ?

1. admin says:
  
  April 13, 2017 at 4:13 pm
  
  Sorry but I dont have it with JSF. However it wont be that difficult to convert this in JSF application if you get the basics right for JSF.
  
Sujit J says:

April 3, 2017 at 11:06 am

Is there an example without the Spring MVC ?

1. admin says:
  
  April 13, 2017 at 4:11 pm
  
  You can create the same exampled with simple JDBC as well. Instead of Spring framework you shall create the JDBC connection and get the data. Rest all functionality will be same.
  Anything else you want to know specifically?
  
  1. viraj Yerunkar says:
    
    January 25, 2021 at 12:47 pm
    
    Hi I need to add button as well in every row in last column,how will i achieve it??
    
Thomas Bisballe says:

July 21, 2021 at 6:59 pm

The link
DataTableWithSpring complete application.
Is broken can you post where the code is?

1. admin says:
  
  August 13, 2021 at 10:56 am
  
  Hello Thomas,
  PFB the link for your easy reference. Updated in the blog as well.
  https://code2java.com/wp-content/uploads/2019/10/DataTableWithSpring.zip

Datatables server side processing in Java

Database Table Structure

JSP View Pages

XML configuration files

Controller, Service and DAO classes

Output

HTML Hidden Fields.

JAR File : Java Archive File

Abstract Class In JAVA

Form tag in HTML

Threads in Java.

Collections In Java.

10 Comments

Leave a Reply Cancel reply

Database Table Structure

JSP View Pages

XML configuration files

Controller, Service and DAO classes

Output

Related Posts

10 Comments

Leave a Reply Cancel reply