A very common scenario is to search data based on user input. Therefore, understanding this kind of scenario is crucial.I have written this article to search opportunity records based on user input in visualforce page. I have explained the use of:
1. Database.query method
2. String method isNotBlank(input String)
3. Getter, setter method
4.Usage of constructor
Scenario: Fetch Opportunity name and stage name based on user input
Conditions
. If user provides both opportunity name and stage name then fetch opportunity records according to name and stage name given by user
2. If user provides only opportunity name then fetch opportunity records according to opportunity name given by user
3.If user provides only opportunity stage name then fetch opportunity records according to opportunity stage name given by user
Solution
Whenever we want to fetch records based on certain condition given by user then go ahead and use Dynamic SOQL. It refers to the creation of a SOQL string at run time with Apex code.
To create a dynamic SOQL query at run time, use the database query method
opp=Database.query(query);
Now we are clear that we will be using Database query method as we want to fetch records based on input provided by user. But how we should use it? Lets check that.
I have created a visualforce page(dynamicsoql.vfp) and apex controller (DynamicSoql.apxc) to perform the scenario.
I have created a button in visualforce page which calls apex method(dynamicSearch)
<apex:commandButton value="Dynamic Query" action="{!dynamicSearch}"/>
In apex controller create member variables oppName, oppStageName, opp. Use getter, settter method to get and set values
Getter Method: Access the data in visualforce page using the controller.
<apex:inputText value="{!oppName}"/>
<apex:inputText value="{!oppStageName}"/>
Setter Method: Allows to see set data in the controller
Public String oppName {set;get;}
Public String oppStageName {set;get;}
Public List<Opportunity> opp{set;get;}
Create constructor DynamicSoql. The constructor name is same as class name. I have written a query to fetch opportunity stage Name, name. This query should be executed when page is loaded. Therefore I have written it inside constructor.
public DynamicSoql(){
opp=[select name,stageName from Opportunity];
}
In the dynamicSearch method, I have stored the query in the string
String query ='select name,stageName from Opportunity';
Then I have used string method isNotBlank(inputString). This method is of boolean type and returns true if the specified String is not whitespace, not empty (”), and not null; otherwise, returns false. I have used this method to check if string(oppName) is not empty or null then only perform certain conditions.
string.isNotBlank(oppName)
Condition 1. If both opportunity name and stage name are not blank then query name, stage name where name and stage name contains value provided by user
if(string.isNotBlank(oppName)&& string.isNotBlank(oppStageName))
{
query= query+' where name LIKE \'%'+oppName+'%\' and StageName LIKE \'%'+oppStageName+'%\'';
}
The % wildcard matches zero or more characters.
The LIKE operator performs a case-insensitive match and is supported for string fields only.
The % wildcard matches zero or more characters.
Condition 2: If opportunity name is not blank then query name, stage name where name contains value provided by user
if(string.isNotBlank(oppName))
{
query= query+' where name LIKE \'%'+ oppName +'%\'';
}
Condition 3: If opportunity stagename is not blank then query name, stage name where stage name contains value provided by user
if(string.isNotBlank(oppStageName))
{
query=query+' where StageName LIKE \'%'+oppStageName+'%\'';
}
Finally store the query in Database Query Method
opp=Database.query(query);
Final Code
dynamicsoql.vfp: Visualforce Page
<apex:page controller="DynamicSoql" >
<apex:form >
<apex:pageBlock title="Dynamic Soql">
<apex:pageBlockButtons location="top">
<apex:commandButton value="Dynamic Query" action="{!dynamicSearch}"/>
</apex:pageBlockButtons>
<apex:pageBlockSection >
<apex:pageBlockSectionItem >
<apex:outputLabel value="Enter Opportunity Name"/>
<apex:inputText value="{!oppName}"/>
</apex:pageBlockSectionItem>
<apex:pageBlockSectionItem >
<apex:outputLabel value="Enter Stage Name"/>
<apex:inputText value="{!oppStageName}"/>
</apex:pageBlockSectionItem>
</apex:pageBlockSection>
</apex:pageBlock>
<apex:pageBlock title="Result">
<apex:pageBlockTable value="{!opp}" var="o">
<apex:column value="{!o.name}"/>
<apex:column value="{!o.stagename}"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
DynamicSoql.apxc: Apex controller
public class DynamicSoql {
Public String oppName {set;get;}
Public String oppStageName {set;get;}
Public List<Opportunity> opp{set;get;}
public DynamicSoql(){
opp=[select name,stageName from Opportunity];
}
public void dynamicSearch(){
String query ='select name,stageName from Opportunity';
if(string.isNotBlank(oppName)&& string.isNotBlank(oppStageName))
{
query= query+' where name LIKE \'%'+oppName+'%\' and StageName LIKE \'%'+oppStageName+'%\'';
}
else{
if(string.isNotBlank(oppName))
{
query= query+' where name LIKE \'%'+ oppName +'%\'';
}
else
{
if(string.isNotBlank(oppStageName))
{
query=query+' where StageName LIKE \'%'+oppStageName+'%\'';
}
}
}
opp=Database.query(query);
}
}
Understand search functionality clearly by trying different scenarios.
I hope you like this article. For more of these kind of articles stay tuned.
Happy Coding!
References:
