Using Dynamic SOQL

We’ve all had situations where you needed to dynamically build a query.  How do we do this in Apex? Queries can be constructed in several ways, but for this article we’ll focus just on the method of building a string and executing it with the Database engine.

A common method of executing a query:

// Retrieve a list of Contacts
List listOfContacts = [SELECT Id, Name FROM Contacts LIMIT 100];

OR you can use the Database Engine:

// Retrieve a list of Contacts
List listOfContacts = Database.Query('SELECT Id, Name FROM Contacts LIMIT 100');

So how do we create a query that always returns fields we need even when they are added later? Apex can access the Schema object to get the list of fields in any Object. To retrieve the fields in the Contact Object for example:

// Get a map of all the fields
Map<String, Schema.SObjectField> fieldList = Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

So now we have the list of fields. All we need to do is construct a Query for the fields in any Object (similar to the SELECT * method of SQL).

String query = 'SELECT ';

Map<String, Schema.SObjectField> fieldList = Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

for(String fld : fieldList.keySet())
{
    query += fld + ',';
}

// Strip the ending comma
query = query.subString(0, query.Length()-1);

// Add the FROM portion
query += ' FROM Contact LIMIT 100';

So if we construct a QueryBuilder class we can make any Object, Limit or even a WHERE filter parameters or settings within the class.

public class QueryBuilder
{
   public String ObjectName {set;get;}
   public Integer LimitSize {set;get;}
   
   public String BuildQuery()
   {
       String query = 'SELECT ';

       Map<String, Schema.SObjectField> fieldList =    Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap(); 

       for(String fld : fieldList.keySet())
       {
          query += fld + ',';
       }

       // Strip the ending comma
       query = query.subString(0, query.Length()-1);

       // Append Object
       query += ' FROM ' + ObjectName;
       query += ' LIMIT ' + String.valueOf(LimitSize);

       return(query);
   }
}

Just like that you have a query engine that will always return the full field list of any object you request. You can also modify the QueryBuilder to accept lists of fields, filters such as “WHERE” and you can even create a method using queryMore to return blocks of records beyond the 1000 row limit.

[contact-form]