Friday, 18 February 2011

JQ datatable pipeline largedata with AJAX

JQuery datatable how to pipeline largedata with AJAX and JSP

My work depends on "DataTables server-side processing with
pipelining example " from the official JQuery side. The
difference are:

1. The original example's AJAX works with PHP where as mine works
with JSP.
2. I corrected a paging problem which I do not know why it
happened to me or whether the original also has it. The problem was:
Although it did not come to the end of the data, it disabled the
further arrow > button. So, I could not reach the data further than
the data that had been read at the first moment. I figured out that if
I change the TotalDisplayRecords the datatable behaves as required,
including the page size and sort change cases.

I added:

if ((oCache.iDisplayStart+ oCache.iDisplayLength < json.iTotalRecords) &&
(oCache.iCacheUpper+ oCache.iDisplayLength < json.iTotalRecords))
json.iTotalDisplayRecords=oCache.iCacheUpper+oCache.iDisplayLength;
else
json.iTotalDisplayRecords = json.iTotalRecords;

The complete and running code follws:

serverSideJQdtARS_5.jsp
-----------------------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<link rel="shortcut icon" type="image/ico" href="DataTables-1.7.5/media/images/favicon.ico" />

<title>DataTables example</title>
<style type="text/css" title="currentStyle">
@import "DataTables-1.7.5/media/css/demo_page.css";
@import "DataTables-1.7.5/media/css/demo_table.css";
</style>
<script type="text/javascript" language="javascript" src="DataTables-1.7.5/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="DataTables-1.7.5/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8">
var oCache = {
iCacheLower: -1
};

function fnSetKey( aoData, sKey, mValue )
{
for ( var i=0, iLen=aoData.length ; i<iLen ; i++ )
{
if ( aoData[i].name == sKey )
{
aoData[i].value = mValue;
}
}
}

function fnGetKey( aoData, sKey )
{
for ( var i=0, iLen=aoData.length ; i<iLen ; i++ )
{
if ( aoData[i].name == sKey )
{
return aoData[i].value;
}
}
return null;
}

function fnDataTablesPipeline ( sSource, aoData, fnCallback ) {
//alert("fnDataTablesPipeline");
var iPipe = 2; /* Ajust the pipe size */

var bNeedServer = false;
var sEcho = fnGetKey(aoData, "sEcho");
var iRequestStart = fnGetKey(aoData, "iDisplayStart");
var iRequestLength = fnGetKey(aoData, "iDisplayLength");
var iRequestEnd = iRequestStart + iRequestLength;
//alert("iRequestStart="+iRequestStart+"iRequestLength="+iRequestLength);



oCache.iDisplayStart = iRequestStart;

/* outside pipeline? */
if ( oCache.iCacheLower < 0 || iRequestStart < oCache.iCacheLower || iRequestEnd + iRequestLength*iPipe > oCache.iCacheUpper )
{
bNeedServer = true;
}
//alert(bNeedServer);
/* sorting etc changed? */
if ( oCache.lastRequest && !bNeedServer )
{
for( var i=0, iLen=aoData.length ; i<iLen ; i++ )
{
if ( aoData[i].name != "iDisplayStart" && aoData[i].name != "iDisplayLength" && aoData[i].name != "sEcho" )
{
if ( aoData[i].value != oCache.lastRequest[i].value )
{
bNeedServer = true;
break;
}
}
}
}

/* Store the request for checking next time around */
oCache.lastRequest = aoData.slice();

if ( bNeedServer )
{
if ( iRequestStart < oCache.iCacheLower )
{
iRequestStart = iRequestStart - (iRequestLength*(iPipe-1));
if ( iRequestStart < 0 )
{
iRequestStart = 0;
}
}

oCache.iCacheLower = iRequestStart;
oCache.iCacheUpper = iRequestStart + (iRequestLength * iPipe);
oCache.iDisplayLength = fnGetKey( aoData, "iDisplayLength" );
fnSetKey( aoData, "iDisplayStart", iRequestStart );
fnSetKey( aoData, "iDisplayLength", iRequestLength*iPipe );
//alert("oCache.iCacheLower="+oCache.iCacheLower+"oCache.iCacheUpper="+oCache.iCacheUpper);

$.getJSON( sSource, aoData, function (json) {
/* Callback processing */
oCache.lastJson = jQuery.extend(true, {}, json);

if ( oCache.iCacheLower != oCache.iDisplayStart )
{
json.aaData.splice( 0, oCache.iDisplayStart-oCache.iCacheLower );
}
json.aaData.splice( oCache.iDisplayLength, json.aaData.length );
jsonARS = json;

// if (oCache.iCacheUpper+ oCache.iDisplayLength < json.iTotalRecords)
// json.iTotalDisplayRecords=oCache.iCacheUpper+oCache.iDisplayLength;
if ((oCache.iDisplayStart+ oCache.iDisplayLength < json.iTotalRecords) &&
(oCache.iCacheUpper+ oCache.iDisplayLength < json.iTotalRecords))
json.iTotalDisplayRecords=oCache.iCacheUpper+oCache.iDisplayLength;
else
json.iTotalDisplayRecords = json.iTotalRecords;
fnCallback(json)
} );
}
else
{
json = jQuery.extend(true, {}, oCache.lastJson);
json.sEcho = sEcho; /* Update the echo for each response */
json.aaData.splice( 0, iRequestStart-oCache.iCacheLower );
json.aaData.splice( iRequestLength, json.aaData.length );
jsonARS = json;
fnCallback(json);
return;
}
}
$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
// "sPaginationType": "full_numbers",
"sPaginationType": "two_button",
"aaSorting": [[0, 'desc'], [1, 'desc']],

