Request for feedback: “Unit Test” Custom Object idea

I’m toying with the idea of adding a “unit test” custom object to apex-lang and I’d really like to know your thoughts. If possible, please read details below and leave a comment with your feedback.

My utopian vision for apex-lang is that it’s unit tests would succeed in any salesforce org capable of executing apex code. I’ve taken great care to make the unit tests as portable as possible; however, one area where this falls short is unit tests which must do DML. Those unit tests exercise the generic SObject handling methods and per the widely accepted pattern of creating data versus querying for it, they create records -> usually on Account or Contact. The problem with this approach of course, is these unit tests will fail when a required field is added to objects like Account or Contact.

Which brings me to my idea: inclusion of a custom object in apex-lang whose sole purpose is make unit tests which require SObject instances more predictable. The custom object would never be used in any practical sense other than in unit tests. I would also add as many fields as there are distinct field types; in other words, there’d be a string field, a date field, a multi-select picklist field and so on. In addition to making apex-lang unit tests more predictable, I think there’s a good use case for consumers of apex-lang who write generic SObject handling code. I do this often, like in the geocoding toolkit I created for a Cloudspokes challenge.

The first downside is I’d be violating another vision of mine for apex-lang: that it consist of apex code and apex code only. Probably not a big deal to throw in a custom object and I need to just get over it. The other downside I can think of is apex-lang would be burning a custom object. But it seems to me that most orgs have plenty to spare so that’s probably not a big deal either.

Thoughts?

Posted in Uncategorized. 3 Comments »

apex-lang 1.17 released

I’m happy to announce I’ve released version 1.17 of apex-lang! It’s been a while since I’ve published a release so this one has a substantial amount of new features.

Links for apex-lang version 1.17

Changes since 1.15

Once you install version 1.17, any of the following code snippets can be run in your org. Note: the snippets assume you installed the managed package; if you instead installed the code directly to your org, remove the “al.” prefix before running.

DatabaseUtils

New class that allows records to be easily retrieved by object id(s) alone; it even allows you to do “select *” if you so choose.  The query method has magic behind it to determine object name of the corresponding id(s).

//insert an account
final Account x = new Account(Name='Test 123', NumberOfEmployees=1);
insert x;

//re-load the account by id only, following call is analogous to select *
final Account y = (Account) al.DatabaseUtils.query(x.id);
System.assertEquals(x.id,y.id);
System.assertEquals(x.name,y.name);
System.assertEquals(x.NumberOfEmployees,y.NumberOfEmployees);

//of course, the more responsible thing to do is to specify the fields you want
final Account z = (Account) al.DatabaseUtils.query(x.id,new Set{'name'});
System.assertEquals(x.id,z.id);
System.assertEquals(x.name,z.name);
//uncomment the following row and you'll get an exception because NumberOfEmployees wasn't retrieved
//System.assertEquals(x.NumberOfEmployees,z.NumberOfEmployees);

//the magic behind this code is the retrieveObjectName() method
//it's probably useful in other context as well
final String objectName = al.DatabaseUtils.retrieveObjectName(''+x.id);
System.assertEquals('account',StringUtils.lowerCase(objectName));

HttpUtils

A new class which enables 100% coverage of web service callouts.  Per this note, the Http.send() method cannot be executed in unit tests.  It’s really a shame that salesforce hasn’t created a mechanism for getting around this similar to Test.setFixedSearchResults() for SOSL queries.  I thought they would’ve by now but alas, no progress.  I’ve seen lots of good ways for getting around this; however, most involve muddying up your real code with test related code (by real code, I mean code that isn’t a unit test); in other words, there’s an if(Test.isRunningTest()) check somewhere in your real code.  I’d much rather keep all my unit testing logic in my unit tests themselves and that’s precisely what HttpUtils allows you to do.  See following example for an explanation.


//save this class and execute unit tests
public class MyService{
    private static final String TEST_RESPONSE = 'a response string - might be xml, json, etc...';
    private HttpRequest request;

    public MyService(){
        request = new HttpRequest();
        request.setEndpoint('http://somewhere.com');
        request.setMethod('GET');
    }

    public String theWayYoureProbablyInvokingCallout(){
        String response = null;
        //following line is such a hack; this is test code in your real code!
        if(Test.isRunningTest()) response = TEST_RESPONSE;
        //but you have to do it because the following line can't be covered #insanity
        else response = (new Http()).send(request).getBody();
        return response;
    }

    public String sameResultButEasierToTest(){
        return al.HttpUtils.sendReturnBody(request);
    }

    private static testmethod void test_theWayYoureProbablyInvokingCallout(){
        System.assertEquals(TEST_RESPONSE,new MyService().theWayYoureProbablyInvokingCallout());
    }

    private static testmethod void test_sameResultButEasierToTest(){
        //Note the call to push test here.  Your pushing your expected result immediately before you check for it.
        al.HttpUtils.pushTest(TEST_RESPONSE);
        System.assertEquals(TEST_RESPONSE,new MyService().sameResultButEasierToTest());
    }

}

