Dynamic Database Queries
In Chapter 4, you learned about bind variables. They are variables whose values are injected into SOQL and SOSL statements in predefined locations, notated with colons. But bind variables are not powerful enough to support an entirely dynamic WHERE clause, one that includes conditional filters added and subtracted based on the behavior of the program. You could write every combination of WHERE clause and use long strings of conditional statements to pick the right one. An alternative is a completely dynamic query, executed using the Database.query method.
Listing 5.28 provides an example of two dynamic queries. The first is on the Contact object. The results of the query are returned in a list of Contact records. Other than the dynamic query itself, this code should be familiar. The second query selects Project records but treats them as a list of SObject objects.
Listing 5.28 Dynamic SOQL Queries
List<Contact> resources = Database.query(
'SELECT Id, Name FROM Contact'),
for (Contact resource : resources) {
System.debug(resource.Id + ' ' + resource.Name);
}
List<SObject> projects = Database.query('SELECT Id, Name FROM Project__c'),
for (SObject project : projects) {
System.debug(project.get('Id') + ' ' + project.get('Name'));
}
The SObject is a typeless database object. It allows you to interact with database records without declaring them as a specific type. The get method of the SObject allows the retrieval of a field value by name. The getSObject method returns the value of a related object. These values also have setter methods: set and setSObject. Used in conjunction with DML statements or database DML methods, you can write generic code that operates on a series of database objects. This is particularly useful when you have several objects with the same field names because it can reduce the amount of code.
Tip
Use the escapeSingleQuotes of the String object to prevent SOQL injection attacks. This method adds escape characters () to all single quotation marks in a string.
SOSL queries can also be constructed and executed dynamically. The Search.query method returns a list of lists containing SObjects. Listing 5.29 provides an example of its use.
Listing 5.29 Dynamic SOSL Query
List<List<SObject>> result = Search.query(
'FIND 'Chicago' '
+ 'RETURNING Contact(Name), Project__c(Name)'),
for (List<SObject> records : result) {
for (SObject record : records) {
System.debug(record.get('Name'));
}
}
The SOSL query returns the names of Project and Contact records containing the word Chicago. The outer loop is executed for each type of object specified in the RETURNING clause. The inner loop runs over the matching records of that object type. For example, the first iteration of the loop assigns records to a list of Contact records that matched the search term. The second iteration assigns it to the matching Project records.
Note
Dynamic queries have all the same governor limits as their static counterparts.