Lets start creating datatable server side listing page using java. Datatable is an ultimate solution for those who want to develop listing pages. By using datatable we can can handle, render large and complicated data very easily.  In this example I have implemented so many features like custom toolbar, initialized filter delay, added refresh and multiple search boxes at the top for individual column search. The processing of sorting, pagination and search is completely done at serverside using java.Technologies used in this example are java, servlet, jsp, jquery-datatable plugin and little bit css. You can also use this plugin in your frameworks like spring MVC and structs.

Datatable serverside listing page using java

Eclipse Project Structure and caret image




The complete serverside logic is written in JqueryDatatablePluginDemo.java handles all incoming request and process the response.

iDisplayStart: This is used to display initial point of data with pagination like 0, 10, 20, 30. For example your are displaying 10 records per page and you want to display 4th page set intially 30.

iDisplayLength: Using this we can set records display range per each page.

iSortCol_0: Used to get column index.

sSortDir_0: Used to get sorting direction. whether it is ascending(ASC) order or desending(DSC) order.

iTotalRecords: Total number of records.

iTotalDisplayRecords: Total number of display records.

sSearch and sSearch_0, 1, 2, 3, 4, sSearch_5 are used to get global filter and multi filter values.

JqueryDatatablePluginDemo.java
package com.studywithdemo;