Here are the send methods you can invoke on HttpUtils:

  • global static HttpResponse send(HttpRequest request)
  • global static String sendReturnBody(HttpRequest request)
  • global static Dom.Document sendReturnDocument(HttpRequest request)
  • global static XmlStreamReader sendReturnReader(HttpRequest request)

SObjectSortByFieldComparator

Another new class that simplifies doing in-memory sort of SObjects (vs using ArrayUtils.qsort).  You simply give the class a list of SObjects and tell it the field to sort against.


//sort a list of accounts on field NumberOfEmployees
List unsorted = new List{
     new Account(name='A',NumberOfEmployees=2)
    ,new Account(name='D',NumberOfEmployees=3)
    ,new Account(name='C',NumberOfEmployees=0)
    ,new Account(name='B',NumberOfEmployees=1)
};
List sorted = al.SObjectSortByFieldComparator.qsort(unsorted,'NumberOfEmployees');
System.assertNotEquals(null,sorted);
System.assertEquals(4,sorted.size());
System.assertEquals(0,sorted.get(0).get('NumberOfEmployees'));
System.assertEquals(1,sorted.get(1).get('NumberOfEmployees'));
System.assertEquals(2,sorted.get(2).get('NumberOfEmployees'));
System.assertEquals(3,sorted.get(3).get('NumberOfEmployees'));

//you could even mix SObjects so long as they share a field
unsorted = new List{
     new Account(name='A')
    ,new Opportunity(name='D')
    ,new Document(name='C')
    ,new Campaign(name='B')
};
//sort is on name field by default; false means sort descending
sorted = al.SObjectSortByFieldComparator.qsort(unsorted, false);
System.assertNotEquals(null,sorted);
System.assertEquals(4,sorted.size());
System.assertEquals('D',sorted.get(0).get('name'));
System.assertEquals('C',sorted.get(1).get('name'));
System.assertEquals('B',sorted.get(2).get('name'));
System.assertEquals('A',sorted.get(3).get('name'));

MapUtils

Added methods for doing trim, lowerCase, upperCase en masse to keys or values on a map.

al.MapUtils.assertEquals(
  new Map{'a'=>'Y','b'=>'Z'}
  ,al.MapUtils.lowerCaseKeys(new Map{'A'=>'Y','B'=>'Z'})
);

al.MapUtils.assertEquals(
  new Map{'a'=>'Y','b'=>'Z'}
  ,al.MapUtils.upperCaseValues(new Map{'a'=>'y','b'=>'z'})
);

SetUtils

Added methods to make switching between lists and sets easier; trim, lowerCase, upperCase en masse methods; new method pluckString.

//switching between String sets and lists
final Set myStrSet1 = new Set{'a','b'};
final Set myStrSet2 = al.SetUtils.listToSet(al.SetUtils.setToList(myStrSet1));
System.assertEquals(myStrSet1,myStrSet2);

//switching between SObject sets and lists
final Set mySObjSet1 = new Set{new Account(name='a'),new Account(name='b')};
final Set mySObjSet2 = al.SetUtils.listToSet(al.SetUtils.setToList(mySObjSet1));
System.assertEquals(mySObjSet1,mySObjSet2);

//plucking an attribute from a set of SObjects
System.assertEquals(myStrSet1,al.SetUtils.pluckString(mySObjSet1,'name'));

//trim and lower case all values in a set
System.assertEquals(myStrSet1,al.SetUtils.lowerCase(al.SetUtils.trim(new Set{'  A\n\t  ','  B '})));

StringUtils

Added a new stripMarkup() method which will remove all HTML/XML markup from a string.

System.assertEquals(null, al.StringUtils.stripMarkup(null));
System.assertEquals('', al.StringUtils.stripMarkup(''));
System.assertEquals('Title! ABC 123', al.StringUtils.stripMarkup('  Title!   ABC 123  '));
System.assertEquals('Title! ABC 123', al.StringUtils.stripMarkup('<html><body><h1>Title!</h1><p>ABC 123</p></html>'));

Note about test coverage

I think one of the best decisions salesforce made when designing Apex was to require 75% code coverage. Salesforce doesn’t get enough good publicity for this because that decision has forced many folks – myself certainly included – to become better developers. With the unit tests for apex-lang, I decided to buy into the Test Driven Development philosophy even further. My standard has been (1) every line must be tested (100% code coverage) and (2) the unit tests actually verify functionality. That latter part is key because code coverage in and of itself really doesn’t buy you anything – it’s the verification that adds value.

That being said, I wanted to note an exception to this rule that I’ve introduced in 1.17 and that’s the HttpUtils class. Like I said above, the Http.send() method cannot be called in a unit test; however, in order for the HttpUtils class to be of value, it obviously needs to call it. So, in the interest of making unit testing eaiser, I’ve sacrificed some of apex-lang’s code coverage by a small number: I believe HttpUtils is 98% covered. But it’s not 100% so that’s why I mentioned it.

Posted in Uncategorized. 5 Comments »

apex-lang 1.15 released

