Introduction:

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

When you work with Salesforce’s Apex programming language, you will need to write a query. This query is used to search for specific records in the Salesforce database. In some cases, you don’t 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. Suppose we want to find all Opportunity records, that are associated with Accounts having an industry of ‘Banking’. The opportunities must have an annual revenue of less than $1,000,000. Additionally, the name must start with ‘Test’.

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+'\''+
    ' AND AnnualRevenue <'+AnnualRevenue+
    ' AND Name Like :name'+
    ' )';
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. It includes bind variables for the industry and name variables. This ensures that the query is executed safely and securely.

Using Database.queryWithBinds

The use of Database.queryWithBinds() resolves the bind variables in a query directly from a Map parameter. It uses 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
);

Another Example Using with Sets in Database.queryWithBinds

Suppose we had a requirement where we need to get all contacts where Account Annual Revenue is not null. The Account Rating should be either Hot or Warm. Additionally, the Contact email must not be null. Let us see how we achieve this requirement using Database.queryWithBinds() in below code –

Map<Id, Account> accountsByIds = new Map<Id, Account>([SELECT Id,Name 
													    FROM Account 
													    WHERE AnnualRevenue !=null]);
Set<String> accNames = new Set<String>();
for(Account objAcc : accountsByIds.values()){
    accNames.add(objAcc.Name);
}
Map<Id,Contact> contactsByIds = new Map<Id, Contact>([SELECT Id 
													  FROM Contact 
													  WHERE email !=null]);
Map<String, Object> bindVars = new Map<String, Object>{'conIds'=>contactsByIds.keyset(),
												       'accountNames'=>accNames};
bindVars.put('accountRatings', new Set<String>{'Hot','Warm'});
String query = 'SELECT Id, Name, Account.Name FROM Contact '+
    		   'WHERE Account.Name = :accountNames '+
    		   'AND Account.Rating =: accountRatings '+
    		   'AND Id =: conIds ';
List<Contact> cons = Database.queryWithBinds(query,  
                                             bindVars,
                                             AccessLevel.USER_MODE);
System.debug('cons: ' + cons.size());
System.debug('cons: ' + cons);

In the above code, we defined and set the variables :

• accountsByIds: A map that will store Account records queried from the database, keyed by their Id. It is populated by querying all accounts where AnnualRevenue is not null.
• accNames: A set that will store unique Account names extracted from the queried Account records.
• contactsByIds: A map that will store Contact records queried from the database, keyed by their Id. It is populated by querying all contacts where the email field is not null.

We then defined the bindVars map. containing bind variables for the search criteria. It includes: –

• conIds: A set of Contact Ids extracted from the queried Contact records.
• accountNames: A set of Account names extracted from the queried Account records.
• accountRatings: A set containing the ratings ‘Hot’ and ‘Warm’.

When the Database.queryWithBinds method is called, it substitutes –

• conIds by contactsByIds.keyset, which contains all matching contact ids where email is not null.
• accountNames by accNames which contains all the accounts where annual evenue is not null.
• accountRatings by set of rating which need to be matched with contacts account rating.

The constructed query and bind variables are passed to Database.queryWithBinds() method to retrieve Contact records matching the specified criteria.

Some key points to remember before usage –

Although map keys of type String are case-sensitive,the queryWithBinds method doesn’t support Map keys that differ only in case. In a queryWithBinds method, comparison of Map keys is case-insensitive. If duplicate Map keys exist, the method throws a runtime QueryException.

Below example throws runtime exception: System.QueryException: The bindMap consists of duplicate case-insensitive keys: [Acctname, acctName].

Map<String, Object> bindVars = new Map<String, Object>{'acctName' => 'Acme Corporation'};
bindVars.put('Acctname', 'Foo');
string query = 'Select Id from Contact where Name like :acctName';
 
List<Contact> contacts =  Database.queryWithBinds(query, bindVars, AccessLevel.USER_MODE);

Benefits of Database.queryWithBinds over Database.query –

Here are the key benefits of using Database.queryWithBinds over traditional Database.query in Apex –

1. True Parameter Binding via Map

Instead of relying on in-scope variables, you pass a Map<String, Object> to define all bind values upfront.

• This ensures your values are resolved directly from the map at runtime.
• Variables don’t need to be in scope when executing the query.

2. Stronger Security — Prevent SOQL Injection

By using bind variables, Salesforce automatically sanitizes inputs, treating them as data only.

• No chance for malicious strings to alter your SOQL logic.

3. Cleaner, More Reusable Code

• Eliminates error-prone string concatenation.
• Promote code reuse by centralizing query logic and binds.

4. Optimized Query Caching & Performance

Salesforce can more effectively cache and plan queries when bind values change, as the structure of the query remains static.

5. Support for Multiple “WithBinds” Variants

Beyond Database.queryWithBinds, you also get:

• Database.countQueryWithBinds
• Database.getQueryLocatorWithBinds – which follow the same pattern for count() operations and batch/query locators.

Points to note –

• Map keys must follow naming standards. They must start with an ASCII letter and can’t start with a number. They must not use reserved keywords. Additionally, they must adhere to variable naming requirements.
• Although currently supported, Salesforce recommends against using the dot notation with Map keys.

Reference link –

• Release Notes
• Salesforce Ideas

Thank you for reading.

For more helpful articles please visit – https://thesalesforcedev.in