JQuery DataTable server side processing using spring 3.0 and JPA

Hello everyone,

In this article I’m going to tell you about how to use JQuery DataTable with Spring 3.0 and JPA.

I am not going to share complete code here but whatever I'm going to write will give you a complete idea about its implementation.

First, we’ll see what is Jquery DataTable and what feature it provides?

So Jquery DataTable is a plug-in for the jQuery Javascript library, now whenever there is need to display data on GUI in tabular format, first thing comes in our mind is html <Table> J, but what if user wants to do additional operation on that data like 

1. Sorting
2. Searching
3. Filtering
4. Pagination
5. Linking
6. Applying different themes

Basically data table operate in 2 modes

1. Client-side processing - where filtering, paging and sorting calculations are all performed in the web-browser.
2. Server-side processing - where filtering, paging and sorting calculations are all performed by a server. 

 Here we are going to see server side processing as this is what requires when you deal with large database.

Server-side processing enabled all paging, searching, ordering actions that DataTables performs are handed off to a server where an SQL engine (or similar) can perform these actions on the large data set (after all, that's what the database engine is designed for!). As such, each draw of the table will result in a new Ajax request being made to get the required data.

So let’s start

Step 1) Download and import below jquery plugins into your jsp

<script type="text/javascript" src="resources/scripts/ jquery-1.11.1.min.js"></script> <script type="text/javascript" src="resources/scripts/jquery.dataTables.js"></script> <script type="text/javascript" src="resources/scripts/jquery.dataTables.columnFilter.js"></script>

jquery.dataTables.columnFilter.js is add-on for the DataTable which added column filtering functionalities found on the data tables 

Step 2) Add place holder for the DataTable

<table id="example" class="display dataTable" cellspacing="0" width="100%" role="grid" aria-describedby="example_info" style="width: 100%;">

Step 3) The JavaScript to initialize the DataTable

$(document).ready(function() {     $('#example').dataTable( {         "processing": true,         "bServerSide": true,         "sAjaxSource":  "/appName/yourSpringRequestMappingValue",     } ); } );

where

·         "bServerSide": true  Configure DataTables to use server-side processing

·         “processing”: for showing page loading ajax effect

·         “sAjaxSource“ :You can instruct DataTables to load data from an external source using this parameter(your action name)

This is a minimum script require to initialize the DataTable. Now we will add rest of functionality to it one by one.

a)       defining column and style

"columns" : [                                     {                            "title" : "First name:",                            "class" : "center"                      }, {                            "title" : "Last name:",                            "class" : "center"                      },            {                 "class":          'details-control',                 "orderable":      false,                 "data":           null,                 "defaultContent": 'default value of column'             }                  ]

Here I have added 2 column name and surname with some default setting

So the script will be like this.

$(document).ready(function() {     $('#example').dataTable( {         "processing": true,         "bServerSide": true,         "sAjaxSource":  "/appName/yourSpringRequestMappingValue", "columns" : [                                                  {                         "title" : "First name:",                         "class" : "center"                   }, {                         "title" : "Last name:",                         "class" : "center"                   }, {                 "class":          'details-control',                 "orderable":      false,                 "data":           null,                 "defaultContent": 'default value of column'             }                  ]     } ) } );

a)       Adding filter to column using 

