Execute a dynamically created query in APEX

Any Salesforce developer must be familiar with SOQL queries. SOQL as we all know is the Salesforce’s version of SQL. It’s full form translates to Salesforce Object Query Language. We have an idea about directly using query in APEX by writing the query in between ‘[‘ and ‘]’. However there is a limitation to the method i.e. queries created are a lot restricted and only available method to use variables is using them in where clause. We cannot use columns as variables, neither the conditions to be set or anything else. For this purpose we require a query which we can be generated as a string and then use it. This is what I am going to demonstrate to you how to execute a dynamic query in APEX.

Sample code (APEX)

public class dynamicqblog {
    list<sobject> records;
    public string sobj { get; set;}
    public string cols;
    public string condition;
    
    public dynamicqblog(){
        string query = '';
        sobj = 'account';
        system.debug(query);
    }
    
    public list<selectoption> getSobjlist(){
        list<selectoption> opt = new list<selectoption>();
        opt.add(new selectoption('account','Account'));
        opt.add(new selectoption('contact','Contact'));
        return opt;
    }
    
    public void dummy(){
        
    }
    
    public list<sobject> getRecords(){
        cols = 'id, name';
        condition = 'fax = null';
        string query = 'select '+cols+' from '+sobj+' where '+condition;
        records = Database.query(query);
        return records;
    }
}

In this class we have done everything like any normal APEX class, only difference is the getRecords() function which has Database.query() function. This function executes a string query, at the cost of total number of rows we can fetch in one execution of the class. The limit is 10,000 as compared to the 50,000 limit of the normal static query.

Sample code (Visualforce)

<apex:page controller="dynamicqblog">
<apex:form>
<apex:selectList value="{!sobj}" size = "1">
<apex:selectOptions value="{!Sobjlist}"/>
</apex:selectList>
<apex:commandButton action="{!dummy}" value="showlist"/>
<apex:dataTable value="{!Records}" var="rec">
<apex:column value="{!rec.id}"/>
</apex:dataTable>
</apex:form>
</apex:page>

In this example as you can see that we have not used the column name, however we have fetched it. That is because, for sObject the column name and value are created as a map at runtime, and hence we have to create a wrapper class to use that data. Rest everything is same.