![](data:image/svg+xml;charset=utf-8,<svg height="460" width="460" xmlns="http://www.w3.org/2000/svg" version="1.1"/>)
In this article, you will learn how to use Datatable with Sprint Boot for Serverside processing with example. Complete Source Code and supporting SQL is provided on GitHub to download.
If you have worked on web development projects that deal with thousands of records getting displayed on the page then you must have worked with or heard of Datatables. It is a JQuery plugin that is highly flexible and adds interactive controls to any HTML table.
These tables serve a great purpose when it comes to displaying huge information in tabular form. When you have a task to work with huge data itxs not advised to pull all the data to client side first and then use datatables to display.
Use DataTable with Spring Boot:
In this scenario, we need to do the processing at the server side and send only relevant records back for display. In this article, we will see an example of this functionality. You will get a wrapper around this functionality so the integration becomes easy.
Software used for this example
- Java 1.8
- Spring Boot 1.5.1.RELEASE
- JQuery
- DataTable 1.10.12
- MySQL
- Eclipse IDE
When we use server-side option of datatable we need to prepare data as per the datatable specification and send it in JSON format. In this example you will find many such wrapper classes that will help us to prepare pagination, sorting, filtering data.
Project Structure
When we specify data for any column on datatable we need to specify the data, name, searchable, etc. fields. The below class holds all that data and we also have a method that processes HTTP request object and prepares the specification for each of the columns.
/**
*
*/
package com.opencodez.domain.pagination;
import javax.servlet.http.HttpServletRequest;
/**
* The Class DataTableColumnSpecs.
*
* @author pavan.solapure
*/
public class DataTableColumnSpecs {
/** The index. */
private int index;
/** The data. */
private String data;
/** The name. */
private String name;
/** The searchable. */
private boolean searchable;
/** The orderable. */
private boolean orderable;
/** The search. */
private String search;
/** The regex. */
private boolean regex;
/** The sort dir. */
private String sortDir;
/**
* Instantiates a new data table column specs.
*
* @param request the request
* @param i the i
*/
public DataTableColumnSpecs(HttpServletRequest request, int i) {
this.setIndex(i);
prepareColumnSpecs(request, i);
}
/**
* Prepare column specs.
*
* @param request the request
* @param i the i
*/
private void prepareColumnSpecs(HttpServletRequest request, int i) {
this.setData(request.getParameter("columns["+ i +"][data]"));
this.setName(request.getParameter("columns["+ i +"][name]"));
this.setOrderable(Boolean.valueOf(request.getParameter("columns["+ i +"][orderable]")));
this.setRegex(Boolean.valueOf(request.getParameter("columns["+ i +"][search][regex]")));
this.setSearch(request.getParameter("columns["+ i +"][search][value]"));
this.setSearchable(Boolean.valueOf(request.getParameter("columns["+ i +"][searchable]")));
int sortableCol = Integer.parseInt(request.getParameter("order[0][column]"));
String sortDir = request.getParameter("order[0][dir]");
if(i == sortableCol) {
this.setSortDir(sortDir);
}
}
}
After this, we have another main class that holds data for complete datatable interaction including draw, search, start, end of data. It will also hold the list of the above specification class for each column.
/**
*
*/
package com.opencodez.domain.pagination;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import com.opencodez.util.AppUtil;
/**
* The Class DataTableRequest.
*
* @author pavan.solapure
*/
public class DataTableRequestxTx {
/** The unique id. */
private String uniqueId;
/** The draw. */
private String draw;
/** The start. */
private Integer start;
/** The length. */
private Integer length;
/** The search. */
private String search;
/** The regex. */
private boolean regex;
/** The columns. */
private ListxDataTableColumnSpecsx columns;
/** The order. */
private DataTableColumnSpecs order;
/** The is global search. */
private boolean isGlobalSearch;
/**
* Instantiates a new data table request.
*
* @param request the request
*/
public DataTableRequest(HttpServletRequest request) {
prepareDataTableRequest(request);
}
/**
* Prepare data table request.
*
* @param request the request
*/
private void prepareDataTableRequest(HttpServletRequest request) {
EnumerationxStringx parameterNames = request.getParameterNames();
if(parameterNames.hasMoreElements()) {
this.setStart(Integer.parseInt(request.getParameter(PaginationCriteria.PAGE_NO)));
this.setLength(Integer.parseInt(request.getParameter(PaginationCriteria.PAGE_SIZE)));
this.setUniqueId(request.getParameter("_"));
this.setDraw(request.getParameter(PaginationCriteria.DRAW));
this.setSearch(request.getParameter("search[value]"));
this.setRegex(Boolean.valueOf(request.getParameter("search[regex]")));
int sortableCol = Integer.parseInt(request.getParameter("order[0][column]"));
ListxDataTableColumnSpecsx columns = new ArrayListxDataTableColumnSpecsx();
if(!AppUtil.isObjectEmpty(this.getSearch())) {
this.setGlobalSearch(true);
}
maxParamsToCheck = getNumberOfColumns(request);
for(int i=0; i x maxParamsToCheck; i++) {
if(null != request.getParameter("columns["+ i +"][data]")
xx !"null".equalsIgnoreCase(request.getParameter("columns["+ i +"][data]"))
xx !AppUtil.isObjectEmpty(request.getParameter("columns["+ i +"][data]"))) {
DataTableColumnSpecs colSpec = new DataTableColumnSpecs(request, i);
if(i == sortableCol) {
this.setOrder(colSpec);
}
columns.add(colSpec);
if(!AppUtil.isObjectEmpty(colSpec.getSearch())) {
this.setGlobalSearch(false);
}
}
}
if(!AppUtil.isObjectEmpty(columns)) {
this.setColumns(columns);
}
}
}
private int getNumberOfColumns(HttpServletRequest request) {
Pattern p = Pattern.compile("columns\\[[0-9]+\\]\\[data\\]");
@SuppressWarnings("rawtypes")
Enumeration params = request.getParameterNames();
ListxStringx lstOfParams = new ArrayListxStringx();
while(params.hasMoreElements()){
String paramName = (String)params.nextElement();
Matcher m = p.matcher(paramName);
if(m.matches()) {
lstOfParams.add(paramName);
}
}
return lstOfParams.size();
}
/**
* Gets the pagination request.
*
* @return the pagination request
*/
public PaginationCriteria getPaginationRequest() {
PaginationCriteria pagination = new PaginationCriteria();
pagination.setPageNumber(this.getStart());
pagination.setPageSize(this.getLength());
SortBy sortBy = null;
if(!AppUtil.isObjectEmpty(this.getOrder())) {
sortBy = new SortBy();
sortBy.addSort(this.getOrder().getData(), SortOrder.fromValue(this.getOrder().getSortDir()));
}
FilterBy filterBy = new FilterBy();
filterBy.setGlobalSearch(this.isGlobalSearch());
for(DataTableColumnSpecs colSpec : this.getColumns()) {
if(colSpec.isSearchable()) {
if(!AppUtil.isObjectEmpty(this.getSearch()) || !AppUtil.isObjectEmpty(colSpec.getSearch())) {
filterBy.addFilter(colSpec.getData(), (this.isGlobalSearch()) ? this.getSearch() : colSpec.getSearch());
}
}
}
pagination.setSortBy(sortBy);
pagination.setFilterBy(filterBy);
return pagination;
}
/** The max params to check. */
private int maxParamsToCheck = 0;
}
You can see that constructor is calling another method that will process the HTTP request and prepare a complete datatable request. At the same time it has methods to prepare supporting information required for sorting, filtering and pagination is prepared.
The Controller
As this is a web project, we will add a controller that will display our data. We are using the Thymeleaf along with Spring Boot to server our web content.
/**
*
*/
package com.opencodez.controllers;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
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.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import com.google.gson.Gson;
import com.opencodez.domain.User;
import com.opencodez.domain.pagination.DataTableRequest;
import com.opencodez.domain.pagination.DataTableResults;
import com.opencodez.domain.pagination.PaginationCriteria;
import com.opencodez.repo.UserRepository;
import com.opencodez.util.AppUtil;
/**
* @author pavan.solapure
*
*/
@Controller
public class BaseController {
@Autowired
private UserRepository userRepo;
/** The entity manager. */
@PersistenceContext
private EntityManager entityManager;
@RequestMapping(value="/users", method=RequestMethod.GET)
public String listUsers(Model model) {
return "users";
}
@RequestMapping(value="/users/paginated", method=RequestMethod.GET)
@ResponseBody
public String listUsersPaginated(HttpServletRequest request, HttpServletResponse response, Model model) {
DataTableRequestxUserx dataTableInRQ = new DataTableRequestxUserx(request);
PaginationCriteria pagination = dataTableInRQ.getPaginationRequest();
String baseQuery = "SELECT id as id, name as name, salary as salary, (SELECT COUNT(1) FROM USER) AS totalrecords FROM USER";
String paginatedQuery = AppUtil.buildPaginatedQuery(baseQuery, pagination);
System.out.println(paginatedQuery);
Query query = entityManager.createNativeQuery(paginatedQuery, User.class);
@SuppressWarnings("unchecked")
ListxUserx userList = query.getResultList();
DataTableResultsxUserx dataTableResult = new DataTableResultsxUserx();
dataTableResult.setDraw(dataTableInRQ.getDraw());
dataTableResult.setListOfDataObjects(userList);
if (!AppUtil.isObjectEmpty(userList)) {
dataTableResult.setRecordsTotal(userList.get(0).getTotalRecords()
.toString());
if (dataTableInRQ.getPaginationRequest().isFilterByEmpty()) {
dataTableResult.setRecordsFiltered(userList.get(0).getTotalRecords()
.toString());
} else {
dataTableResult.setRecordsFiltered(Integer.toString(userList.size()));
}
}
return new Gson().toJson(dataTableResult);
}
}
In the above you can see when a request comes to this controller, datatable request, and pagination criteria that we need to pass to our data layer is prepared. After that using the pagination criteria which may contain sorting, filtering information a query is prepared and sent to execution for data. Below are both the queries base query and the one which gets prepared using the pagination information.
--Base Query SELECT id as id, name as name, salary as salary, (SELECT COUNT(1) FROM USER) AS totalrecords FROM USER --Query after adding pagination information SELECT FILTERED_ORDERD_RESULTS.* FROM (SELECT BASEINFO.* FROM ( SELECT id as id, name as name, salary as salary, (SELECT COUNT(1) FROM USER) AS totalrecords FROM USER ) BASEINFO ORDER BY id ASC ) FILTERED_ORDERD_RESULTS LIMIT 0, 5
Once results are back, datatable results are prepared which will have information about total records, filtered records, and actual data. This information is then converted to JSON and passed back to the client. Below is our User model, the data that get fetched and passed.
/**
*
*/
package com.opencodez.domain;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
/**
* @author pavan.solapure
*
*/
@Entity
public class User {
@Id
private Long id;
private String name;
private String salary;
@Column(name="totalrecords", updatable=false, insertable=false)
private Integer totalRecords;
}
The View
Now we will see how we have our HTML and JavaScript put together. In HTML we need to put below js and CSS files in order to use datatable functionality
x!DOCTYPE HTMLx
xhtml xmlns:th="http://www.thymeleaf.org"x
xheadx
xtitlexHomex/titlex
xmeta http-equiv="Content-Type" content="text/html; charset=UTF-8" /x
xlink href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
rel="stylesheet" media="screen" /x
xlink th:href="@{../css/starter-template.css}" rel="stylesheet" media="screen" /x
xlink th:href="@{../css/datatables.min.css}" rel="stylesheet" media="screen" /x
xscript
src="https://code.jquery.com/jquery-3.1.1.min.js"
integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8="
crossorigin="anonymous"xx/scriptx
xscript src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"xx/scriptx
xscript type="text/javascript" th:src="@{js/admin.js}"xx/scriptx
xscript type="text/javascript" th:src="@{../js/datatables.min.js}"xx/scriptx
xscript type="text/javascript" th:src="@{../js/fnSetFilteringEnterPress.js}"xx/scriptx
x/headx
xbodyx
xnav class="navbar navbar-inverse navbar-fixed-top"x
xdiv class="container"x
xdiv class="navbar-header"x
xbutton type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar"x
xspan class="sr-only"xToggle navigationx/spanx
xspan class="icon-bar"xx/spanx
xspan class="icon-bar"xx/spanx
xspan class="icon-bar"xx/spanx
x/buttonx
xa class="navbar-brand" href="#"xDatatable Demox/ax
x/divx
x/divx
x/navx
xdiv class="container"x
xdiv class="starter-template"x
xdiv class="table-responsive"x
xtable id="paginatedTable" class="table table-striped"x
xtheadx
xtrx
xthxIdx/thx
xthxNamex/thx
xthxSalaryx/thx
x/trx
x/theadx
x/tablex
x/divx
x/divx
x/divxx!-- /.container --x
x/bodyx
x/htmlx
And after we will initialize the datatable as shown below
$( document ).ready(function() {
$('#paginatedTable').DataTable( {
"processing": true,
"serverSide": true,
"pageLength": 5,
"ajax": {
"url": "/users/paginated",
"data": function ( data ) {
//process data before sent to server.
}},
"columns": [
{ "data": "id", "name" : "ID", "title" : "ID" },
{ "data": "name", "name" : "Name" , "title" : "Name"},
{ "data": "salary", "name" : "Salary" , "title" : "Salary"}
]
});
$('#paginatedTable').dataTable().fnSetFilteringEnterPress();
});
Demo of Datatable
We are done with the code and config. Letxs see it working.
Default Rendering
Filtering
Sorting
Update:
Many people asked and trying to work this example with the Oracle database. I have added Oracle specific code to the same project. As this project is not equipped to connect to two database at the same time, I had to comment MySQL properties to connect with Oracle. (I know its pain, but I had to do it as I wanted to keep the focus of this article on Datatable and Oracle Integration.)
Change properties file to point to your Oracle database.
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe spring.datasource.username=pavans spring.datasource.password=****** spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
Refer file datatable.user.sql that is available in the source repository to create the table and sample data.
I have added couple of views and controllers to separea Oracle and MySQL flow.
The main change is how paginated query is built for Oracle.
public static String buildPaginatedQueryForOracle(String baseQuery, PaginationCriteria paginationCriteria) {
StringBuilder sb = new StringBuilder("SELECT * FROM (SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() total_records, ROWNUM AS RN FROM (SELECT BASEINFO.* FROM ( #BASE_QUERY# ) BASEINFO ) FILTERED_ORDERED_RESULTS #WHERE_CLAUSE# #ORDER_CLASUE# ) WHERE RN x (#PAGE_NUMBER# * #PAGE_SIZE#) AND RN x= (#PAGE_NUMBER# + 1) * #PAGE_SIZE# ");
String finalQuery = null;
//Datatable start is set to 0, 5, 10 ..etc (5 is page size)
//For oracle paginated query we need page start from 1,2,3
int pageNo = paginationCriteria.getPageNumber() / paginationCriteria.getPageSize();
paginationCriteria.setPageNumber(pageNo);
if(!AppUtil.isObjectEmpty(paginationCriteria)) {
finalQuery = sb.toString().replaceAll("#BASE_QUERY#", baseQuery)
.replaceAll("#WHERE_CLAUSE#", ((AppUtil.isObjectEmpty(paginationCriteria.getFilterByClause())) ? "" : " WHERE ") + paginationCriteria.getFilterByClause())
.replaceAll("#ORDER_CLASUE#", paginationCriteria.getOrderByClause())
.replaceAll("#PAGE_NUMBER#", paginationCriteria.getPageNumber().toString())
.replaceAll("#PAGE_SIZE#", paginationCriteria.getPageSize().toString());
}
return (null == finalQuery) ? baseQuery : finalQuery;
}
The above function takes pagination criteria into account and prepares the query for Oracle. You can not use the limit clause directly in Oracle, you have to rely on ROWNUM to limit the results. In this function, we calculate the page number and size and accordingly set them in the query.
I have done some minor changes to the domain object. You can refer them in source code.
Conclusion
I hope this example of datatable with Spring Boot will help you. You can download the source code and supporting SQL from our GitHub
![](data:image/svg+xml;charset=utf-8,<svg height="64" width="64" xmlns="http://www.w3.org/2000/svg" version="1.1"/>)