Replies

• _An0maly Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 63,498Questions: 1Answers: 10,471 Site admin

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 63,498Questions: 1Answers: 10,471 Site admin

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  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 Posts: 10Questions: 0Answers: 0

  June 2012

  I figured it out. I added return false at the end, so that the page does not refresh.
• _An0maly Posts: 10Questions: 0Answers: 0

  June 2012

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