Thank you Joel Dietz for contributing to apex-lang!  Joel added aggregate functions to SoqlBuilder and here is an explanation of those changes: http://d3developer.com/2011/01/11/soql-builder-enhancements/.

For anyone else that would like to contribute to apex-lang, please don’t hesitate to contact me.

Posted in Uncategorized. No Comments »

Dreamforce session: Using Code Share Projects in Force.com

If you’re planning on attending Dreamforce, I’ll be presenting features from apex-lang during the session “Using Code Share Projects in Force.com” and would love for you to be there. The session is during the last time slot (save the best for last right?) on Thursday, Dec 6 from 11am-12pm in the Force.com Theater.

Thankful for EDL Consulting

And while I’m on the subject of apex-lang, I’d like to thank Bill Loumpouridis and Craig Traxler at EDL for giving me the freedom and support to create apex-lang. I’ve used many open source projects and apex-lang is my way of giving back to the community. I recognize that other companies might not grant me that freedom and support and for that, I’m certainly thankful EDL Consulting. If you’re looking for a great place to work, look no further.

Abstract: Using Code Share Projects in Force.com

Force.com code share projects are a great resource to enhance developer productivity when building custom apps. So why reinvent the wheel when you don’t have to? This session begins with an introduction the Force.com code share initiative, and then transitions to how you can take advantage and contribute to projects. We’ll also provide plenty of technical knowledge on how to utilize the apex-lang Code Share Project inspired by the Apache Commons Lang library. Apex-lang’s goal is to make Apex programming easier by providing a host of helper utilities for the base Apex API, notably string manipulation methods, basic numerical methods, pagination and construction of complex SOQL queries.

Template for Global Configuration Variables in Apex

Summary: This article discusses a pattern or template for enabling and customizing features in an application developed upon the Force.com platform.

Force.com and “configurability”: new expecations for developers

A great thing about developing on the Force.com platform is how configurable the product is out of the box. Need a new field on a screen? Easy, find the right page layout and modify it. Want a field to be required? Go find the field and make it required. It’s crazy easy. So easy in fact, that more and more folks can contribute to the solution of a system. Even folks without an IT background that 5-10 years ago would never make these types of configuration changes are doing it. Can you imagine Joe the BA adding an Oracle constraint to make a field required? Of course not.

The downside to this amazing “configurability” of the Force.com platform however, is it creates a disconnect between developers who are not used to including this level of configurability, and salesforce users who have grown to love this type of configuration and now expect it in anything associated with salesforce. Below I discuss a “global configuration variable” pattern that makes adding configurability to your solution simple, clean and reliable.

What’s a global configuration variable?

A global variable (as opposed to a global configuration variable) is a variable which is accessible from any scope. It’s widely accepted that using global variables is a bad practice because those variables can easily be modified from anywhere within your codebase and as a result, using them becomes chaotic and difficult to debug. While similar in that its accessible from any scope, a global configuration variable should also be:

  1. read only (because it would be a bad practice if it weren’t)
  2. type cast (client code shouldn’t be burdened with type-casting a string to an integer)
  3. cached (each variable shouldn’t be loaded each time it’s accessed)
  4. sanity checked upon load (for example, a timeout shouldn’t be negative so default to 0)
  5. easily changeable in production (Joe the BA should be able to do it)

Usage

Best explained in a diagram:

What components are included?

  • GlobalConfigurationVariable__c.object: Defines the data store for the global configuration variables (I think I just sounded like George W Bush saying “the google”). If you’re unfamiliar with custom settings, essentially they are specialized custom objects whose records are cached in the Force.com application tier and accessing those records doesn’t burn any governor limits.
  • GlobalConfigurationVariables.cls: The main class which you’ll need to modify with your configuration variables. The example template is show below.
  • GlobalConfigurationVariablesTest.cls: Test class which you’ll need to modify as well.
  • GlobalConfigurationVariableTestUtils.cls: No need to modify. Unit test helper that makes achieving 100% code coverage much easier.
  • GlobalConfigurationVariableTestUtilsTest.cls: No need to modify. Unit test helper’s test class (yeah, I know the name is confusing but you shouldn’t need to touch this class or the unit test helper class).

GlobalConfigurationVariables.cls

/**
 * Please consider this class a template.  You will need to customize
 * this class according to the global variables you might require in
 * your application.  Also, you might consider creating multiple
 * classes similar to this broken down by global configuration variables that make
 * sense to be packaged together.
 *
 * First (1), define your global configuration variables as member variables of this class.
 * For example, if you're defining a twitter username as a global variable, then
 * the variable might be:
 *
 * public String twitterUsername {get; private set;}
 *
 * Next (2), define the key for your global variable.  This is the value which
 * must be entered as the name of the "Global Variable" custom settings record
 * defined at:
 *
 * Setup | App Setup | Develop | Custom Settings | Global Variable | Manage
 * (NOTE: if you haven't deployed that Custom Settings object yet, then obviously
 * the above won't be available.)
 *
 * The key can be the same value as member variable but that isn't required.
 * Using same example as above, the variable key might be:
 *
 * public static final String KEY_TWITTER_USERNAME = 'twitter.username';
 *
 * Third (3), define how the variable will be loaded.  Included are two
 * methods which read Strings and Integers.  If you have a different data type,
 * definie a different "retrieve" method.
 *
 * And last (4), consider putting in some sanity checking.  For example, if
 * you have a timeout variable defined as integer, perhaps it's a good idea
 * to make sure it's > 0 but less than 60000 milliseconds.
 *
 * Once your configuration variables are defined, you should access the configuration variables in your
 * code via the following:
 *
 * GlobalConfigurationVariables.getInstance().twitterUsername
 *
 * And I suggest you always call getInstance and you never store a reference of
 * the GlobalConfigurationVariables object.  That way, you can be assured that all transactions
 * will get the latest copy of the configuration variables AND only do it once.
 *
 * @author Richard Vanhook
 */
