+ Start a Discussion
Kenji775Kenji775 

Building a reusable JSON component

Hey all,

I am in the process of shifting a bunch of tools into salesforce. To do this basically I am rewritting the front ends in jQuery (and plugins) and creating Apex web services that fetch the required data and return it in JSON format to the front end components.

 

I want to write one component, I can pass it a query string, it returns me a JSON formatted object. However, I am not handy enough in Apex syntax to pull this off. I have a framework in place, but I don't know 2 things.

 

1) How do I make a query completly out of a URL variable.

2) How can I loop over all the column names returned in a query?

 

Below is the code I have so far, that has been the basic outline of my componts so far, but I want to stop having to make a new component for every tool. Any help is appreciated. Thank you.

 

 

public class queryToJSON { public JsonObject json {get;set;} /** invoked on an Ajax request */ public void getJson() { //There should be a parameter called QueryString in the URL. Map<string,string> params = ApexPages.currentPage().getParameters(); json = new JsonObject(); // Do SOQL query to see if there are any records ! List<Events__c> records = new List<Events__c>(); // Here I need a totally dynamic query, built from the URL query string variable. // theoretically it would look like this. // records = [params.get('queryString')]; if (!records.isEmpty()) { // Jsonify the results ! List<JSONObject.value> values = new List<JSONObject.value>(); for (Events__c c : records) { JSONObject cjson = new JSONObject(); //Now for every column in the query, I need to construct a new JSON "element" //I can do that statically by typing something like this //cjson.putOpt('"id"', new JSONObject.value(c.Id)); //That adds the id field to my JSON object. But I won't know all the columns that are //being queried for, so I am going to need to loop over all the columns returned for every row //and add them dynamically to my JSON object. values.add(new JSONObject.value(cjson)); } json.putOpt('events', new JSONObject.value(values)); } } // Returns the JSON result string public String getResult() { string jsonString = json.ValuetoString(); return jsonString; } }

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
dke01dke01

What i do is

1) Make a Visual Force page, set the  contentType="text/plain"  or 'application/json'

2) Have a controller for that page return an Array of Objects you ned

3) Have the Visual force page include a repeater conrol to constuct your xml or JSON

4)  Have you calling page with the jQuery controls   perform a ajax get on this json URL

 

 

Cheers

Dave

All Answers

gm_sfdc_powerdegm_sfdc_powerde

Sounds like you need to use dynamic SOQL here. You can find more information about dynamic SOQL here - http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_soql.htm

 

Having said that, you want to build a SOQL directly off a request parameter and I see a high risk of SOQL injection here.

Kenji775Kenji775

Yes, you are correct about the injection. I will be adding a token based security system to this WSDL, and also I hope to only allow for SOQL statments, which in my understand are select only. So worst case scenerio someone is able to query for data, which sucks, but isn't the end of the world.

 

Thank you for the info on dynamic SOQL. That should prove extremely useful.

Kenji775Kenji775

Okay, I am in the home stretch here. I just have to figure out how to get the value of a given column for the current object I am looping over. Currently when I use

 

String colValue = c.get(columnName); 

 

I get the error


Save error: Illegal assignment from Object to String       line 38  

 

I'm not sure why it's returning an object. It should just be getting the value of the given columnName. Here is my code.

 

 

public class queryToJSON { public JsonObject json {get;set;} /** invoked on an Ajax request */ public void getJson() { //There should be a parameter called QueryString in the URL. Map<string,string> params = ApexPages.currentPage().getParameters(); json = new JsonObject(); //Dynamic SOQL Query based off URL queryString param. use String.escapeSingleQuotes to help //prevent SOQL injection. List<sObject> records = Database.query(String.escapeSingleQuotes(params.get('queryString'))); if (!records.isEmpty()) { // Jsonify the results ! List<JSONObject.value> values = new List<JSONObject.value>(); //This is a mapping of the column names returned by the query Map<String, Schema.SObjectField> columns = records.getSObjectType().getDescribe().fields.getMap(); for (sObject c : records) { JSONObject cjson = new JSONObject(); //Now for every column in the query, I need to construct a new JSON "element" //I can do that statically by typing something like this //cjson.putOpt('"id"', new JSONObject.value(c.Id)); //That adds the id field to my JSON object. But I won't know all the columns that are //being queried for, so I am going to need to loop over all the columns returned for every row //and add them dynamically to my JSON object. id myId = c.id; for(String columnName : columns.keySet()) { try { //Try to get the value of the column name. If it isn't there, then it tosses //an error, no big deal. String colValue = c.get(columnName); cjson.putOpt('"'+columnName+'"', new JSONObject.value(colValue)); } catch(System.Exception e) { //just ignore. field is not available in query. } } values.add(new JSONObject.value(cjson)); } json.putOpt('"resultSet":', new JSONObject.value(values)); } } // Returns the JSON result string public String getResult() { string jsonString = json.ValuetoString(); return jsonString; } }

 

 

 

Message Edited by Kenji775 on 03-03-2010 10:53 AM
dke01dke01

What i do is

1) Make a Visual Force page, set the  contentType="text/plain"  or 'application/json'

2) Have a controller for that page return an Array of Objects you ned

