Writing Business Logic with the Apex Programming Language for the Force.com Platform

This chapter is from the book 

Database Integration in Apex

In Apex, the Force.com database is already integrated into the language and runtime environment. There are no object-relational mapping tools or database connection pools to configure. Your Apex code is automatically aware of your database, including all of its objects and fields and the security rules protecting them.

This section examines the five ways the database is exposed in Apex code, which are summarized here:

1. Database records as objects—Database objects are directly represented in Apex as classes. These classes are implicitly imported into your code, so you’re always developing from the latest database schema.
2. Database queries—SOQL is a concise expression of the records to be queried and returned to your programs.
3. Persisting database records—Apex has a built-in Data Manipulation Language (DML), providing verbs that create, update, or delete one or more records in the database.
4. Database triggers—Triggers are code that register interest in a specific action or actions on a database object, such as an insert or delete on the Account object. When this action occurs, the trigger code is executed and can inhibit or enhance the behavior of the database action.
5. Database security in Apex—Normally, Apex code runs in a privileged mode, granting it full access to all the data in the system. Alternatively, you can configure it to run under the same restrictions imposed on the current user, including object and record-level sharing rules.

Database Records as Objects

All database objects, standard and custom, are available as first-class members of the Apex language, automatically and transparently. This eliminates the mind-numbing, error-prone work of importing, mapping, and translating between relational and program data structures, chores commonly required in general-purpose programming languages. In Apex, references to database objects are verified at compile time. This reduces the possibility of runtime surprises caused by field or object mismatches. Listing 4.23 shows an example of creating a record in the Contact object and setting its first name field.

Listing 4.23 Creating a Record

Contact contact = new Contact();
contact.FirstName = 'Larry';

Database relationships are also exposed in Apex. The __r syntax refers to a relationship field, a field that contains a reference to another object or list of objects. Listing 4.24 builds on the previous listing, creating an Assignment record and associating it with the Contact record.

Listing 4.24 Creating a Record with Relationship

Assignment__c assignment = new Assignment__c();
assignment.Contact__r = contact;

The Force.com IDE’s Schema Explorer can take the mystery out of relationship fields like Contact__r. It displays the correct syntax for referring to fields and relationships, based on the actual schema of the database object. Its Schema list on the right side displays all objects, custom and standard. Drilling into an object, the Fields folder lists all fields in the object and their types. A reference type indicates that a field is the child object in a Lookup relationship. Expand these fields to reveal their parent object’s type and name. For example, in the Project custom object, Account__r is the foreign key to the Account object. This is demonstrated in Figure 4.4.

Data integrity is protected in Apex at compile and runtime using object metadata. For example, Name is defined as a read-only field in Contact, so the code in Listing 4.25 cannot be compiled.

Listing 4.25 Attempted Assignment to Read-Only Field

Contact c = new Contact();
c.Name = 'Larry';

After a database object is referenced in Apex code, that object cannot be deleted or edited in a way that invalidates the code. This protects your code from changes to the database schema. Impacted code must be commented out before the database objects are modified.

Database Queries

You’ve seen how data structures in Apex are implicitly defined by the objects in your database. Force.com provides two query languages to populate these objects with data: Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL). SOSL, addressed in Chapter 5, “Advanced Business Logic,” provides unstructured, full-text search across many objects from a single query.

The focus of this section is SOQL because it is the workhorse of typical business applications. This section includes subsections on the basics of SOQL, filtering and sorting, how to query related objects, and how to use SOQL from Apex code.

As you read this section, you can experiment with the sample SOQL queries using the Force.com IDE’s Schema Explorer. In the Navigator or Package Explorer View, expand the node for your Force.com Project and double-click salesforce.schema. Enter a query in the text box in the upper-left corner and click the Run Me button. The results appear in the table below the query. In Figure 4.5, a query has been executed against the Project object, returning four records. Note that many of the queries rely on objects from the Services Manager sample application rather than standard Force.com objects.

SOQL Basics

