SOQL, SOSL and DML in Apex

What it is

Apex reads data with two query languages and writes it with DML.

• SOQL (Salesforce Object Query Language) reads records from one object and its related objects, like SQL SELECT.
• SOSL (Salesforce Object Search Language) runs a text search across several objects at once.
• DML (Data Manipulation Language) inserts, updates, upserts, deletes, undeletes and merges records.

How it works

SOQL in Apex

String stage = 'Closed Won';
List<Opportunity> opps = [
    SELECT Id, Name, Amount, Account.Name
    FROM Opportunity
    WHERE StageName = :stage AND CloseDate = LAST_N_DAYS:30
    ORDER BY Amount DESC
    LIMIT 50
];

• :stage is a bind variable; it is safe from SOQL injection.
• Assigning a query to a single sObject (Account a = [SELECT ...];) throws a QueryException if zero or more than one row returns.
• Date literals include TODAY, YESTERDAY, THIS_MONTH, LAST_N_DAYS:n (includes today) and NEXT_N_DAYS:n.
• IN :idSet filters by a collection.

Aggregate queries

List<AggregateResult> rows = [
    SELECT AccountId, COUNT(Id) total, SUM(Amount) amt
    FROM Opportunity
    GROUP BY AccountId
    HAVING COUNT(Id) > 5
];
for (AggregateResult r : rows) {
    Id accId = (Id) r.get('AccountId');
    Integer total = (Integer) r.get('total');
}

WHERE filters rows before grouping; HAVING filters groups by an aggregate. OFFSET can skip at most 2,000 rows.

SOSL

List<List<SObject>> found = [
    FIND 'Acme*' IN NAME FIELDS
    RETURNING Account(Id, Name), Contact(Id, LastName)
];
List<Account> accs = (List<Account>) found[0];

A transaction can run 20 SOSL searches, and each search returns at most 2,000 records.

Dynamic queries

Database.query(queryString) runs a SOQL string built at run time. Bind variables still work inside the string (WHERE Name = :term), and are the safe way to include user input. Database.queryWithBinds() takes a map of bind values and an access level.

DML statements and Database methods

Database.SaveResult[] results = Database.insert(contacts, false);
for (Integer i = 0; i < results.size(); i++) {
    if (!results[i].isSuccess()) {
        System.debug(contacts[i].LastName + ': ' + results[i].getErrors()[0].getMessage());
    }
}

merge combines up to three records of the same type (Account, Contact, Lead and some others). Inserting a setup object such as User and a non-setup object such as Account in the same transaction causes a mixed DML error; move one to @future or use System.runAs in tests.

A worked example

A nightly import inserts 200 Contacts, and three fail a validation rule. With insert contacts; all 200 fail and a DmlException is thrown. With Database.insert(contacts, false), 197 save and the three failures appear in the SaveResult array, where the code can log them. If the business rule says "save what is valid and report the rest", the Database method with allOrNone = false is the answer.

How the exam asks about this

• "Search for a term across Accounts, Contacts and Leads." SOSL.
• "Save valid records and report failures." Database.insert(records, false).
• "Filter by a value the user typed." A bind variable, never string concatenation.