Best way to get table data

_An0maly

Let me first explain the situation.
I have servlet which after sending a POST request returns a JSON string like this:

[code] {"sEcho":0,"totalRecords":1,"TotalDisplayRecords":10,"aaData":[{"messagecaseid":"xxxxxx","messagesubject":"xxxxx","messagesender":"xxxxxxx","messagereceiver":"xxxxxxx"}]} [/code]

I also have a JSP with a form which submits searchcriteria to the servlet.
I have loaded an empty datatable after the JSP page is done loading, then when I click the submit button I want the table
to refresh with the values returned from the servlet.

I've tried using sAjaxSource, this didn't work, after that I tried a static Json file and added this to the sAjaxSource, this didn't work
either. I'm kinda on the brink of giving up. I just don't know what I am doing wrong. Can someone please help.

JS I have now:

[code]
var table;

$(document).ready(function() {
table = $('#searchResults').dataTable({
"bServerSide": true,
"sAjaxSource": "/resources/json/json.txt",
"sPaginationType": "full_numbers",
"bJQueryUI": true,
"aoColumns": [
{ "mDataProp": "messagecaseid" },
{ "mDataProp": "messagesubject" },
{ "mDataProp": "messagesender" },
{ "mDataProp": "messagereceiver" }
]
});
$('.searchsubmit').click(function() {

});
});
[/code]

_An0maly

I've been working on the JS, here is my new version:

[code]
var table;

$(document).ready(function() {
generateTable();
$('.searchsubmit').click(function() {
var formData = $('form').serialize();
$.ajax({
type: "POST",
url: 'Search',
data: formData,
succes: function(data) {
tableReload(table, data);
},
dataType: 'json'
});

table.fnAddData(json.aaData);
});
});

function generateTable () {
table = $("#searchResults").dataTable({
"bFilter" : false,
"sPaginationType": "full_numbers",
"bJQueryUI": true,
"aoColumns": [
{"mDataProp": "messagecaseid"},
{"mDataProp" : "messagesubject"},
{"mDataProp" : "messagesender"},
{"mDataProp" : "messagereceiver"}
]
});
};

function reloadTable(table, data) {

}
[/code]

Now I only have to populate the table with the values in data. Get the feeling this solution is not
the best way. what is the preferred way of doing this.

allan

That looks okay - you could perhaps use DataTables sAjaxSource option and the fnReloadAjax plug-in to reduce the number of lines of code that you have - but it looks fine :-)

Regarding your server-side processing before - sEcho should never be zero. See http://datatables.net/usage/server-side for more information - but server-side processing is only really needed for tables with 50'000+ rows.

Allan

_An0maly

Allan, thanks for the comment, the backend MSSQL database i'm using can return alot more than 50.000 rows, that's why i'm doing the processing on the server-side.

I fixed the sEcho problem.

new JS:

[code]
$(document).ready(function() {
$("#searchResults").dataTable({
"bJQueryUI": true
});
$('.searchsubmit').click(function() {
var formData = $('form').serialize();
$("#searchResults").dataTable({
"bDestroy": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": 'Search',
"sServerMethod": "POST",
"aoColumns": [
{ "mDataProp": "messagecaseid" },
{ "mDataProp": "messagesubject" },
{ "mDataProp": "messagesender" },
{ "mDataProp": "messagereceiver" }
],
"fnServerParams": function ( aoData ) {
aoData.push({"name": "formData", "value": formData}
);
}

});
});
});
[/code]

Server response:

[code]
{"sEcho":1,"totalRecords":1,"TotalDisplayRecords":10,"aaData":[{"messagecaseid":"xxxxx","messagesubject":"xxxxx (xxxxx)","messagesender":"xxxxxxx","messagereceiver":"xxxxxxxxx"}]}
[/code]

Special characters that can returned by the server, @,(), as far as I know.
I'm still getting the "No data available in table" message.

Any Ideas?

_An0maly

allan

Your JSON doesn't contain the required properties - specifically iTotalRecords and iTotalDisplayRecords . Have a look at this server-side usage page for the definition of those two parameters: http://datatables.net/usage/server-side .

Allan

_An0maly

Okey, that was just plain stupid, haha. I renamed the properties with the following result.
[code]
{"sEcho":1,"iTotalRecords ":1,"iTotalDisplayRecords ":10,"aaData":[{"messagecaseid":"750269","messagesubject":"abonnee W1439922 CABLE_EASY","messagesender":"pjvanwijck@online.nl","messagereceiver":"ksonb1@online.nl"}]}
[/code]

I took a look at the different parameters at iTotalRecords it states that this is the number of rows in de database, I take it this is the total number of rows in the resultset i get from the database?

Still no luck with table data though. This is getting really pathetic :P

_An0maly

_An0maly

To investigate further I installed firebug to check the parameter values.

List of parameters and the values after filling in one of the form fields:
Is there