Despite being one letter away from SQL and borrowing some of its syntax, SOQL is completely different and much easier to understand on its own terms. Just as Apex is not a general-purpose programming language like Java, SOQL is not a general-purpose database query language like SQL. SOQL is specifically designed and optimized for the Force.com database.

A SOQL statement is centered on a single database object, specifying one or more fields to retrieve from it. The fields to select are separated by commas. Listing 4.26 is a simple SOQL statement that returns a list of Account records with Id and Name fields populated. SOQL is not case sensitive. SOQL keywords are shown throughout the book in uppercase and metadata objects in title case for readability only.

Listing 4.26 Simple SOQL Statement

SELECT Id, Name
  FROM Account

Filtering Records

SOQL supports filter conditions to reduce the number of records returned. A filter condition consists of a field name to filter, an operator, and a literal value.

Valid operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), != (not equal to), IN and NOT IN (matches a list of literal values, and supports semi-joins and anti-joins), and INCLUDES and EXCLUDES (match against multi-select picklist values). On String fields, the LIKE operator is also available, which applies a pattern to filter records. The pattern uses the % wildcard to match zero or more characters, _ to match one character, and the \ character to escape the % and _ wildcards, treating them as regular characters.

Multiple filters are combined in a single SOQL statement using the Boolean operators AND and OR and grouped with parentheses. Listing 4.27 returns the names of accounts with a type of direct customer, a modification date sometime during the current year, and more than $100 million in annual revenue.

Listing 4.27 SOQL Statement with Filter Conditions

SELECT Name
  FROM Account
  WHERE AnnualRevenue > 100000000
  AND Type = 'Customer - Direct'
  AND LastModifiedDate = THIS_YEAR

Notice the way literal values are specified. Single quotation marks must be used around String literals but never with other data types. THIS_YEAR is a built-in relative time function. The values of relative time functions vary based on when the query is executed. Other relative time functions are YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, and so forth.

Absolute dates and times can also be specified without single quotation marks. Dates must use the YYYY-MM-DD format. Datetimes can be YYYY-MM-DDThh:mm:ssZ, YYYY-MM-DDThh:mm:ss+hh:mm, or YYYY-MM-DDThh:mm:ss-hh:mm, indicating the positive or negative offset from Coordinated Universal Time (UTC).

In addition to filter conditions, SOQL supports the LIMIT keyword. It sets an absolute upper bound on the number of records that can be returned from the query. It can be used in conjunction with all the other SOQL features. For example, the SOQL statement in Listing 4.28 returns up to ten Account records modified today.

Listing 4.28 SOQL Statement with Record Limit

SELECT Name, Type
  FROM Account
  WHERE LastModifiedDate = TODAY
  LIMIT 10

Sorting Query Results

Results of a query can be sorted by up to 32 fields in ascending (ASC, the default) or descending (DESC) order. Sorting is not case sensitive, and nulls appear first unless otherwise specified (NULLS LAST). Multi-select picklists, long text areas, and reference type fields cannot be used as sort fields. The SOQL query in Listing 4.29 returns records first in ascending order by Type and then in descending order by LastModifiedDate.

Listing 4.29 SOQL Statement with Sort Fields

SELECT Name, Type, AnnualRevenue
  FROM Account
  ORDER BY Type, LastModifiedDate DESC

Querying Multiple Objects

The result of a SOQL query can be a simple list of records containing rows and columns or hierarchies of records containing data from multiple, related objects. Relationships between objects are navigated implicitly from the database structure. This eliminates the work of writing accurate, efficient join conditions common to development on traditional SQL databases.

The two ways to navigate object relationships in SOQL are child-to-parent and parent-to-child. Listing 4.30 is an example of a child-to-parent query, returning the name, city, and Force.com username creating its contact of all resources with a mailing address in the state of California. It selects and filters fields of the Project object, the parent object of Account. It also selects the Name field from the User object, a parent two levels removed from Project via the Account’s CreatedBy field.

Listing 4.30 SOQL with Child-to-Parent Relationship

SELECT Name, Account__r.Name, Account__r.CreatedBy.Name
  FROM Project__c
  WHERE Account__r.BillingState = 'CA'

