Problem using datatables with JSP

Rilmax

Hello everyone, am a newbie with datatables and am using the demo schema and data to practice.
On my server jsp(source.jsp), i confirmed my result to be valid by printing it out using System.out.print(result). But it doesnt render on the client-side. All i get is processing.
Here is my client-side script:
[code]

var oTable;
var giRedraw = false;

$(document).ready(function() {

/* Init the table */
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "source.jsp",

"fnServerData": function (sSource, aoData, fnCallback, iTotalRecords,sEcho,iTotalDisplayRecords) {

// Please do i need to use the aoData.push? and if yes, how?
$.ajax({
"dataType": 'json',
"type":"POST",
"url":sSource,
"data":aoData,
"success":fnCallback,
"iFilteredTotal":iTotalDisplayRecords,
"iTotal":iTotalRecords,
"intval($_GET['sEcho'])":sEcho

});
$.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 );

fnCallback(json)

} );

}
} );
} );


/* Get the rows which are currently selected */
function fnGetSelected( oTableLocal )
{
var aReturn = new Array();
var aTrs = oTableLocal.fnGetNodes();

for ( var i=0 ; i

Rilmax

HTML code for the body:
[code]



Live example
Delete selected row




Rendering engine
Browser
Platform(s)
Engine version
CSS grade





Loading data from server





Rendering engine
Browser
Platform(s)
Engine version
CSS grade










[/code]

Rilmax

Here is the source.jsp:
[code]

<%@page import="java.util.*"%>

<%@page import="java.sql.*"%>
<%@page import="org.json.*"%>
<%
String[] cols = { "engine", "browser", "platform", "version", "grade" };
String table = "ajax";

JSONObject result = new JSONObject();
JSONArray array = new JSONArray();
int amount = 10;
int start = 0;
int echo = 0;
int col = 0;

String engine = "";
String browser = "";
String platform = "";
String version = "";
String grade = "";

String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sEcho = request.getParameter("sEcho");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");

engine = request.getParameter("sSearch_0");
browser = request.getParameter("sSearch_1");
platform = request.getParameter("sSearch_2");
version = request.getParameter("sSearch_3");
grade = request.getParameter("sSearch_4");

List sArray = new ArrayList();
if (!engine.equals("")) {
String sEngine = " engine like '%" + engine + "%'";
sArray.add(sEngine);
//or combine the above two steps as:
//sArray.add(" engine like '%" + engine + "%'");
//the same as followings
}
if (!browser.equals("")) {
String sBrowser = " browser like '%" + browser + "%'";
sArray.add(sBrowser);
}
if (!platform.equals("")) {
String sPlatform = " platform like '%" + platform + "%'";
sArray.add(sPlatform);
}
if (!version.equals("")) {
String sVersion = " version like '%" + version + "%'";
sArray.add(sVersion);
}
if (!grade.equals("")) {
String sGrade = " grade like '%" + grade + "%'";
sArray.add(sGrade);
}

String individualSearch = "";
if(sArray.size()==1){
individualSearch = sArray.get(0);
}else if(sArray.size()>1){
for(int i=0;i 100)
amount = 10;
}
if (sEcho != null) {
echo = Integer.parseInt(sEcho);
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 5)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}
String colName = cols[col];
int total = 0;

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("parameters ommited");
try {
String sql = "SELECT count(*) FROM "+table;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()){
total = rs.getInt("count(*)");
}
}catch(Exception e){

}
int totalAfterFilter = total;
result.put("sEcho",echo);