global class GlobalConfigurationVariables {

    //==================================================
    // (1) Define your configuration variables
    //==================================================
    global String exampleString {get; private set;}
    global Integer exampleInteger {get; private set;}

    //==================================================
    // (2) Define variable keys
    //==================================================
    global static final String KEY_STRING_EXAMPLE = 'ExampleString';
    global static final String KEY_INTEGER_EXAMPLE = 'ExampleInteger';

    private GlobalConfigurationVariables(){
        final Map<String,GlobalConfigurationVariable__c> all = GlobalConfigurationVariable__c.getAll();
        //==================================================
        // (3) Load configuration variables
        //==================================================
        exampleString = retrieveString(KEY_STRING_EXAMPLE, all);
        exampleInteger = retrieveInteger(KEY_INTEGER_EXAMPLE, all);

        //==================================================
        // (4) Do some sanity checking
        //==================================================
        if(exampleInteger == null || exampleInteger < 0 || exampleInteger > 60000){
            exampleInteger = 1000;
        }
    }

    //==================================================
    // HELPER METHODS
    //==================================================
    global static GlobalConfigurationVariables instance;

    global static GlobalConfigurationVariables getInstance(){
        if(instance == null){
            instance = new GlobalConfigurationVariables();
        }
        return instance;
    }

    private static Integer retrieveInteger(String key, Map<String,GlobalConfigurationVariable__c> all){
        Integer returnValue = null;
        if(all != null && !isBlank(key) && all.get(key) != null){
            try{
                if(all.get(key).value__c != null){
                    returnValue = Integer.valueOf(all.get(key).value__c);
                }
            }catch(System.TypeException e){}
        }
        return returnValue;
    }

    private static String retrieveString(String key, Map<String,GlobalConfigurationVariable__c> all){
        String returnValue = null;
        if(all != null && !isBlank(key) && all.get(key) != null){
            returnValue = all.get(key).value__c;
        }
        return returnValue;
    }

    private static boolean isBlank(String str) {
        return str == null || str.trim() == null || str.trim().length() == 0;
    }

}

Component install instructions:

  1. Unzip GlobalConfigurationVariables.zip to a local folder
  2. Open Eclipse and set up a Force.com project (steps for doing this are outside the scope of this article)
  3. Include object metadata components in your project (via right-click project -> Force.com -> Add/Remove Metadata Components)
  4. Drag and drop file GlobalConfigurationVariable__c.object into your objects folder
  5. Drag and drop class files into your classes folder

VisualForce HTML5 Template

