SF Fact #02 | Database.querywithBinds() usage

 

Dynamic SOQL refers to constructing a SOQL query string at runtime using Apex code. This approach allows for more flexible applications. For example, you can create a search based on user input or update records with different field names.

Creating Dynamic SOQL Queries To create a dynamic SOQL query at runtime, use the Database.query or Database.queryWithBinds methods in the following ways:

Single Record Query:

sObject s = Database.query(string);

Multiple Records Query:

List<sObject> sobjList = Database.query(string);

Query with Bind Variables:

List<sObject> sobjList = Database.queryWithBinds(string, bindVariablesMap, accessLevel);

These methods can be used wherever inline SOQL queries are used, such as in assignment statements and for loops, and are processed similarly to static SOQL queries.

API Version 55.0 and Later: User Mode for Database Operations With API version 55.0 and later, the accessLevel parameter allows running the query in user or system mode:

System Mode (Default): Ignores object and field-level permissions of the current user, with record sharing rules controlled by class sharing keywords.

User Mode: Enforces object permissions, field-level security, and sharing rules of the current user.

Type Safety in Dynamic SOQL Dynamic SOQL results can be specified as specific sObjects (e.g., Account or MyCustomObject__c) or the generic sObject type. At runtime, Salesforce validates the query result type against the declared variable type, throwing an error if they don't match. This ensures you don't need to cast from a generic sObject to a specific type.

Dynamic SOQL Considerations

Using Bind Variables:

String myTestString = 'TestName'; List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE Name = :myTestString');

Unlike inline SOQL, you can't use bind variable fields directly in Database.query:

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField'); List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c'); // Not supported

API Version 57.0 and Later: Use Database.queryWithBinds to pass bind variables directly from a map:

Map<String, Object> acctBinds = new Map<String, Object>{'acctName' => 'Acme Corporation'}; List<Account> accts = Database.queryWithBinds('SELECT Id FROM Account WHERE Name = :acctName', acctBinds, AccessLevel.USER_MODE);

Map Parameter Considerations:

• Map keys are case-sensitive but queryWithBinds treats them case-insensitively, throwing an error if duplicates exist.
• Map keys must adhere to naming standards and avoid reserved keywords.
• Avoid using dot notation with map keys.

Preventing SOQL Injection To prevent SOQL injection, use the escapeSingleQuotes method to sanitize user input:

String safeInput = String.escapeSingleQuotes(userInput);

Additional Dynamic SOQL Methods

• Database.countQuery and Database.countQueryWithBinds: Return the number of records a dynamic SOQL query would return.
• Database.getQueryLocator and Database.getQueryLocatorWithBinds: Create a QueryLocator object for batch Apex or Visualforce.