$(document).ready(function() {     $('#example').dataTable( {         "processing": true,         "bServerSide": true,         "sAjaxSource":  "/appName/yourSpringRequestMappingValue", "columns" : [                                                        {                            "title" : "First name:",                            "class" : "center"                      }, {                             "title" : "Last name:",                            "class" : "center"                      }, {                 "class":          'details-control',                 "orderable":      false,                 "data":           null,                 "defaultContent": 'default value of column'             }                  ]     } ).columnFilter({                       aoColumns: [                                                              { sSelector: "#name", type: "select", values:  ${nameListInArrayForm}},                            { sSelector: "#surname", type: "select", values: [‘A’,’B'] }                                          ]                       }); } );

Please find more details about column filter at http://jquery-datatables-column-filter.googlecode.com/svn/trunk/customFilters.html

Here as I have displayed the filter in separate section so need to add place holder for this filter

<div id="filterWrapperDiv">       <div style="margin-left: 15px; margin-top: 20px;">             <a class="filterLables">Name</a>             <div id="name" class="hintText">Select Name</div>             <a class="filterLables">Surname</a>             <div id="surName" class="hintText">Select Surname</div>       </div>       <div id="filterSubmitResetDiv">             <button type="button" id="resetButton">Reset</button>             <button type="button" id="applyButton" class="applyButton">Apply             </button>             <br>                   <br>       </div>       <br> </div>

a)       Adding style to particular cell

If you want to add some style while displaying data, you can do this by using below callback method.

Here for each row you can set particular style to column data. This is useful when you want to add links/color to particular value

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {             if ( (aData[1] == "BB" ) {       jQuery('td:eq(1)', nRow).addClass('redText');       }else if (aData[2] == "AA") {       jQuery('td:eq(2)', nRow).addClass('greenText');       }             return nRow; }

On server side we will write on controller to load data for the DataTable like below

@RequestMapping(value = "/yourSpringRequestMappingValue", method = RequestMethod.GET) @ResponseBody String  getJsonData(HttpServletRequest request) {                   return "JSON_DATA_AS_STRING"       }

Now most important thing

Here on every sort, search, and show page operation this controller will be called.

So in order identify which operation is requested, below are some request parameter we need to check carefully for deciding selection criteria for query.

For pagination we have

·         request.getParameter("iDisplayLength") use for number of rows to display on a single page when using pagination

·         request.getParameter("iDisplayStart") use to define the starting point for data display when using DataTables with pagination

These values will help us to calculate the page no.

int pageNumber = (iDisplayStart  + iDisplayLength)/iDisplayLength;

For sorting we can use parameter like 

request.getParameter("iSortCol_0")  0 indicates user click on first column for sorting

request.getParameter("sSortDir_0") this gives direction for sorting ASC or DESC  order

For Searching we can use below parameter

request.getParameter("sSearch_0")  if this value is not empty or null then it indicates search applied on first column

In this way you can get request parameter based on your table size to set criteria for your result query.

You can use JPA javax.persistence.Query interface for issuing such criteria query.

Query q = entityManager.createQuery(stringQuery);

For pagination you can use below method of Query class to select data for particular page 

q.setFirstResult((pageNumber-1) * pageSize); //page no q.setMaxResults(pageSize);//no of rows

And in order to calculate and maintain the current page no, total record we can use count query like below

Query countQ = entityManager.createQuery(Select count(id) from + “same criteria query used in select” );

Which will return the total no of record exists for particular criteria

Long totalCount    = (Long) countQ.getSingleResult();

This will help us maintain total page that we display on GUI like below. 

Showing 80 to 90 of 100 entries

·         Total Page = totalCount % pageSize

Once u fetch the data, next step is to convert it into Json object And at last you can use com.google.gson.JsonObject class to create Json Object.

//Create Main Json object JsonObject data  = new JsonObject(); //Create DATA ARRAY Collection<JsonArray> allData = new ArrayList<JsonArray>(); //Create Json data object JsonArray dataArray = new JsonArray(); JsonPrimitive name = new JsonPrimitive(“ABC”); JsonPrimitive surName = new JsonPrimitive(“XYZ”); dataArray.add(name); dataArray.add(surName); //In this way add all data objects to jsonArray collection. allData.add(jobVoArray); //And at last create the main json object data.addProperty("draw", pageNo); data.addProperty("recordsTotal",totalRecord); data.addProperty("recordsFiltered", filteredRecord); data.addProperty("aaData", allData.toString()); data.addProperty("sEcho", echo + 1 ); data.addProperty("iDisplayLength", pageSize); //and convert this data object to string to pass it as response

Here is something interesting; along with the data you need to pass these parameter

draw : Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables. Conveys page number it is going to display

sEcho   This parameter will change with each draw (it is basically a draw count) - so it is important that this is implemented.

recordsTotal  Total records, before filtering (i.e. the total number of records in the database

recordsFiltered Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).

iDisplayLength   Number of rows to display on a single page when using pagination.

This is how it looks like (example)

Charts And Graphs Using ChartJS, Ajax, Spring 3, Json

Web applications now these days are in demanding state, they have a huge data with them and user want to make this data more effective, interesting easy to understand , easy to analyse and compare.

Graphs and charts are the best way to present data in logical manner.

In this article I am going to explain you very easy and efficient way to draw the different graphs using chartjs 

see the link for more detail http://www.chartjs.org/

This chartJs provide javascript Chart.js which is easy and object oriented client side java script.

These graphs require the data in the form of Json object.

Here we are going to make an ajax request to spring action  for data and display the PIE chart for that data.

Below are the steps you need to follow.

1.  First we need to include the Chart.js library in the JSP page. The library occupies a global variable of Chart.

<script src="resources/scripts/jquery-1.9.1.js"></script> <script src="resources/scripts/jquery-ui.js"></script> <script src="resources/scripts/Chart.js"></script> <script src="resources/scripts/legend.js"></script>

 JQuery I used for making AJAX call and to use its other utility method like parseJSON().

       Here you can see the legend.js , legend is table explaining symbol used on chart like which color indicate what. Chart.js do not provide this facility so legends. js is an extension to Chart.js to add graph legends

You can download this javascript extension form here

https://github.com/bebraw/Chart.js.legend/tree/master/src

2. To create a chart, Chart.js need html 5 canvas tag as place holder 

    

<td >                 <canvas id="canvas" height="250" width="300"></canvas>                 <div id="canvasLegend"></div> </td>

Legend is table explaining symbol used on chart also this <div> canvasLegend is place holder for displaying legends

3. Ajax call to get the Json data as response. Here we are using JQuery Ajax to call the spring action         getReportData which will return the Json object.   

    

   We are parsing the string in the form of Json object using jQuery.parseJSON(response)

<script type="text/javascript"> function showPieChart() {               $.ajax({               type: "GET",               url: "/appname/getReportData",               cache: false,                                   contentType: "application/json; charset=utf-8",               success: function(response){                      var responsePIE = jQuery.parseJSON(response); var myPieChart = new Chart(document.getElementById("canvas").getContext("2d")).Pie(responsePIE); legend(document.getElementById("canvasLegend"), responsePIE);               },               error: function(response){                                    alert('Error while request..');                                    }        });        }             </script>

           To create a chart, we need to instantiate the Chart class.

            To do this, we need to pass in the 2d context of where we want to draw the chart.

var myPieChart = new Chart(document.getElementById("canvas").getContext("2d")).Pie(responsePIE);

           So here we pass the canvas id and the json data and command it to draw the PIE chart

4. Now let’s look at the code at server side, here we create one action loadData() which will be access by getReportData  [request Url  mapping path].

Here I used google json api to create the JSON object

import com.google.gson.Gson; import com.google.gson.JsonObject;

And set the data according to Chart.js data structure.

While adding property you can see I have added one more property called title

piedata1.addProperty("title", "INDIA");

These title are nothing but the legends used for the graph.  legend.js looks for this property and display the color for particular title.

var data = [          {                  value: 30,                  color:"#F38630"          },          {                  value : 50,                  color : "#E0E4CC"          },          {                  value : 100,                  color : "#69D2E7"          }                         ]

@RequestMapping(value = "/getReportData", method = RequestMethod.GET)        public @ResponseBody        String loadData(HttpServletResponse response) {                   JsonObject piedata1  = new JsonObject();                      piedata1.addProperty("value", Integer.parseInt("30"));                      piedata1.addProperty("color", "#F38630");                      piedata1.addProperty("title", "INDIA");                                           JsonObject piedata2  = new JsonObject();                      piedata2.addProperty("value",  Integer.parseInt("50"));                      piedata2.addProperty("color", "#E0E4CC");                      piedata2.addProperty("title", "US");                                           JsonObject piedata3  = new JsonObject();                      piedata3.addProperty("value", Integer.parseInt("100"));                      piedata3.addProperty("color", "#E0E4FF");                      piedata3.addProperty("title", "CHINA");                                           ArrayList<Object> pieDataSet = new ArrayList<Object>();                      pieDataSet.add(piedata1);                      pieDataSet.add(piedata2);                      pieDataSet.add(piedata3);                                                        return pieDataSet.toString();                      }

In this way we send the JSON response in the form of string like below and jQuery parse this string data into Json object and we get the below PIE chart as result 

[          {                  value: 30,                  color:"#F38630",                title:”INDIA”           },          {                  value : 50,                  color : "#E0E4CC".                title:”US”          },          {                  value : 100,                  color : "#69D2E7"                title:”CHINA”          }                         ]

So only thing need to take care here is to create the json structure for graph data rest is simple.