Below is the simplest VisualForce page I’ve been able to construct that renders valid html5 markup (per http://html5.validator.nu). Controlling the exact output rendered by a VF page is tricky business; especially so for generating doctypes. The trick here is getting the page attributes right, using apex:outputText unescaped, and returning the doctype via a binding. I don’t think it matters from a validation perspective but a space unfortunately precedes the doctype declaration. If anyone know of an even simpler way to do this, please share!

<apex:page standardStylesheets="false"
    sidebar="false"
    showHeader="false"
    contentType="text/html" cache="true"><apex:outputText escape="false" value="{!"<!DOCTYPE html>"}"/>
<html>
    <head>
        <title>test</title>
    </head>
    <body>test</body>
</html>
</apex:page>

And here’s the report saying its valid:

Error Notifications in Apex

When an error occurs in any language or platform, it’s important to know (1) an issue even occurred and then (2) as much context regarding the error as possible. Curiously though, I’ve observed that most folks doing Apex development don’t build into their solution any error reporting. That’s a pretty big oversight in my opinion and in order to hopefully make it easier, I’ve attempted to create a really simple mechanism for reporting an error in Apex.

Here’s the solution zipped up as SimpleApexErrorNotification.zip which contains the following files:

  • ErrorUtils.cls
  • ErrorUtils.cls-meta.xml
  • ErrorUtilsTest.cls
  • ErrorUtilsTest.cls-meta.xml
  • Custom_Code_Support_Email__c.object

To install:

  1. Unzip SimpleApexErrorNotification.zip to a local folder
  2. Open Eclipse and set up a Force.com project (steps for doing this are outside the scope of this article)
  3. Include object metadata components in your project (via right-click project -> Force.com -> Add/Remove Metadata Components)
  4. Drag and drop file Custom_Code_Support_Email__c.object into your objects folder
  5. Drag and drop class files into your classes folder

To utilize:

First, whenever you have code for which you’d like a notification if it failed, wrap that code with a try/catch per following.

try{
    //do something which, if it fails, you'd like to know
} catch (Exception e){
    ErrorUtils.handleError(e);
    //throw e; //depending on situation, keep in mind you might want to re-throw exception
}

Next, set up your email address to receive alerts via following:

  1. Login to salesforce
  2. Go to Setup | App Setup | Develop | Custom Settings
  3. Click the Manage link for object “Custom Code Support Email”
  4. Click New
  5. Enter an email in both fields
  6. Click save

To ensure everything is set up properly, run the following code anonymously:

Exception sampleException = null;
try{
     sampleException.getMessage();
}catch(System.NullPointerException e){
     sampleException = e;
}
ErrorUtils.handleError(sampleException);

and you should receive an email alert similar to the following:

Code coverage:

The ErrorUtilsTest class is the companion test class for ErrorUtils and without any modifications, the test should run with 100% code coverage across ErrorUtils.

Want to hear about apex-lang at Dreamforce 2010?

Then please vote for it here!

Posted in Uncategorized. No Comments »

apex-lang 1.12 released

Biggest modification is the addition of SoqlBuilder classes.

SOQL Builder

It’s been my observation that one of the most error prone pieces of code any force.com developer will write is the code which builds a SOQL query. Think about it. How many times have you forgotten a comma, misplaced an AND, mis-formatted a date string, or worst of all, forgotten to escape user input?

These types of mistakes are silly for several reasons. First, they happen to everyone, second, they seem to happen over and over again – mainly during development but even still, that’s wasted time, and wasted time is wasted money. And the worst part is apex is a good enough language that the rules governing SOQL syntax should be apparent via a clear API. Something where many mistakes could be caught at compile-time – and just in case an error did occur at run-time, something that pinpoints the error better than “hey, your SOQL is invalid”.

My response is a concept called the “SOQL Builder”: a set of classes contained in the apex-lang library whose main class is appropriately called SoqlBuilder. My hope is this set of classes will alleviate force.com developers of having to remember the mundane details of SOQL syntax and provide them a developer friendly tool for creating SOQL. This article is quick introduction to SoqlBuilder followed by a full reference. I hope you find SoqlBuilder useful and if you have any suggestions for improvement, please don’t hesitate to let me know!

Basic Example

Let me start by showing a quick example. Given this SOQL query:

SELECT name FROM account WHERE employees < 10

Here is the corresponding code to create the query string via a SoqlBuilder:

new al.SoqlBuilder()
 .selectx('name')
 .fromx('account')
 .wherex(new al.FieldCondition('employees').lessThan(10))
 .toSoql();


NOTE: the above code sample assumes you’ve installed the managed package version of apex-lang. If you’ve directly included the apex-lang code in your org, then remove all al. namespace prefix references.

Right off the bat, you probably noticed the x’s at the end of some of the methods. Unfortunately, “select”, “from”, and “where” are all reserved words in apex and cannot be used as method identifiers. In order to get around this, I chose to append an x to the end of the method name (this convention is followed throughout other classes as well). Wish I didn’t need to append the x, but it is what it is. Another thing you might have noticed is the methods are “chained”. That helps make this code less verbose and feel more like a SOQL statement.

Benefits

Here’s a list of benefits to building SOQL queries using SoqlBuilder:

1. Reduces the risk of a silly SOQL grammar error
2. More readable / less verbose code
3. Automatic literal conversion
4. String escaping by default
5. Easy wild-carding

The first benefit is the reduced risk of misplacing an element. That’s the “silly” class of mistakes I mentioned earlier and whether it’s a parenthesis here or a comma there, it’s surprisingly easy to do. All String concatenation with SoqlBuilder takes place inside the super intelligent toSoql() method.

While it might not be true for simple cases (like the example above), using a SoqlBuilder will make your code less verbose and as a result, more readable. The construction of most real-world soql queries is an ugly, un-readable mess of String concatenation. Perhaps you’ve written or seen code like the following:

final Datetime aDatetime = DateTime.newInstance(2010,1,1,1,1,1);
final String aName = 'O\'Neal';
final List<String> aList = new List<String>{'Apparel','Auto'};
String soql = 'SELECT id,name,ispartner,industry';
soql += ' FROM account';
soql += ' WHERE CreatedDate < ';
soql += aDatetime.format('yyyy-MM-dd') + 'T' + aDatetime.format('hh:mm:ss') + 'Z';
soql += ' AND Name like \'%';
soql += String.escapeSingleQuotes(aName);
soql += '%\' AND industry INCLUDES (';
Boolean isFirst = true;
for(String anItem : aList){
 if(isFirst){
 isFirst = false;
 } else {
 soql += ',';
 }
 soql += '\'';
 soql += anItem;
 soql += '\'';
}
soql += ')';
System.debug(soql);

There’s no other way to put it: dude, that’s ugly code!

Here’s how the same query can be constructed using SoqlBuilder:

final Datetime aDatetime = DateTime.newInstance(2010,1,1,1,1,1);
final String aName = 'acme';
final List<String> aList = new List<String>{'Apparel','Auto'};
String soql = new al.SoqlBuilder()
  .selectx(new Set<Object>{'id','name','ispartner','industry'})
  .fromx('account')
  .wherex(new al.AndCondition()
    .add(new al.FieldCondition('CreatedDate').lessThan(aDatetime))
    .add(new al.FieldCondition('Name').likex(aName))
    .add(new al.SetCondition('industry').includes(aList))
  )
  .toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators());