At most, five levels of parent objects can be referenced in a single child-to-parent query, and the query cannot reference more than 25 relationships in total.

The second form of relationship query is the parent-to-child query. Listing 4.31 provides an example. The parent object is Resource, and the child is Timecard. The query selects from every Contact its Id, Name, and a list of hours from its Timecards in the current month.

Listing 4.31 SOQL with Parent-to-Child Relationship

SELECT Id, Name,
  (SELECT Total_Hours__c
    FROM Timecards__r
    WHERE Week_Ending__c = THIS_MONTH)
  FROM Contact

A parent-to-child query cannot reference more than 20 child objects. Double-clicking the parent record in the results table brings up the child records for viewing in the Force.com IDE.

Using SOQL in Apex

Like database objects, SOQL queries are an integrated part of the Apex language. They are developed in-line with your code and verified at compile time against your database schema.

Listing 4.32 is an example of a SOQL query used in Apex. It retrieves a list of Project records for this year and loops over them, summing their billable hours in the variable totalHours. Note the usage of the variable named statuses directly in the SOQL query, preceded by a colon. This is known as a bind variable. Bind variables can appear on the right side of a WHERE clause, as the value of an IN or NOT IN clause, and in the LIMIT clause.

Listing 4.32 SOQL Query in Apex

Decimal totalHours = 0;
List<String> statuses = new String[] { 'Green', 'Yellow' };
List<Project__c> projects = [ SELECT Billable_Hours__c
  FROM Project__c
  WHERE Start_Date__c = THIS_YEAR and Status__c IN :statuses ];
for (Project__c project : projects) {
  totalHours += project.Billable_Hours__c;
}
System.debug(totalHours);

This code relies on a List to store the results of the SOQL query. This means the entire SOQL query result must fit within the heap size available to the program. A better syntax for looping over SOQL records is a variation of the List/Set Iteration For Loop called a SOQL For Loop. The code in Listing 4.33 is a rewrite of Listing 4.32 using the SOQL For Loop. This allows it to run when the Project object contains up to 50,000 records for this year without consuming 50,000 records’ worth of heap space at one time.

Listing 4.33 SOQL Query in Apex Using SOQL For Loop

Decimal totalHours = 0;
for (Project__c project : [ SELECT Billable_Hours__c
  FROM Project__c
  WHERE Start_Date__c = THIS_YEAR ]) {
  totalHours += project.Billable_Hours__c;
}
System.debug(totalHours);

An additional form of the SOQL For Loop is designed for use with Data Manipulation Language (DML). Consider how the code in Listing 4.32 could be adapted to modify Project records returned from the SOQL query rather than simply summing them. With the existing code, one Project record would be modified for each loop iteration, an inefficient approach and a quick way to run afoul of the governor limits. But if you change the type of variable in the For Loop to a list of Project records, Force.com provides up to 200 records per loop iteration. This allows you to modify a whole list of records in a single operation.

Any valid SOQL statement can be executed in Apex code, including relationship queries. The result of a child-to-parent query is returned in a List of objects whose types match the child object. Where fields from a parent object are included in the query, they are available as nested variables in Apex code. For example, running the query in Listing 4.30 within a block of Apex code returns a List<Project__c>. If this List is assigned to a variable named projects, the first Account record’s billing state is accessible by projects[0].Account__r.BillingState.

Parent-to-child queries are returned in a List of objects, their type matching the parent object. Each record of the parent object includes a nested List of child objects. Using Listing 4.31 as an example, if results contains the List<Contact> returned by the query, results[0].Timecards__r[0].Total_Hours__c accesses a field in the first Contact’s first Timecard child record.

Persisting Database Records

Changes to database records in Force.com are saved using Data Manipulation Language (DML) operations. DML operations allow you to modify records one at a time, or more efficiently in batches of multiple records. The five major DML operation types are listed next. Each is discussed in more detail later in this subsection.

• Insert—Creates new records.
• Update—Updates the values in existing records, identified by Force.com unique identifier (Id) field or a custom field designated as an external identifier.
• Upsert—If records with the same unique identifier or external identifier exist, this updates their values. Otherwise, it inserts them.
• Delete—Moves records into the Recycle Bin.
• Undelete—Restores records from the Recycle Bin.