"sAjaxSource": "./server_processing.jsp",
"fnServerData": fnDataTablesPipeline
} );
} );


</script>
</head>
<body id="dt_example">
<div id="container">
<div class="full_width big">
<i>DataTables</i> server-side processing example
</div>
<H1>Live example</H1>
<DIV id=dynamic>
<TABLE id=example class=display border=0 cellSpacing=0 cellPadding=0>
<THEAD>
<TR>
<TH width="20%">Rendering engine</TH>
<TH width="25%">Browser</TH>
<TH width="25%">Platform(s)</TH>
<TH width="15%">Engine version</TH>
<TH width="15%">CSS grade</TH></TR></THEAD>
<TBODY>
<TR>
<TD class=dataTables_empty colSpan=5>Loading data from server
</TD>
</TR>
</TBODY>
<TFOOT>
<TR>
<TH>Rendering engine</TH>
<TH>Browser</TH>
<TH>Platform(s)</TH>
<TH>Engine version</TH>
<TH>CSS grade</TH>
</TR>
</TFOOT>
</TABLE>
</DIV>
</div>
</body>
</html>

server_processing.jsp
---------------------

<%--
Document : server_processingARS.jsp
Created on : 04.?ub.2011, 17:53:53
Author : Ali Riza SARAL
--%>

<%@page import="java.sql.Connection"%>
<%@page import="atg.taglib.json.util.*"%>
<%@page import= "java.util.*"%>
<%@page import= "java.sql.*"%>
<%@page import= "org.apache.commons.lang.*"%>


<%
class Utilities {

Utilities() {
}

;

String escape_string(String input) {
return StringEscapeUtils.escapeHtml(input);
}
}
%>

<%
System.out.println("test arsssssssssssssssssssssss");
Utilities util = new Utilities();
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
String[] aColumns = {"engine", "browser", "platform", "version", "grade"};

/* Indexed column (used for fast and accurate table cardinality) */
String sIndexColumn = "id";

/* DB table to use */
String sTable = "ajax";
/* Database connection information */
String user = "root";
String password = "3391309";
String db = "jquerydb";
String server = "localhost";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/

Connection conn = null;

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost/" + db,
user, password);

if (!conn.isClosed()) {
System.out.println("Successfully connected to "
+ "MySQL server using TCP/IP...");
}

} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
// try {
// if (conn != null) {
// //conn.close();
// }
// } catch (SQLException e) {
// }
}

/*
* Paging
*/
String sLimit = "";
if (request.getParameter("iDisplayStart") != null
&& request.getParameter("iDisplayLength") != "-1") {
sLimit = "LIMIT " + util.escape_string(request.getParameter("iDisplayStart")) +
", " + util.escape_string(request.getParameter("iDisplayLength"));
}

/*
* Ordering
*/
String sOrder = "";

if (request.getParameter("iSortCol_0") != null) {
sOrder = "ORDER BY ";

for (int i = 0; i < Integer.valueOf(request.getParameter("iSortingCols")); i++) {
if (request.getParameter("bSortable_" + Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))).equals("true")) {
sOrder += aColumns[Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))] + " "
+ util.escape_string(request.getParameter("sSortDir_" + String.valueOf(i))) + ", ";
}
}