System.debug(soql);

The third benefit is automatic conversion of literals. From the example above (the SoqlBuilder portion), notice how the aDatetime variable is simply passed to the lessThan() method? The FieldCondition handles converting the date time to the appropriate format. Just in case you’re curious where that code is, see the toLiteral() method in the SoqlUtils class.

Another benefit which can be seen in the previous example is automatic escaping of single quotes. Notice that the aName variable is simply passed to the likex() method. When toSoql() is executed, all single quotes in all Strings will be automatically escaped! Imagine how much the AppExchange Security Review Team will like that!

Also, the previous example also shows how easily all Strings in LIKE operators can be wild-carded. By default, the “wildcardStringsInLikeOperators” property is set to FALSE. However, if you call the wildcardStringsInLikeOperators() method on a new SoqlOptions object, then it will do just that: all strings will be wild-carded on both sides.

Reference

For all examples below, the new al.SoqlBuilder() and .toSoql() portions are omitted. If you’d like to run one of the examples – using anonymous execute for example – then use the following snippet:

System.debug('\n\n'
+ new al.SoqlBuilder()
//insert example from below here
.toSoql()
+ '\n\n');



SELECT



Selecting Fields

.selectx('ID')
.selectx('Name')
.fromx('Account')
//-> SELECT Name,ID FROM Account
.selectx(new Set<String>{'ID','Name'})
 .fromx('Account')
 //-> SELECT Name,ID FROM Account
.selectx(new List<String>{'ID','Name'})
.fromx('Account')
//-> SELECT Name,ID FROM Account
.fromx('Account')
//-> SELECT ID FROM Account

count()

.selectCount()
.fromx('Account')
//-> SELECT count() FROM Account

toLabel

.selectx(new Field('Rating').toLabelx())
.fromx('Account')
//-> SELECT toLabel(Rating) FROM Account

Relationship Queries

.selectx('id')
.selectx(
  new al.SoqlBuilder()
  .selectx('id')
  .fromx('OpportunityLineItems'))
.fromx('Opportunity')
//-> SELECT id,(SELECT id FROM OpportunityLineItems) FROM Opportunity



FROM



.fromx('account')
//-> SELECT id FROM account
.fromx('Contact c, c.Account a')
//-> SELECT id FROM Contact c, c.Account a



WHERE



Field Condition

/*
You can create a field condition using any of the following formats:
new al.FieldCondition().field(fieldName).operator(value)
new al.FieldCondition(fieldName).operator(value)
new al.FieldCondition(fieldName,Operator,value)
*/
//the following four examples are equivalent:
.fromx('account').wherex(new al.FieldCondition().field('name').equals('acme'))
.fromx('account').wherex(new al.FieldCondition('name').equals('acme'))
.fromx('account').wherex(new al.FieldCondition('name',al.Operator.EQUALS,'acme'))
.fromx('account').wherex(new al.FieldCondition('name','acme'))  //special case only valid for equals
//-> SELECT id FROM account WHERE name = 'acme'

Field Operators (using Operator as constructor argument)

/*
+--------------------------+----------+
| enum value               | operator |
+--------------------------+----------+
| EQUALS                   | =        |
| NOT_EQUALS               | !=       |
| LESS_THAN                | <        |
| LESS_THAN_OR_EQUAL_TO    | <=       |
| GREATER_THAN             | >        |
| GREATER_THAN_OR_EQUAL_TO | >=       |
| LIKEX                    | like     |
+--------------------------+----------+
*/
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.EQUALS,1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.NOT_EQUALS,1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.LESS_THAN,1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.LESS_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.GREATER_THAN,1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new al.FieldCondition('employees',al.Operator.GREATER_THAN_OR_EQUAL_TO,1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new al.FieldCondition('name',al.Operator.LIKEX,'acme'))
//-> SELECT id FROM account WHERE name like 'acme'

Field Operators (operator as method identifier)

