Using apex-lang to build Soql statements

In my last post, I showed how to use the new Javascript Remoting functionality that was just released in Spring ’11. After reading a bit about the apex-lang project’s Soql Builder classes, I thought that I would try to rewrite my controller. While I wasn’t able to reduce the number of lines of code as I hoped I would, I think it made it more readable. The Soql Builder let me not worry about escaping single quotes to avoid Soql injection.

One area where I felt that I had to do more work than was necessary was working around filtering by additional fields. In my design, I wanted to be able to always filter by Name, and then by any optional additional fields passed into the controller. This created quite a bit more code than I was hoping to use, but it works!

I also took the time to do a little bit more error handling in the controller. I realized that any exceptions thrown could be seen in the calling Javascript. The controller now checks that all the additional fields passed in are actually fields in the object. If they aren’t it throws an error.

Without further ado, here’s my new controller:

global class autoCompleteController {

    public class applicationException extends Exception{}

    @RemoteAction
    global static SObject[] findSObjects(string obj, string qry, string addFields) {
 
        // check to see if the object passed is valid
        Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
        Schema.SObjectType sot = gd.get(obj); 
        if (sot == null) {
            throw new applicationException('Object name ' + obj + ' not valid');
            return null;    
        }       
        
        al.SoqlBuilder soqlB = new al.SoqlBuilder()
            .selectx('ID')
            .selectx('Name')
            .fromx(obj)
            .orderbyx(new al.OrderBy('name'))
            .limitx(20);
        
        //add the where statement. If no additional fields were sent, it is easy
        if (addFields == '') {
            soqlB.wherex(new al.FieldCondition('Name').likex(qry));
        }
        else
        {
            // Split the list of additional fields passed
            List<String> fieldList = addFields.split(',');
        
            // get a map of all fields in the object
            Map<String, Schema.SObjectField> gf = sot.getDescribe().fields.getMap();
      
            // Create the condition for the soql
            al.OrCondition orC = new al.OrCondition();
        
            // always search the Name field
            orC.add(new al.FieldCondition('Name').likex(qry));
        
            // loop through the list of fields to validate and add them to the condition
            for (String s : fieldList) {
                Schema.SObjectField sof = gf.get(s);
                if (sof == null) {
                    throw new applicationException('Additional Field ' + s + ' not valid for object ' + obj);
                    return null;
                }
                orC.add(new al.FieldCondition(s).likex(qry));
            }
            // add the list of additional fields to the soql
            soqlB.selectx(fieldList);
            // add the where clause
            soqlB.wherex(orC);
        }
            
        String soql = soqlB.toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators());
            
        List<sObject> L = new List<sObject>();
        try {
            L = Database.query(soql);
        }
        catch (QueryException e) {
            throw e;
            return null;
        }
        return L;
   }
}

So, there you have it. If you need to build dynamic Soql in apex, then take a look at apex-lang. It definitely helps avoid common mistakes when building Soql strings and makes the code more readable.

Advertisements