try {
String searchSQL = "";
String sql = "SELECT * FROM "+table;
String searchTerm = request.getParameter("sSearch");
String globeSearch = " where (engine like '%"+searchTerm+"%'"
+ " or browser like '%"+searchTerm+"%'"
+ " or platform like '%"+searchTerm+"%'"
+ " or version like '%"+searchTerm+"%'"
+ " or grade like '%"+searchTerm+"%')";
if(searchTerm!=""&&individualSearch!=""){
searchSQL = globeSearch + " and " + individualSearch;
}
else if(individualSearch!=""){
searchSQL = " where " + individualSearch;
}else if(searchTerm!=""){
searchSQL=globeSearch;
}
sql += searchSQL;
sql += " order by " + colName + " " + dir;
sql += " limit " + start + ", " + amount;

PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
JSONArray ja = new JSONArray();
ja.put(rs.getString("engine"));
ja.put(rs.getString("browser"));
ja.put(rs.getString("platform"));
ja.put(rs.getString("version"));
ja.put(rs.getString("grade"));
array.put(ja);
}
String sql2 = "SELECT count(*) FROM "+table;
if (searchTerm != "") {
sql2 += searchSQL;
PreparedStatement ps2 = conn.prepareStatement(sql2);
ResultSet rs2 = ps2.executeQuery();
if (rs2.next()) {
totalAfterFilter = rs2.getInt("count(*)");
}
}
result.put("iTotalRecords", total);
result.put("iTotalDisplayRecords", totalAfterFilter);
result.put("aaData", array);
response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
out.print(result);
System.out.println(result);
conn.close();
} catch (Exception e) {

}
%>
[/code]

jocapc

Hi,

You have a working example here http://www.codeproject.com/KB/java/JQuery-DataTables-Java.aspx (it works with companies instead of browsers). You can also download eclipse project.

Hope that this helps.

Regards,
Jovan

Rilmax

Thanx...It really helped. Am now having problem with redrawing my table after using a TableTools ajax button to delete records on the server-side. Deleting occurs at source2.jsp.
[code]
oTable = $("#example").dataTable({
"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "source.jsp",
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"sRowSelect": "single",
"aButtons": [
{
"sExtends": "copy",
"bSelectedOnly": "true"
},
{
"sExtends": "pdf",
"bSelectedOnly": "true"
},
{
"sExtends": "ajax",
"sButtonText": "Ajax",
"bHeader" : false,
"bFooter" : false,
"mColumns": [0,2],
"bSelectedOnly": "true",
"sAjaxUrl" : "source2.jsp",
"fnClick": function( nButton, oConfig ) {
var sData = this.fnGetTableData(oConfig);

var iId = sData[1];
if (confirm ('Do you really want to delete?')){
$.ajax( {
"url": oConfig.sAjaxUrl,
"data": [
{
"name": "tableData",
"value": sData
}
],
"success": oConfig.fnAjaxComplete,
"dataType": "json",
"type": "POST",
"cache": false,
"error": function () {
alert( "Error detected when sending table data to server" );
}
} );


oTable.fnDraw();

}
else {alert('Not deleted');}
}
},
"print",
{

"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]
}
]
},

"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
$.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 );

fnCallback(json)

} );
}
,
"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
if ( jQuery.inArray(aData[0], gaiSelected) != -1 )
{
$(nRow).addClass('row_selected');
}
return nRow;
},
"aoColumns": [
{ "bVisible": 0 }, /* ID column */
null,
null,
null,
null,
null
]
});

[/code]

jocapc

Hi,

There is another addon for data tables that handles add/edit/delete functionalities see http://www.codeproject.com/KB/java/J2EE-Editable-Web-Table.aspx article.

If you use this plugin it will handle delete/refresh you just need to add some servlet handler that will handle delete ajax call:

[code]
@WebServlet("/DeleteData")
public class DeleteData extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { }
}
[/code]

See more details in the article. Your code looks fine and probably there is some minor error that causes this. Mabe you cna try this plugin too there will be handeled delete actions and you don't need to worrie about this.

Regards,
Jovan

Rilmax

Thanks a lot for your prompt response. Have being enjoying the datatables experience so far, you've got no idea how much you've been of help to my Finals.

Rilmax.

Rilmax

I downloaded the zipped application on the site. I was woondering what 'static int nextID = 17;' was meant for. Thanks.

jocapc

It is just a simulation of autogenerated ids. I have put ids to increment each time new object is created and start from number 17 (nothing important).