/*
+----------------------+----------+
| method identifier    | operator |
+----------------------+----------+
| equals               | =        |
| notEquals            | !=       |
| lessThan             | <        |
| lessThanOrEqualTo    | <=       |
| greaterThan          | >        |
| greaterThanOrEqualTo | >=       |
| likex                | like     |
+----------------------+----------+
*/
.fromx('account').wherex(new al.FieldCondition('employees').equals(1))
//-> SELECT id FROM account WHERE employees = 1
.fromx('account').wherex(new al.FieldCondition('employees').notEquals(1))
//-> SELECT id FROM account WHERE employees != 1
.fromx('account').wherex(new al.FieldCondition('employees').lessThan(1))
//-> SELECT id FROM account WHERE employees < 1
.fromx('account').wherex(new al.FieldCondition('employees').lessThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees <= 1
.fromx('account').wherex(new al.FieldCondition('employees'.greaterThan(1))
//-> SELECT id FROM account WHERE employees > 1
.fromx('account').wherex(new al.FieldCondition('employees').greaterThanOrEqualTo(1))
//-> SELECT id FROM account WHERE employees >= 1
.fromx('account').wherex(new al.FieldCondition('name').likex('acme'))
//-> SELECT id FROM account WHERE name like 'acme'

Set Conditions

/*
You can create a set condition using any of the following formats:
new al.SetCondition().field(fieldName).operator(values)
new al.SetCondition(fieldName).operator(values)
new al.SetCondition(fieldName,Operator,values)
*/
//the following three examples are equivalent:
.fromx('account').wherex(new al.SetCondition().field('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new al.SetCondition('x').includes(new List<Object>{1,2}))
.fromx('account').wherex(new al.SetCondition('x',al.Operator.INCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)

Set Operators (using Operator as constructor argument)

/*
+------------+----------+
| enum value | operator |
+------------+----------+
| INCLUDES   | includes |
| EXCLUDES   | excludes |
| INX        | in       |
| NOT_IN     | not in   |
+------------+----------+
*/
.fromx('account').wherex(new al.SetCondition('x',al.Operator.INCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.Operator.EXCLUDES,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.Operator.INX,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new al.SetCondition('x',al.Operator.NOT_IN,new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x NOT IN (1,2)

Set Operators (operator as method identifier)

/*
+-------------------+----------+
| method identifier | operator |
+-------------------+----------+
| includes          | includes |
| excludes          | excludes |
| inx               | in       |
| notin             | not in   |
+-------------------+----------+
*/
.fromx('account').wherex(new al.SetCondition('x').includes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x INCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x').excludes(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x EXCLUDES (1,2)
.fromx('account').wherex(new al.SetCondition('x').inx(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x IN (1,2)
.fromx('account').wherex(new al.SetCondition('x').notIn(new List<Object>{1,2}))
//-> SELECT id FROM account WHERE x NOT IN (1,2)

Primitives to String literals

//null
.fromx('account').wherex(new al.FieldCondition('x').equals(null))
//->SELECT id FROM account WHERE x = null
//Boolean
.fromx('account').wherex(new al.FieldCondition('x').equals(true))
//->SELECT id FROM account WHERE x = true
//String
.fromx('account').wherex(new al.FieldCondition('x').equals('acme'))
//->SELECT id FROM account WHERE x = 'acme'
//Integer
.fromx('account').wherex(new al.FieldCondition('x').equals(1))
//->SELECT id FROM account WHERE x = 1
//Long
.fromx('account').wherex(new al.FieldCondition('x').equals(1L))
//->SELECT id FROM account WHERE x = 1
//Double
.fromx('account').wherex(new al.FieldCondition('x').equals(1.1))
//->SELECT id FROM account WHERE x = 1.1
//Date
.fromx('account').wherex(new al.FieldCondition('x').equals(Date.newinstance(2010,1,1)))
//->SELECT id FROM account WHERE x = 2010-01-01
//Datetime
.fromx('account').wherex(new al.FieldCondition('x').equals(Datetime.newinstance(2010,1,1,1,1,1)))
//->SELECT id FROM account WHERE x = 2010-01-01T01:01:01Z

Date Formulas

//=========================
// Hard-coded day methods
//=========================
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().todayx()))
//->SELECT id FROM account WHERE CreatedDate = TODAY
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().yesterdayx()))
//->SELECT id FROM account WHERE CreatedDate = YESTERDAY
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().tomorrowx()))
//->SELECT id FROM account WHERE CreatedDate = TOMORROW
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last90Days()))
//->SELECT id FROM account WHERE CreatedDate = LAST_90_DAYS
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().next90Days()))
//->SELECT id FROM account WHERE CreatedDate = NEXT_90_DAYS
//=========================
// By Units
//=========================
/*
+-----------------------+-----------------+
| UnitOfTime enum value | SOQL equivalent |
+-----------------------+-----------------+
| Day                   | DAY             |
| Week                  | WEEK            |
| Month                 | MONTH           |
| Quarter               | QUARTER         |
| Year                  | FISCAL_QUARTER  |
| FiscalQuarter         | YEAR            |
| FiscalYear            | FISCAL_YEAR     |
+-----------------------+-----------------+
*/
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Week)))
//->SELECT id FROM account WHERE CreatedDate = LAST_WEEK
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Month)))
//->SELECT id FROM account WHERE CreatedDate = LAST_MONTH
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Quarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_QUARTER
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Year)))
//->SELECT id FROM account WHERE CreatedDate = LAST_YEAR
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.FiscalQuarter)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_QUARTER
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.FiscalYear)))
//->SELECT id FROM account WHERE CreatedDate = LAST_FISCAL_YEAR
//=========================
// By Interval
//=========================
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().next(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:1
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().next(7,al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = NEXT_N_DAYS:7
.fromx('account').wherex(new al.FieldCondition('CreatedDate',new al.DateFormula().last(7,al.UnitOfTime.Day)))
//->SELECT id FROM account WHERE CreatedDate = LAST_N_DAYS:7

AND, OR & NOT

//simple AND condition
.fromx('account')
.wherex(
  new al.AndCondition()
   .add(new al.FieldCondition('name','acme'))
   .add(new al.FieldCondition('ispartner',true))
  )
//->SELECT id FROM account WHERE (name = 'acme' AND ispartner = true)
//simple OR condition
.fromx('account')
.wherex(
  new al.OrCondition()
   .add(new al.FieldCondition('name','acme'))
   .add(new al.FieldCondition('ispartner',true))
  )
//->SELECT id FROM account WHERE (name = 'acme' OR ispartner = true)
//simple NOT condition
.fromx('account')
.wherex(
  new al.NotCondition(new al.AndCondition()
   .add(new al.FieldCondition('name','acme'))
   .add(new al.FieldCondition('ispartner',true)))
  )
//->SELECT id FROM account WHERE NOT((name = 'acme' AND ispartner = true))
//nested ANDs and ORs
.fromx('account')
.wherex(
    new al.NotCondition(
        new al.AndCondition()
        .add(
            new al.OrCondition()
            .add(new al.FieldCondition('name','acme'))
            .add(
                new al.AndCondition()
                .add(new al.FieldCondition('ispartner',true))
                .add(new al.FieldCondition('NumberOfEmployees').lessThan(10))
            )
        )
        .add(
            new al.OrCondition()
            .add(new al.FieldCondition('createddate').lessThan(new al.DateFormula().yesterdayx()))
            .add(new al.FieldCondition('Rating','Hot'))
        )
    )
)
//->SELECT id FROM account WHERE NOT(((name = 'acme' OR (ispartner = true AND NumberOfEmployees < 10)) AND (createddate < YESTERDAY OR Rating = 'Hot')))



ORDER BY



Single Order By

/*
You can create an OrderBy using the following formats:
new al.OrderBy(fieldName).[ascending|descending|nullsFirst|nullsLast]*()
*/
.fromx('account').orderByx(new al.OrderBy('name'))
//->SELECT id FROM account ORDER BY name
.fromx('account').orderByx(new al.OrderBy('name').ascending().nullsFirst())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').ascending().nullsLast())
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').descending().nullsFirst())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST
.fromx('account').orderByx(new al.OrderBy('name').descending().nullsLast())
//->SELECT id FROM account ORDER BY name DESC NULLS FIRST

Multiple Order By

.fromx('account').orderByx(new List<al.OrderBy>{
  new al.OrderBy('name').ascending().nullsFirst()
  ,new al.OrderBy('rating').descending().nullsLast()
})
//->SELECT id FROM account ORDER BY name ASC NULLS FIRST, rating DESC NULLS LAST



LIMIT



.fromx('account').limitx(50)
//->SELECT id FROM account LIMIT 50



SOQL Options for toSoql() method



Wildcards

Enabled by default: No

//default
.fromx('account')
.wherex(
  new al.OrCondition()
  .add(new al.FieldCondition('name').likex('acme'))
  .add(new al.FieldCondition('name').likex('test'))
).toSoql()
//->SELECT id FROM account WHERE (name like 'acme' OR name like 'test')
.fromx('account')
.wherex(
  new al.OrCondition()
  .add(new al.FieldCondition('name').likex('acme'))
  .add(new al.FieldCondition('name').likex('test'))
).toSoql(new al.SoqlOptions().wildcardStringsInLikeOperators())
//->SELECT id FROM account WHERE (name like '%acme%' OR name like '%test%')
.fromx('account')
.wherex(
  new al.OrCondition()
  .add(new al.FieldCondition('name').likex('acme'))
  .add(new al.FieldCondition('name').likex('test'))
).toSoql(new al.SoqlOptions().doNotWildcardStringsInLikeOperators())
//->SELECT id FROM account WHERE (name like 'acme' OR name like 'test')

String Escaping

Enabled by default: Yes

//GOOD (default)
.fromx('account')
.wherex(new al.FieldCondition('name').likex('O\'Neal'))
.toSoql()
//->SELECT id FROM account WHERE name like 'O\'Neal'
//BAD!  The generated query below is invalid and will throw an error.
//Why even allow it as an option?  Because you never know when it might be needed - invalid or not.
.fromx('account')
.wherex(new al.FieldCondition('name').likex('O\'Neal'))
.toSoql(new al.SoqlOptions().doNotEscapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O'Neal'
.fromx('account')
.wherex(new al.FieldCondition('name').likex('O\'Neal'))
.toSoql(new al.SoqlOptions().escapeSingleQuotes())
//->SELECT id FROM account WHERE name like 'O\'Neal'