DML operations can be included in Apex code in one of two ways: DML statements and database methods. Beyond the syntax, they differ in how errors are handled. If any one record in a DML statement fails, all records fail and are rolled back. Database methods allow for partial success. This chapter uses DML statements exclusively. Chapter 5 provides information on database methods.

Insert

The Insert statement adds up to 200 records of a single object type to the database. When all records succeed, they contain their new unique identifiers. If any record fails, a DmlException is raised and the database is returned to its state prior to the Insert statement. For example, the code in Listing 4.34 inserts a Contact record and uses it as the parent of a new Resource record.

Listing 4.34 Inserting a Record

try {
  Contact c = new Contact(FirstName = 'Justin', LastName = 'Case',
    Hourly_Cost_Rate__c = 75, Region__c = 'West');
  insert c;
} catch (DmlException e) {
  System.debug(LoggingLevel.ERROR, e.getMessage());
}

Update

Update saves up to 200 existing records of a single object type. Existing records are identified by unique identifier (Id). Listing 4.35 illustrates the usage of the Update statement by creating a Resource record for Doug and updating it. Refresh the Resources tab in the native user interface to see the new record.

Listing 4.35 Updating Records

Contact doug = new Contact(FirstName = 'Doug', LastName = 'Hole');
insert doug;
doug.Hourly_Cost_Rate__c = 100;
doug.Home_Office__c = 'London';
update doug;

Upsert

Upsert combines the behavior of the Insert and Update operations on up to 200 records of the same object type. First, it attempts to locate a matching record using its unique identifier or external identifier. If one is found, the statement acts as an Update. If not, it behaves as an Insert.

The syntax of the Upsert statement is identical to Update and Insert, but adds a second, optional argument for specifying an external identifier. If an external identifier is not provided, the record’s unique identifier is used. The code in Listing 4.36 upserts a record in the Contact object using the field Resource_ID__c (created in Chapter 11, “Advanced Integration”) as an external identifier. If a Contact record with a Resource_ID__c value of 1001 exists, it is updated. If not, it is created.

Listing 4.36 Upserting a Record

Contact c = new Contact(Resource_ID__c = 1001,
  FirstName = 'Terry', LastName = 'Bull');
upsert c Resource_ID__c;

Delete and Undelete

Delete and Undelete statements move up to 200 records of the same object type to and from the Recycle Bin, respectively. Listing 4.37 shows an example of the Delete statement. A new Resource record named Terry is added and then deleted.

Listing 4.37 Deleting Records

Contact terry = new Contact(FirstName = 'Terry', LastName = 'Bull');
insert terry;
delete terry;

Listing 4.38 builds on Listing 4.37 to undelete the Terry record. Concatenate the listings in the Execute Anonymous view to test. The database is queried to prove the existence of the undeleted record. Try running the code a second time with the undelete statement commented out to see that it is working as intended.

Listing 4.38 Undeleting Records

undelete terry;
Contact terry2 = [ SELECT Id, Name
  FROM Contact WHERE Name LIKE 'Terry%' LIMIT 1 ];
System.debug(terry2.Name + ' exists');
delete terry;

Database Triggers

Triggers are Apex code working in concert with the Force.com database engine, automatically invoked by Force.com when database records are modified. Trigger code can perform any necessary processing on the modified data before or after Force.com completes its own work. The following list describes scenarios commonly implemented with triggers:

• A validation rule is required that is too complex to define on the database object using formula expressions.
• Two objects must be kept synchronized. When a record in one object is updated, a trigger updates the corresponding record in the other.
• Records of an object must be augmented with values from another object, a complex calculation, or external data via a Web service call.

This subsection covers the essentials of trigger development, including definition, batch processing, and error handling.

Definition

A trigger definition consists of four parts:

1. A unique trigger name to differentiate it from other triggers. Multiple triggers can be defined on the same database object.
2. The name of the database object on which to create the trigger. You can create triggers on standard and custom objects.
3. A comma-separated list of one or more trigger events that cause the trigger code to be executed. An event is specified using two keywords. The first keyword is either before or after, indicating that the trigger is to be executed before or after the database operation is saved. The second keyword is the DML operation: insert, update, delete, or undelete. For example, the trigger event before update means that the trigger is fired before a record is updated. Note that before undelete is an invalid trigger event.
4. The block of Apex code to execute when the trigger event occurs. The code typically loops over the list of records in the transaction and performs some action based on their contents. For insert and update triggers, the list of records in the transaction is provided in the variable Trigger.new. In a before trigger, these records can be modified. In update, delete, and undelete triggers, Trigger.old contains a read-only list of the original versions of the records. Also available to your trigger code is a set of Boolean variables indicating the event type that fired the trigger. They are useful when your trigger is defined on multiple events yet requires separate behavior for each. These variables are Trigger.isBefore, Trigger.isAfter, Trigger.isInsert, Trigger.isUpdate, Trigger.isDelete, and Trigger.isUndelete.

Listing 4.39 is an example of a trigger named validateTimecard. It is triggered before inserts and updates to the Timecard custom object. It doesn’t do anything yet because its code block is empty.

Listing 4.39 Trigger Definition

trigger validateTimecard on Timecard__c(before insert, before update) {
  // code block
}

Triggers cannot be created in the Execute Anonymous view. Create them in the Force.com IDE by selecting File, New, Apex Trigger. To test triggers, use the native user interface to manually modify a relevant record, or write a unit test and invoke it from the Apex Test Runner or Execute Anonymous view.

Batch Processing in Triggers

Manual testing in the native user interface and simplistic unit tests can lull you into the false belief that triggers operate on a single record at a time. Not to be confused with Batch Apex, triggers can always be invoked with a list of records and should be optimized accordingly. Many ways exist to get a batch of records into the Force.com database, including the Data Loader and custom user interfaces. The surest way to a production issue with governor limits is to write a trigger that operates inefficiently when given a batch of records. The process of hardening a trigger to accept a batch of records is commonly called bulkifying the trigger.

Batches can be up to 200 records. When writing your trigger code, look at the resources consumed as you loop over Trigger.new or Trigger.old. Study the governor limits and make sure your code splits its work into batches, doing as little work as possible in the loop. For example, if you have some additional data to query, build a set of IDs from the trigger’s records and query them once. Do not execute a SOQL statement for each loop iteration. If you need to run a DML statement, don’t put that in the loop either. Create a List of objects and execute a single DML statement on the entire List. Listing 4.40 shows an example of looping over a batch of Contact records (in the variable contacts) to produce a list of Assignment records to insert.

Listing 4.40 Batching DML Operations

List<Assignment__c> toInsert = new List<Assignment__c>();
for (Contact contact : contacts) {
  toInsert.add(new Assignment__c(
    Contact__r = contact));
}
insert toInsert;

Error Handling

Errors are handled in triggers with try, catch blocks, consistent with other Apex code. But uncaught errors within a trigger differ from other Apex code in how they can impact execution of the larger database transaction the trigger participates in.

A common use of errors in triggers is for validation. Strings describing validation errors can be added to individual records or fields using the addError method. Force.com continues to process the batch, collecting any additional errors, and then rolls back the transaction and returns the errors to the initiator of the transaction.

If an uncaught exception is encountered in a trigger, whether thrown by the system or the trigger code itself, the batch of records is immediately aborted, and all changes are rolled back.

Database Security in Apex

Outside of Anonymous blocks, Apex always runs in a privileged, system context. This gives it access to read and write all data. It does not honor object-, field-, and record-level privileges of the user invoking the code. This works well for triggers, which operate at a low level and need full access to data.

Where full access is not appropriate, Apex provides the with sharing keyword. For example, custom user interfaces often require that access to data is limited by the privileges of the current user. Using with sharing, the sharing rules applying to the current user are evaluated against the data requested by queries and updated in DML operations. This option is discussed in detail in Chapter 6, “User Interfaces.”