This is the JAVA-JSP version of "DataTables server-side processing example" of official JQuery website. I have converted the 'server_processing.php' to 'server_processing.jsp'.
Please note that the original example lacks the
"sPaginationType": "two_button", (or "sPaginationType": "full_numbers")
My JSP version lacks it too... This is basicly an example for AJAX and server-side processing not pagination. I will be providing an example which uses pagination and AJAX calls for server-side processing in a couple of days or weeks.
Here is the full complete working code:
serverSideJQdtARS.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">
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"aaSorting": [[0, 'desc'], [1, 'desc']],
"sAjaxSource": "./server_processing.jsp"
} );
} );
</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\" : " + Integer.valueOf(iTotal) + ", " +
"\"iTotalDisplayRecords\" : " + iFilteredTotal + ", " +
"\"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);
%>
MySQL definitions and insert commands
-------------------------------------
CREATE TABLE `ajax` (
`engine` varchar(20) NOT NULL DEFAULT '',
`browser` varchar(35) DEFAULT NULL,
`platform` varchar(20) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`grade` varchar(1) DEFAULT NULL,
PRIMARY KEY (`engine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `ajax` (`engine`, `browser`, `platform`, `version`, `grade`)
VALUES ("engine1", "browser1", "platform1", 1, "A");
INSERT INTO `ajax` (`engine`, `browser`, `platform`, `version`, `grade`)
VALUES ("engine2", "browser1", "platform1", 1, "A");
INSERT INTO `ajax` (`engine`, `browser`, `platform`, `version`, `grade`)
VALUES ("engine3", "browser1", "platform1", 1, "A");
...
You will find my technical articles and translations on software in this article. My areas of interest include JAVA - J2EE with all its aspects, specially EJB3, Struts, JSF, Spring, WebServices and other frameworks.
INFORMATION
Blog Archive
-
▼
2011
(61)
-
▼
February
(20)
- Simple CRUD with JQuery-4
- Simple CRUD with JQuery-3
- Simple CRUD with JQuery-2
- Simple CRUD with JQuery
- Multi Actions with JQuery - 2
- Multi Actions with JQuery - 1
- JQuery how to pass data to a servlet on the server...
- JQ Forms with AJAX-JSP8
- JQ Forms with AJAX-JSP6
- JQ Forms with AJAX-JSP5
- JQ Forms with AJAX-JSP4
- JQ Forms with AJAX-JSP3
- JQ Forms with AJAX-JSP2
- JQ Forms with AJAX-JSP1
- JQ datatable pipeline largedata with AJAX
- JQ AJAX server processing with PAGING
- JQ server-processing with AJAX calls using JSP
- JQuery server processing with JSP-JAVA
- Simple JSON Examples with JAVA
- selectRow with JQeuryUI interface and JSP
-
▼
February
(20)
About Me
- Air Traffic Control and Large Systems
- MSc Electronics and Telecommunication ITU, MM Music Theory and Composition Illinois State Uni, post grad studies ATC - FPP and Large Systems EUROCONTROL Uni ;-)..