import java.io.*;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import org.json.*;

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

 private String GLOBAL_SEARCH_TERM;
 private String COLUMN_NAME;
 private String DIRECTION;
 private int INITIAL;
 private int RECORD_SIZE;
 private String ID_SEARCH_TERM,NAME_SEARCH_TERM,PLACE_SEARCH_TERM,CITY_SEARCH_TERM,
 STATE_SEARCH_TERM,PHONE_SEARCH_TERM;

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

  String[] columnNames = { "id", "name", "place", "city", "state","phone" };

  JSONObject jsonResult = new JSONObject();
  int listDisplayAmount = 10;
  int start = 0;
  int column = 0;
  String dir = "asc";
  String pageNo = request.getParameter("iDisplayStart");
  String pageSize = request.getParameter("iDisplayLength");
  String colIndex = request.getParameter("iSortCol_0");
  String sortDirection = request.getParameter("sSortDir_0");
  
  if (pageNo != null) {
   start = Integer.parseInt(pageNo);
   if (start < 0) {
    start = 0;
   }
  }
  if (pageSize != null) {
   listDisplayAmount = Integer.parseInt(pageSize);
   if (listDisplayAmount < 10 || listDisplayAmount > 50) {
    listDisplayAmount = 10;
   }
  }
  if (colIndex != null) {
   column = Integer.parseInt(colIndex);
   if (column < 0 || column > 5)
    column = 0;
  }
  if (sortDirection != null) {
   if (!sortDirection.equals("asc"))
    dir = "desc";
  }

  String colName = columnNames[column];
  int totalRecords= -1;
  try {
   totalRecords = getTotalRecordCount();
  } catch (SQLException e1) {
   e1.printStackTrace();
  }

  RECORD_SIZE = listDisplayAmount;
  GLOBAL_SEARCH_TERM = request.getParameter("sSearch");
  ID_SEARCH_TERM=request.getParameter("sSearch_0");
  NAME_SEARCH_TERM=request.getParameter("sSearch_1");
  PLACE_SEARCH_TERM=request.getParameter("sSearch_2");
  CITY_SEARCH_TERM=request.getParameter("sSearch_3");
  STATE_SEARCH_TERM=request.getParameter("sSearch_4");
  PHONE_SEARCH_TERM=request.getParameter("sSearch_5");
  COLUMN_NAME = colName;
  DIRECTION = dir;
  INITIAL = start;

  try {
   jsonResult = getPersonDetails(totalRecords, 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(jsonResult);

 }

 public JSONObject getPersonDetails(int totalRecords, HttpServletRequest request)
   throws SQLException, ClassNotFoundException {

  int totalAfterSearch = totalRecords;
  JSONObject result = new JSONObject();
  JSONArray array = new JSONArray();
  String searchSQL = "";

  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  String dbConnectionURL = "jdbc:mysql://localhost:3306/faruk?user=root&password=root";
  Connection con = DriverManager.getConnection(dbConnectionURL);
  String sql = "SELECT " + "id, name, place, city, state, "
    + "phone " + "FROM " + "person " + "WHERE ";

  String globeSearch = "id like '%" + GLOBAL_SEARCH_TERM + "%'"
    + "or name like '%" + GLOBAL_SEARCH_TERM + "%'"
    + "or place like '%" + GLOBAL_SEARCH_TERM + "%'"
    + "or city like '%" + GLOBAL_SEARCH_TERM + "%'"
    + "or state like  '%" + GLOBAL_SEARCH_TERM + "%'"
    + "or phone like '%" + GLOBAL_SEARCH_TERM + "%'";
  
  String idSearch="id like " + ID_SEARCH_TERM + "";
  String nameSearch="name like '%" + NAME_SEARCH_TERM + "%'";
  String placeSearch=" place like '%" + PLACE_SEARCH_TERM + "%'";
  String citySearch=" city like '%" + CITY_SEARCH_TERM + "%'";
  String stateSearch=" state like '%" + STATE_SEARCH_TERM + "%'"; 
  String phoneSearch=" phone like '%" + PHONE_SEARCH_TERM + "%'";
        System.out.println(phoneSearch);
  if (GLOBAL_SEARCH_TERM != "") {
   searchSQL = globeSearch;
  }
  else if(ID_SEARCH_TERM !="")
  {
   searchSQL=idSearch;
  }
  else if(NAME_SEARCH_TERM !="")
  {
   searchSQL=nameSearch;
  }
  else if(PLACE_SEARCH_TERM!="")
  {
   searchSQL=placeSearch;
  }
  else if(CITY_SEARCH_TERM!="")
  {
   searchSQL=citySearch;
  }
  else if(STATE_SEARCH_TERM!="")
  {
   searchSQL=stateSearch;
  }
  else if(PHONE_SEARCH_TERM!=null)
  {
   searchSQL=phoneSearch;
   
  }
     
  sql += searchSQL;
  sql += " order by " + COLUMN_NAME + " " + DIRECTION;
  sql += " limit " + INITIAL + ", " + RECORD_SIZE;
        
        //for searching
  PreparedStatement stmt = con.prepareStatement(sql);
  ResultSet rs = stmt.executeQuery();

  while (rs.next()) {
   JSONArray ja = new JSONArray();
   ja.put(rs.getString("id"));
   ja.put(rs.getString("name"));
   ja.put(rs.getString("place"));
   ja.put(rs.getString("city"));
   ja.put(rs.getString("state"));
   ja.put(rs.getString("phone"));
   array.put(ja); 
  }
  stmt.close();
  rs.close();

  String query = "SELECT " + "COUNT(*) as count " + "FROM " + "person " + "WHERE ";

  //for pagination
  if (GLOBAL_SEARCH_TERM != ""||ID_SEARCH_TERM != "" || NAME_SEARCH_TERM != "" 
||PLACE_SEARCH_TERM != ""||CITY_SEARCH_TERM != ""|| STATE_SEARCH_TERM != "" 
|| PHONE_SEARCH_TERM != "" ) {
   query += searchSQL;

   
   PreparedStatement st = con.prepareStatement(query);
   ResultSet results = st.executeQuery();

   if (results.next()) {
    totalAfterSearch = results.getInt("count");
   }
   st.close();
   results.close();
   con.close();
  }
  try {
   result.put("iTotalRecords", totalRecords);
   result.put("iTotalDisplayRecords", totalAfterSearch);
   result.put("aaData", array);
  } catch (Exception e) {

  }

  return result;
 }

 public int getTotalRecordCount() throws SQLException {

  int totalRecords = -1;
  String sql = "SELECT " + "COUNT(*) as count " + "FROM " + "person";
        String dbConnectionURL = "jdbc:mysql://localhost:3306/myDB?user=root&password=root";
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }

  Connection con = DriverManager.getConnection(dbConnectionURL);

  PreparedStatement statement = con.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();

  if (resultSet.next()) {
   totalRecords = resultSet.getInt("count");
  }
  resultSet.close();
  statement.close();
  con.close();

  return totalRecords;
 }

}