3) Have the Visual force page include a repeater conrol to constuct your xml or JSON

4)  Have you calling page with the jQuery controls   perform a ajax get on this json URL

 

 

Cheers

Dave

This was selected as the best answer
Kenji775Kenji775

Wow... thats a simple and elegent solution. I feel rather foolish for writting this whole component now. Oh well, live and learn I guess. Where were you when I needed you days ago! Haha.

 

If anyone cares here is the mostly final build. Still need to handle URL encodings for special chars and quotes, as well as maybe add some security, but the basics are here.

 

public class queryToJSON {
String jsonReturn;

/** invoked on an Ajax request */
public void getJson()
{

//There should be a parameter called QueryString in the URL.
Map<string,string> params = ApexPages.currentPage().getParameters();
jsonReturn = '{ "queryString": "'+params.get('queryString')+'", ';

//Dynamic SOQL Query based off URL queryString param. use String.escapeSingleQuotes to help
//prevent SOQL injection.

if(params.get('queryString').length() > 1)
{
try
{
List<sObject> records = Database.query('select ' +String.escapeSingleQuotes(params.get('queryString')));


if (!records.isEmpty()) {

//This is a mapping of the column names returned by the query
Map<String, Schema.SObjectField> columns = records.getSObjectType().getDescribe().fields.getMap();
jsonReturn = jsonReturn + ' "resultSet": [';

for (sObject c : records)
{
//Now for every column in the query, I need to construct a new JSON "element"
//I can do that statically by typing something like this
//cjson.putOpt('"id"', new JSONObject.value(c.Id));

jsonReturn = jsonReturn + '{';
for(String columnName : columns.keySet())
{
try
{

//Try to get the value of the column name. If it isn't there, then it tosses
//an error, no big deal.

string cellVal = String.valueOf( c.get(columnName));
jsonReturn = jsonReturn + '"'+columnName+'": "'+cellVal+'",';
}
catch(System.Exception ex)
{
//jsonReturn = jsonReturn + '"'+columnName+'": "'+ex.getMessage()+'",';
}


}
jsonReturn = jsonReturn.substring(0, jsonReturn.length() - 1);
jsonReturn = jsonReturn + '},';

}
jsonReturn = jsonReturn.substring(0, jsonReturn.length() - 1);
}
}
catch(Exception ex)
{
jsonReturn = jsonReturn + '"ERROR": "'+ex.getMessage()+'"';
}
}
jsonReturn = jsonReturn + ']}';
}



// Returns the JSON result string
public String getResult()
{

return jsonReturn;
}

}

 

 

 

Message Edited by Kenji775 on 03-04-2010 09:06 AM
Farhat MirzaFarhat Mirza

I am facing problem to get lookup field value passed through dynamic soql.

for e,g my soql looks like this

AlertSoql ='Account__r.name from Alert__c'; // where Alert_c is custom object.

 

In the code snippet you gave it loops through custome fields but not through relation field,


 Map<String, Schema.SObjectField> columns = records.getSObjectType().getDescribe().fields.getMap();
                  
                    for (sObject c : records)
                    {
                        //Now for every column in the query, I need to construct a new JSON "element"
                        //I can do that statically by typing something like this
                        //cjson.putOpt('"id"', new JSONObject.value(c.Id));

                        jsonReturnString = jsonReturnString + '{';
                        for(String columnName : columns.keySet())
                        {
                             try
                             {

                                 //Try to get the value of the column name. If it isn't there, then it tosses
                                 //an error, no big deal.

                                 string cellVal = String.valueOf(c.get(columnName)); // the problem is here i am not able to get Account__r.name

                               
                                 
                                 //jsonReturnString = jsonReturnString + '"'+columnName+'": "'+cellVal.replace('"','\'')+'",';
                                 jsonReturnString = jsonReturnString + '"Account__r.name":'+cellVal.replace('"','\'')+'",';
                             }
                             catch(System.Exception ex)
                             {
                                 //jsonReturnString = jsonReturnString + '"'+columnName+'": "'+ex.getMessage()+'",';
                             }

                        }
                        jsonReturnString = jsonReturnString.substring(0, jsonReturnString.length() - 1);
                        jsonReturnString = jsonReturnString + '},';

                    }