Dynamic SOQL means creation of SOQL string at runtime with Apex code. It is basically used to create more flexible queries based on user’s input.
Use Database.query() to create dynamic SOQL.
Public static void main(String str)
{
String s1 = ‘select name from’+str;
List<sObject> sLst = Database.query(s1);
for(sObject s: sList)
{
System.debug(s);
}
}
Note: If the query string is wrong, this method returns the QueryException.
Public static void main(String table, String field1)
{
String s1 = ‘SELECT name,’;
S1 = s1+field;
S1 = s1+’from’;
S1 = s1+table;
String str = ‘where name like \’Acme\’’;
S1 = s1+str;
}
Note: We can use bind variables (:i) in dynamic SOQL but we can’t use bind variable field in a dynamic SOQL. ‘:500’
Public static void main()
{
String s = ‘Test%’;
Database.query(‘SELECT Name FROM account where name like :s’);
}
Note: The above query will work in dynamic as well as static SOQL.
Public static void main()
{
Account a = new Account(name= ‘Test’, phone=’12345’);
Database.query(‘SELECT Name FROM Account where Phone= :a.phone’);
}
Note: The above query will not work in dynamic SOQL and will result in a ‘variable does not exists’ error in it. But it will work perfectly in static SOQL.
There is a hack to use bind variable fields in SOQL.
Public static void main()
{
Account a = new Account(name=’abcd’, phone=’12345’);
String str = a.phone;
String s = ‘SELECT Name FROM Account WHERE phone=:str’;
}
Note: Use string escape singleQuotes(String str) on the string used for creating the query on dynamic SOQL, just to prevent SOQL injection.
String finalString = String.escapeSingleQuotes(‘SELECT Name FROM Account WHERE phone=:str’);
Database.query(finalString);
This method replaces all single quotes(‘) by (\’) which ensures that all single quotation marks are treated as enclosing strings instead of database commands.
String fieldString = ‘Name’;
String sObjectString = ‘Position__c’;
List<Position__c> positionList = Database.query(‘SELECT ‘ + fieldString + ‘FROM ’ + sObjectString);
