[SalesForce] How to properly use JQuery Datatable sAjaxSource with Apex providing JSON source

I'm trying to use sAjaxSource with server side processing from Apex class which will provide JSON as source.
First I was trying simple example, which won't work and won't output any error (not in Salesforce and not in JS console), I'm just getting empty JQuery DataTable.

Visualforce page:

<apex:page controller="vfDBTestController">

    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js" />
    <apex:stylesheet value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css"/>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js" />

    <apex:includeScript value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/js/jquery.dataTables.js')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/css/jquery.dataTables_themeroller.css')}" />  

    <script type="text/javascript">
        var j$ = jQuery.noConflict();
        j$(document).ready( function () 
        {
            j$('#tblid').dataTable(
            {
                "bProcessing"   : true,
                "sAjaxSource"   : {!JSONDataSource}
            } );
        } );
    </script>

    <apex:form >

        <table cellpadding="0" cellspacing="0" border="0" class="display" id="tblid">
            <thead>
                <th>cell1</th>
                <th>cell2</th>
            </thead>
            <tbody>

            </tbody>
        </table>

    </apex:form>

</apex:page>

Apex:

public with sharing class vfDBTestController 
{     
    public vfDBTestController(){}

    public String getJSONDataSource()
    {
        List<Account> lstAaData = [SELECT Id, Name FROM Account];

        // Create a JSONGenerator object.
        // Pass true to the constructor for pretty print formatting.
        JSONGenerator gen = JSON.createGenerator(true);

        // Write data to the JSON string.
        gen.writeStartObject();
        gen.writeFieldName('aaData');
        gen.writeStartArray();

        for (Account acc : lstAaData)
        {
            gen.writeStartObject();
            gen.writeStringField('cell1', acc.Name);
            gen.writeStringField('cell2', acc.Id);
            gen.writeEndObject();
        }
        gen.writeEndArray();
        gen.writeEndObject();

        String JSONString = gen.getAsString();
        system.debug('JSONString: ' + JSONString);
        return JSONString;
    }

}

JSON output from system debug:

{"aaData":[{"cell1" : "GenePoint","cell2" : "001b000000MTqJJAA1"},{"cell1" : "United Oil & Gas, UK","cell2" : "001b000000MTqJKAA1"},{"cell1" : "United Oil & Gas, Singapore","cell2" : "001b000000MTqJLAA1"}]}

Best Answer

I have found an answer with help from here. I will give a small example, which shows how to use this technique related to JQuery DataTable.

Note: this is only small example that shows how one can communicate with JQuery DataTable and Apex, to get a good grasp on the matter. Hope it will help more people who trying this.

Ajax Response VF page:

<apex:page controller="AjaxRespController"  action="{!retrieveData}"
    contentType="application/x-JavaScript; charset=utf-8" showHeader="false" standardStylesheets="false" sidebar="false">
{!JSONString}
</apex:page>

AjaxRespController:

public class AjaxRespController 
{
    public String JSONString {get;set;}

    public AjaxRespController() {}

    /** invoked on an Ajax request */   
    public void retrieveData() 
    {
        // We need those parameters to work with JQuery DataTable.
        Map<String, String> params = ApexPages.currentPage().getParameters();
        for (String key : params.keySet())
        {
            system.debug('Key: ' + key + ', Value: ' + params.get(key));
        }
        List<Account> lstAaData = [SELECT Id, Name FROM Account LIMIT 10];

        // Create a JSONGenerator object.
        // Pass true to the constructor for pretty print formatting.
        JSONGenerator gen = JSON.createGenerator(true);

        // Write data to the JSON string.
        gen.writeStartObject();
        gen.writeNumberField('sEcho', Integer.valueOf(params.get('sEcho')));
        gen.writeNumberField('iTotalRecords', 14);
        gen.writeNumberField('iTotalDisplayRecords', 14);
        gen.writeFieldName('aaData');       
        gen.writeStartArray();
        for (Account acc : lstAaData)
        {       
            gen.writeStartArray();
            gen.writeString(acc.Name);
            gen.writeString(acc.Id);
            gen.writeEndArray();

        }

        gen.writeEndArray();
        gen.writeEndObject();

        JSONString = gen.getAsString();     
    }

}

VF client page:

<apex:page>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js" />
    <apex:stylesheet value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css"/>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js" />

    <apex:includeScript value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/js/jquery.dataTables.js')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/css/jquery.dataTables_themeroller.css')}" />  

    <script type="text/javascript">

        var j$ = jQuery.noConflict();
        j$(document).ready( function () 
        {           
            j$('#tblid').dataTable(
            {
                "bProcessing"   : true,
                "bServerSide"   : true,
                "sAjaxSource"   : '{!$Page.AjaxResponder}?core.apexpages.devmode.url=1'
            } );
        } );

    </script>

    <apex:form >
        <table cellpadding="0" cellspacing="0" border="0" class="display" id="tblid">
            <thead>
                <th>Name</th>
                <th>Id</th>
            </thead>
            <tbody>

            </tbody>
        </table>
    </apex:form>
</apex:page>
Related Topic