custom-datatable.js
var table;

jQuery(document).ready(function() {
 table = $('#personTable').dataTable({
     "bPaginate": true,
     "order": [ 0, 'asc' ],
     "bInfo": true,
     "iDisplayStart":0,
     "bProcessing" : true,
    "bServerSide" : true,
    "sAjaxSource" : path+"/com/studywithdemo/JqueryDatatablePluginDemo.java",
    "dom": 'C<"clear">lfrtip',
   colVis: {
    "align": "right",
             restore: "Restore",
             showAll: "Show all",
             showNone: "Show none",
    order: 'alpha',
    "buttonText": "columns <img src=\"/datatableServersideExample/images/caaret.png\"/>"
         },
      "language": {
             "infoFiltered": ""
         },
         "dom": 'Cf<"toolbar"">rtip',
 
       })
     .columnFilter({
      aoColumns: [
                  { type: "number"},
               { type: "text" },
               { type: "text" },
               { type: "text" },
                           { type: "text" },
                           { type: "text" },
       ],
      bUseColVis: true
      }).fnSetFilteringDelay();
   $("#personTable_length").hide();
   $("div.toolbar").append('<div class="btn-group" style="padding:5px "><button 
class="btn btn-default" id="refreshbtn" style="background:none;border:1px 
solid #ccc;height:30px" 
type="button"><span class="glyphicon glyphicon-refresh" style="padding:3px">
</span></button>
</div>');
      $("div.toolbar").css("float","right");
      $('#refreshbtn').click(function(){
       table.fnStandingRedraw();
     });
   

 });


viewDatatable.jsp
<%@ page import="java.util.*"%><!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
 var path = '${pageContext.request.contextPath}';
</script>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap
/3.2.0/css/bootstrap.min.css">
<link href="http://cdn.datatables.net/1.10.3/css/jquery.dataTables.css" rel="stylesheet"
 type="text/css">
<link href="http://datatables.net/release-datatables/extensions/ColVis/
css/dataTables.colVis.css" 
rel="stylesheet" type="text/css">
<script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="http://cdn.datatables.net/1.10.3/js/jquery.dataTables.min.js"></script>
<script src="http://datatables.net/release-datatables/extensions/ColVis/js/
dataTables.colVis.js"
></script>
<script src="http://jquery-datatables-column-filter.googlecode.com/svn/trunk/media/
js/jquery.dataTables.columnFilter.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/
assets/js/custom-datatable.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/
assets/plugin/fnStandingRedraw.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/
assets/plugin/fnSetFilteringDelay.js"></script>
<title>Person Form</title>
</head>
<body>
<form>
 <div class="form">
  <table width="100%" border="0" margin="0" padding="0"
   class="row-border tableHeader" id="personTable">
   <thead>
    <tr>
        <th>Id</th>
     <th>Name</th>
     <th>Place</th>
     <th>City</th>
     <th>State</th>
     <th>Phone</th>
    </tr>
   </thead>
   <tfoot>
     <tr>
     <th>Id</th>
     <th>Name</th>
     <th>Place</th>
     <th>City</th>
     <th>State</th>
     <th>Phone</th>
    </tr>
     </tfoot>
   <tbody>
   </tbody>
  </table>
 </div>
</form>
<style>
tfoot input {
        width: 100%;
        padding: 3px;
        box-sizing: border-box;
    }
.tableHeader{
text-align:left;
}
tfoot {
    display: table-header-group;
}
.dataTables_length
{
position: absolute;
    top: 10px;
    left: 220px;
}
.dataTables_info {
    position: absolute;
    top: 0px;
    left: 5px;
}
.ColVis{
 padding-right:10px;
 padding-top:5px;

}
.dataTables_filter {
   position: absolute;
   top: 10px;
   left: 200px;
   font-size:15px;
}
.dataTables_filter input{
height:22px;
width:150px
}
input
{
-moz-border-radius: 15px;
 border-radius: 3px;
 border:solid 1px #c7c7c7;
 padding:5px;
}
table.dataTable tbody td {
    padding: 5px;
    padding-left: 20px;
}
</style>
</body>

</html>

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" xmlns:jsp="http://java.sun.com/xml/ns/javaee/jsp" 
xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" 
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>datatableServersideExample</display-name>

  <servlet>

    <servlet-name>JqueryDatatablePluginDemo</servlet-name>

    <servlet-class>com.studywithdemo.JqueryDatatablePluginDemo</servlet-class>

  </servlet>

  <servlet-mapping>

    <servlet-name>JqueryDatatablePluginDemo</servlet-name>

    <url-pattern>/com/studywithdemo/JqueryDatatablePluginDemo.java</url-pattern>

  </servlet-mapping>

  <welcome-file-list>

    <welcome-file>

      /WEB-INF/views/viewDatatable.jsp

    </welcome-file>

  </welcome-file-list>

</web-app>


46 comments:

  1. Hi,

    Nice datatable, do you have the source code in github??

    Thx!!!!!

    ReplyDelete
    Replies
    1. Hi,

      You can download from this link https://drive.google.com/file/d/0Bw-ZBbYmW_PRWFgzS3BIdHRYYkk/view?usp=sharing

      Thank you For Visiting StudywithDemo

      Delete
  2. Thanks for the download link, It has helped me a lot :)

    ReplyDelete
  3. Welcome :) Keep Visiting StudywithDemo

    ReplyDelete
  4. how can i get database of this app?

    ReplyDelete
    Replies
    1. Hi dipesh raichana i have provided the download link in comment section please once check and download the datatable example..

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. @Faruk,

      How can I change the number of records shown? I can't seem to show more than 10 and ideally, I'd be showing 50 at minimum

      Delete
    2. Hi Michael Wiggins,

      Can u please change the logic in java code at listDisplayAmount = 10. Give your record display amount to variable listDisplayAmount and check the logic once at line 43. Thank you for visiting studywithdemo

      Delete
    3. Hi sir can u help me to make some changes in my website www.iandbf.com plz make me a call to 7477382083

      Delete
  7. Hi Faruk,

    Every time I run the program, I get "Access denied for root@localhost", and "Unknown Database: Faruk". Any ideas? I have already reset root password.

    ReplyDelete
    Replies
    1. Hi,
      did u created database?
      Create database with name "faruk" and give mysql user name and password "root".

      Thank you

      Delete
  8. Thanks a lot ...its really a good example of dataTable with database...:)

    ReplyDelete
  9. im facing one problem after adding columnFilter,the ajax call is still loading stage.when i checked the ajax url it is taking the current window path as the url and sending the parameter along with it.
    Can u help me how to figure this out? it is only after adding columnFilter

    ReplyDelete
    Replies
    1. Hi Nishanth,

      did u checked this line "sAjaxSource" : path+"/com/studywithdemo/JqueryDatatablePluginDemo.java",
      ?

      Delete
  10. I'm using this example to dip 40k rows. Initially, for 3 to 5 pages it loading fast after that it showing 'Processing...' but not completing. If i restart the server then it repeating same.
    And If i want to add link to any of column, how to add the anchor tag ?
    Any solution

    Thank you.

    ReplyDelete
    Replies
    1. Hi Arun kumar,
      did u checked the totalRecords?
      Add link to any of column Example: http://jsfiddle.net/rp0zw8rm/1/

      Delete
  11. Good article, however somewhat dated now. DataTables has since seen some major changes. Your readers may want to consider visiting the JED website for a current implementation supporting DataTables on the Java platform. http://jed-datatables.net

    ReplyDelete
    Replies
    1. Hi Alan, why would you say the code is dated? I visited JED and did not see as detailed code as is here. Am downloading JED now to discover what it has to offer. Please note, I agree that datatables.net does not really present java solutions (php based mainly), and appreciate what JED is saying, but have yet to discover what JED offers to enhance java based coding of dynamic data tables.

      Delete
    2. Hi Alan, hope you are having a wonderful day. Am going to follow this tutorial to get JED up and running in JSF. Later shall port over to Spring Tag MVC. Thank you!

      Delete
  12. Excellent example. Worked like a charm on Tomcat. I have a question for you. I have turned the some values displayed into input boxes (input type=text) so that each row can be updated. However, I do not know how to get that data passed back to the servlet. Is there a way to capture that data and send it back to the server. Please let me know. Thanks.

    ReplyDelete
    Replies
    1. I have tried using something like this:
      $('#savebtn').click(function () {table.$('input').serialize(); });
      But I get nothing back.

      Delete
    2. Hi, you were able to run the code - by your post. I am hoping to accomplish the same. Do you know of something similar to run in a JSF instead of JSP web app?

      Delete
    3. No, but I would think there would be some examples out there.

      Delete
    4. I hope to be able to complete this today in JSF.

      Delete
  13. Hi Faruk, just wanted to say thank you for this post!

    ReplyDelete
  14. Faruk, the downloaded example only sits and continues to say PROCESSING and does not load any data. Any suggestions on how to get your app to run?

    ReplyDelete
  15. I don't think it is connecting to the database.
    I updated the mysql driver -
    I checked the connection:

    String dbConnectionURL = "jdbc:mysql://localhost:3306/faruk?user=root&password=root";

    Connection con = DriverManager.getConnection(dbConnectionURL);

    ReplyDelete
  16. FYI notes for this tutorial...
    import org.json.*;

    Download the ZIP file from this URL (http://www.java2s.com/Code/JarDownload/java/java-json.jar.zip) and extract it to get the Jar. Add the Jar to your build path. To check the available classes in this Jar use this URL.

    To Add this Jar to your build path Right click the Project > Build Path > Configure build path> Select Libraries tab > Click Add External Libraries > Select the Jar file Download

    NOTE FROM: http://stackoverflow.com/questions/8997598/importing-json-into-an-eclipse-project

    ReplyDelete
  17. Make sure you place this CORRECTLY as if you copy as it is -- it will produce an error DUE TO the splitting of the SINGLE line into MULTIPLE lines

    "$("div.toolbar..."

    ReplyDelete
  18. failed to get it to run, tried to re-code in JSF but failed as well.

    Has anyone been able to get this to run?

    Q. Where is the reference to dataTables.js files?

    ReplyDelete
  19. Can you share the table ddl?

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. Please update your front end code to latest Datatable version, CDN'S shows 404(NOT FOUND) in console and page doesn't load properly.
    colVis is retired in latest version of datatables. Toggling columns, refreshing the table doesn't work anymore due to 404 Errors.

    ReplyDelete
  22. Hii farukh this is very nice example.From where i can get database??

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. while Implementing above code I've faced this issue
    DataTables warning: table id=personTable - Invalid JSON response.

    ReplyDelete
  25. Hi farukh,
    I get an error popup in pages that says: DataTables warning: table id=personTable Invalid JSON response – Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  26. Hello i got a Problem. I have the Same code and the Same files like you. But on my Website i cant search in each rows. The fields are not there. And the searchbar is between my header names.
    Hope you can Help me.

    ReplyDelete
  27. Hi, Thank you for this solution. But can you please tell me how to add download excel and csv plugins ?

    ReplyDelete

 
Top