sOrder = sOrder.substring(0, sOrder.length()-2);
if (sOrder.equals("ORDER BY")) {
sOrder = "";
}
sOrder += " ";
}



/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
String sWhere = "";
System.out.println("sSearch="+ request.getParameter("sSearch"));

if (request.getParameter("sSearch") != "") {
sWhere = "WHERE (";
for (int i = 0; i < aColumns.length; i++) {
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch")) + "%' OR ";
}
sWhere = sWhere.substring(0, sWhere.length()-3);
sWhere += ')';
}

/* Individual column filtering */

for (int i = 0; i < aColumns.length; i++) {
if (request.getParameter("bSearchable_" + String.valueOf(i)) == "true"
&& request.getParameter("sSearch_" + String.valueOf(i)) != "") {
if (sWhere == "") {
sWhere = "WHERE ";
} else {
sWhere += " AND ";
}
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch_" + String.valueOf(i))) + "%' ";
}
}

/*
* SQL queries
* Get data to display
*/
String strColumnNames = "";

for (int i=0;i < aColumns.length;i++){
strColumnNames = strColumnNames + ", "+ aColumns[i];
}
strColumnNames = strColumnNames.substring(1);

String sQuery = "SELECT "
+ strColumnNames
+ " FROM " + sTable
+ " "
+ sWhere
+ sOrder
+ sLimit;

System.out.println("sQuery="+sQuery);


Statement s = conn.createStatement();
s.executeQuery(sQuery);

/* Data set length after filtering */
ResultSet rs = s.getResultSet();
int count = 0;
while (rs.next()) {
String engineCol = rs.getString("engine");
System.out.println("engine = " + engineCol);
++count;
}
int iFilteredTotal = count;
rs.close();
s.close();
System.out.println("Selected count="+iFilteredTotal);

/* Total data set length */
s = conn.createStatement();
String sQuery2 = " SELECT COUNT(*) AS rowcount FROM " + sTable;
rs = s.executeQuery(sQuery2);
rs.next();
int iTotal = rs.getInt("rowcount");
rs.close();
s.close();
System.out.println("Total count="+iTotal);

/*
* Output
*/
String output = "{" +
"\"sEcho\" : "+ request.getParameter("sEcho")+ ", " +
"\"iTotalRecords\" : " + String.valueOf(iTotal) + ", " +
"\"iTotalDisplayRecords\" : " + String.valueOf(iFilteredTotal) + ", " +
// "\"iDisplayLength\" : " + "10" + ", " +
// "\"iDisplayStart\" : " + "10" + ", " +
"\"aaData\" : [" + "";

Object[][] aaData= new Object[iFilteredTotal][5];

s = conn.createStatement();
s.executeQuery(sQuery);
rs = s.getResultSet();

int rowNum=0;
while ( rs.next() )
{
System.out.println("rowNum="+ rowNum);
//if (rowNum > 3) break;
Object[] aRow = {"","","",0,""};
Object[] row = {"","","",0,""};

for ( int i=0 ; i<aColumns.length ; i++ )
{
System.out.println("i="+ i +" colVal="+ rs.getString( aColumns[i]));
if ( aColumns[i] == "version" )
{
/* Special output formatting for 'version' column */
row[i] = (rs.getInt( aColumns[i] )==0) ? "-" : rs.getInt(aColumns[i]);
}
else if ( aColumns[i] != " " )
{
/* General output */
row[i] = rs.getString( aColumns[i]);
}
}
aaData[rowNum] = row;
System.out.println("rowNum("+rowNum +") row[0]="+row[0].toString());
rowNum++;
}
rs.close();
s.close();

String valRow = " [ ";
for (int rowN = 0; rowN < iFilteredTotal; rowN++) {
System.out.println("rowN="+rowN);
for (int colN = 0; colN < aColumns.length; colN++) {
System.out.println(" colN="+colN);
if (aColumns[colN] == "version" )
valRow = valRow + " " + aaData[rowN][colN].toString() + " , ";
else if ( aColumns[colN] != " " )
valRow = valRow + "\"" + aaData[rowN][colN].toString() + "\" , ";
}
valRow = valRow.substring(0, valRow.length()-2);
valRow = valRow + " ], ";
System.out.println("valRow="+valRow);
output = output + valRow;
valRow = " [ ";
}
System.out.println("output="+output);
output = output.substring(0, output.length()-2);
output = output + " ] }";

out.print(output);
%>
Posted by at