Enhancing Dynamic SOQL Queries in Salesforce Apex through Database.queryWithBinds() – Salesforce Apex Spring ’23 Release

/ apex, Salesforce / By
Opening Statement:

In this post, we’ll look at an example of using Database.queryWithBinds in apex and the advantages of it on the previous Database.query approach.

If you’re working with Salesforce’s Apex programming language, you may need to write a query to search for specific records in the Salesforce database. In some cases, you may not know the specific values to search for at the time you write the query. In those cases, you can build a dynamic SOQL query that uses variables to specify the search criteria.

Example:

Let’s consider the following example: we want to find all Opportunity records associated with Accounts that have an industry of ‘Banking’, annual revenue of less than $1,000,000, and a name that starts with ‘Test’.

Previously, before spring 23 release, we used to write the dynamic query code like below

example.

String industry = 'Banking';
Integer AnnualRevenue = 1000000;
String name = 'Test%';
String query = 'SELECT Id, Name,Account.Name FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE Industry =\''+industry+'\'';
query+= ' AND AnnualRevenue <'+AnnualRevenue;
query+= ' AND Name Like :name';
query+= ' )';
List<Opportunity> opps = Database.query(query);

In above example , we first defined three variables to specify the search criteria:

  • industry: a string that specifies the industry of the associated Account
  • AnnualRevenue: an integer that specifies the maximum annual revenue of the associated Account
  • name: a string that specifies the name of the associated Account, using the % wildcard character to match any characters that come after the specified string
  • We then build a dynamic SOQL query by concatenating strings that specify the search criteria. The query is constructed using the + operator to concatenate strings, and includes bind variables for the industry and name variables to ensure that the query is executed safely and securely.
Utilizing Database.queryWithBinds

The use of Database.queryWithBinds() resolves the bind variables in a query directly from a Map parameter, using a key instead of Apex code variables. Therefore, the variables do not need to be in scope when executing the query. The following code shows an updated example based on this approach.

Map<String, Object> acctBinds = new Map<String, Object>{
    'industry' => 'Banking',
    'revenue' => 1000000,
    'name' => 'Test%'
};
List<Opportunity> opps = Database.queryWithBinds(
    'SELECT Id, Name,Account.Name FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = :industry AND AnnualRevenue < :revenue AND Name Like : name)',
    acctBinds,
    AccessLevel.USER_MODE
);


In the provided code snippet, we have established a Map named “acctBinds,” encompassing named bind variables for the search criteria:

  • “industry”: A named bind variable indicating the industry of the associated Account.
  • “revenue”: A named bind variable specifying the maximum annual revenue of the associated Account.
  • “name”: A named bind variable detailing the name of the associated Account, employing the % wildcard character to match any characters following the specified string.

The execution of the dynamic SOQL query is achieved using the Database.queryWithBinds() method, requiring three parameters:

  • The initial parameter is the SOQL query string, utilizing named bind variables to define the search criteria.
  • The second parameter is a map containing named bind variables and their corresponding values. Here, we provide the previously defined “acctBinds” map.
  • The third parameter is an optional parameter determining the query’s access level. We employ the AccessLevel.USER_MODE constant to ensure execution with the same permissions as the current user.

Following the query execution, the results are accessible through the “opps” variable, which holds a list of Opportunity records matching the specified search criteria.

Related Posts