[code]
bRegex false
bRegex_0 false
bRegex_1 false
bRegex_2 false
bRegex_3 false
bSearchable_0 true
bSearchable_1 true
bSearchable_2 true
bSearchable_3 true
bSortable_0 true
bSortable_1 true
bSortable_2 true
bSortable_3 true
formData messagecaseid=710889&messagesubject=&messagesender=&messagereceiver=&date1=&date2=
iColumns 4
iDisplayLength 10
iDisplayStart 0
iSortCol_0 0
iSortingCols 1
mDataProp_0 messagecaseid
mDataProp_1 messagesubject
mDataProp_2 messagesender
mDataProp_3 messagereceiver
sColumns
sEcho 1
sSearch
sSearch_0
sSearch_1
sSearch_2
sSearch_3
sSortDir_0 asc
[/code]

Is there anything else I could check to make your life a little easier?

_An0maly

I figured out what is going wrong, I call the servlet, the servlet prints the JSON array, but when I look in firebug at the post request plus the answer I dont get the JSON array returned.

Any ideas?

Greetz.

_An0maly

Servletcode:

[code]
package servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.codehaus.jackson.JsonGenerationException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;


import classes.DBConnection;

public class Search extends HttpServlet
{
private static final long serialVersionUID = 1L;
private int echo;
private int totalrecords;
private int totalDisplayRecords;

public Search()
{
super();
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
Connection connect = new DBConnection().returnConnection();
Map searchParameterMap = seperateFormString(request.getParameter("formData"));
echo = Integer.parseInt(request.getParameter("sEcho"));
String sql = buildQuerytring(searchParameterMap);
ResultSet rs = executeQuery(connect, sql);


try {
List aaData = buildAaData(rs);
String JsonString = buildJsonResonse(aaData, echo);
response.setContentType("application/Json");
response.getWriter().print(JsonString);
} catch (SQLException e) {
e.printStackTrace();
}
}

private Map seperateFormString(String formData){

Map searchParameterMap = new HashMap();
String[] strings = formData.split("&");
for(String s : strings)
{
String[] parameters = s.split("=");
int value = parameters.length-1;
if(parameters.length>1){
searchParameterMap.put(parameters[0], parameters[value]);
}
}
return searchParameterMap;
}

private ResultSet executeQuery(Connection connect, String sql)
{
ResultSet rs = null;
try {
PreparedStatement presmt = connect.prepareStatement(sql);
rs = presmt.executeQuery();
}
catch (SQLException e) {
e.printStackTrace();
}
return rs;
}

private String buildQuerytring(Map map)
{
String select = "SELECT messagecaseid, messagesubject, messagesender, messagereceiver FROM berichtenarchief";
StringBuilder sb = new StringBuilder(select);
boolean mapIsEmpty = map.isEmpty();
int counter = 0;

if(mapIsEmpty == false)
{
for(Map.Entry entry : map.entrySet())
{
if(counter >= 1)
{
sb.append(" AND " + entry.getKey() + " = " + entry.getValue());
}
else if(counter == 0)
{
sb.append(" WHERE " + entry.getKey() + " = " + entry.getValue());
}
counter++;
}
}
System.out.println(sb.toString());
return sb.toString();
}

// private Map filterParameters(Map parameters)
// {
// Map searchParameters = new HashMap();
// for(Map.Entry entry : parameters.entrySet())
// {
// String[] value = entry.getValue();
// if(value[0].isEmpty() == false)
// {
// searchParameters.put(entry.getKey(), value[0]);
// }
// }
// return searchParameters;
// }

private List buildAaData(ResultSet rs) throws SQLException, JsonGenerationException, JsonMappingException, IOException
{
List JsonArray = new ArrayList();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
int rowCounter = 0;

while(rs.next())
{
Map JsonObject = new LinkedHashMap();
for(int colCounter = 1; colCounter<=colCount; colCounter++)
{
String JsonString = rsmd.getColumnName(colCounter);
String JsonValue = rs.getObject(colCounter).toString();
JsonObject.put(JsonString, JsonValue);
}
JsonArray.add(JsonObject);
rowCounter++;
}
this.totalrecords = rowCounter;
return JsonArray;
}

private String buildJsonResonse(List aaData, int echo) throws JsonGenerationException, JsonMappingException, IOException
{
totalDisplayRecords = 10;
Map jsonObject = new LinkedHashMap();
ObjectMapper mapper = new ObjectMapper();

jsonObject.put("sEcho", echo);
jsonObject.put("iTotalRecords ", totalrecords);
jsonObject.put("iTotalDisplayRecords ", totalDisplayRecords);
jsonObject.put("aaData", aaData);

return mapper.writeValueAsString(jsonObject);
}
}
[/code]

_An0maly

With Firebug I have come to the following conclusion. When sending the Ajax request I get the followig error:

// Do send the request
// This may raise an exception which is actually
// handled in jQuery.ajax (so no try/catch here)
xhr.send( ( s.hasContent && s.data ) || null );

When I resend the request using Firebug, I get the correct response.

_An0maly

I figured it out. I added return false at the end, so that the page does not refresh.

_An0maly

I want to thank you for your help. This plug-in is pretty cool once you get your head around it. :P