数据库

SOQL: Salesforce Object Query Language

SOSL: Salesforce Object Search Language

数据库

DML

DML配置

新增

Account a = new Account();
a.Name = 'BestLove';
insert a;

Account account = new Account(Name = 'Tang Xiao Feng');
insert account;

删除

Account a = [SELECT Id FROM Account WHERE Name = 'BestLove' LIMIT 1];
delete a;

还原删除

salesforce的删除动作会将数据在回收站中保留15天，15天之内可以使用undelete来还原

ALL ROWS 关键字能查询到包含已删除数据的所有信息

Account a = new Account(Name='BestLove');
insert(a);
insert(new Contact(LastName='David',AccountId=a.Id));
delete a;

Account[] savedAccts = [SELECT Id, Name FROM Account WHERE Name = 'BestLove' ALL ROWS]; 
try {
    undelete savedAccts;
} catch (DmlException e) {

}

修改

Account a = [SELECT Id,Name FROM Account WHERE Name = 'BestLove' LIMIT 1];
a.Name = 'Hello world';
update a;

查询

Account a = [SELECT Id,Name FROM Account WHERE Name = 'BestLove' LIMIT 1];

// 使用__r语法查找关联对象的字段
// 子查母，比如：Company__r.Name，需要是查找字段
Account b = [SELECT Id,Name, Company__r.Name FROM Account WHERE Name = 'BestLove' LIMIT 1];

// 母查子，需要在子表的查找字段上设置子级关系名称，比如在Account（子表上）有一个字段叫（CompanyId），然后设置了子级关系名称为：CompanyOnAccount
Company__c c = [SELECT Id,Name,(SELECT Id,Name FROM CompanyOnAccount__r) FROM Company__c WHERE Name = 'BestLove' LIMIT 1];

Find (SOSL)

[FIND :xxx IN xxx FIELDS RETURNING Account(x,xx,xxx),Contract(x,xx,xxx)...];

public class FindCLA
{
    List<Account> account = new List<Account>();
    List<Contract> contract = new List<Contract>();

    public void function findByPhone(String phone)
    {
        List<List<SObject>> s = [Find :phone in Phone FIELDS 
                RETURNING 
                Account(Id, Name, Phone),
                Contract(Id, Phone)
            ];

        List<SObject> restlt = new List<SObject>();
        restlt.addAll(s[0]);
        restlt.addAll(s[1]);

        Integer size = restlt.size();

        for (Integer i; i < size; i++) {
            if (restlt[i].getSObjectType() == Account.sObjectType()) {
                account.add((Account)restlt[i]);
            } else if(restlt[i].getSObjectType == Contract.sObjectType()) {
                contract.add((Contract)restlt[i]);
            }
        }

    }
}

批量处理

salesforce限制

• 150条 SQL 语句（使用批量操作可以减少数据库操作）
• 10000 条数据行数（限制10000条防止内存溢出）

List<Account> accounts = [SELECT Id,Name,Phone FROM Account LIMIT 100];
List<Account> updateAccounts = new List<Account>();

for (Account account : accounts) {
    account.Name = account.Name + '_new';
    updateAccounts.add(account);
}

update updateAccounts;

// 如果希望部分成功，使用以下方法，将第2个参数设置为false
Database.update(updateAccounts, false);

默认情况下，salesforce为保证数据完整性，批量操作是具有事务性的，如果需要在错误情况下不回滚，可以使用 Database 类

List<Account> accounts = new List<Account>();
accounts.add(new Account('Name' ='张三'));
accounts.add(new Account('Name' ='李四'));

// 第2个参数默认为true，传入false在发生异常的情况下不回滚提交的数据
Database.SaveResult[] saveResults = Database.insert(accounts, false);

for (Database.SaveResult s : saveResults) {
    if (s.isSuccess()) {

    } else {
        for (Database.Error err : s.getErrors()) {
            System.debug(err.getFields() + err.getMessage() + err.getStatusCode());
        }
    }
}

关联操作

新增

Opportunity newOpportunity = new Opportunity(
    Name='OpportunityWithAccountInsert',
    StageName='Prospecting',
    CloseDate=Date.today().addDays(7));

Account accountReference = new Account(
    MyExtID__c='SAP111111');                

newOpportunity.Account = accountReference;

Database.SaveResult results = Database.insert(newOpportunity);

修改

try {
    // 根据联系人信息查询账号信息
    Contact queriedContact = [SELECT Account.Name 
                              FROM Contact 
                              WHERE FirstName = 'Joe' AND LastName='Smith'
                              LIMIT 1];

    queriedContact.Phone = '1383838438';
    queriedContact.Account.Industry = 'Technology';

    update queriedContact;
    update queriedContact.Account; 
} catch(Exception e) {
    System.debug('An unexpected error has occurred: ' + e.getMessage());
}

修改或新增

List<Account> accounts = [SELECT Name FROM Account LIMIT 10];

for (Account account : accounts) {
    account.Name = account.Name + '_new';
}

accounts.add(new Account('Name' = 'First Insert'));

upsert accounts;

锁

FOR UPDATE 永远加在最后面，并且不能有 ORDER BY 语句

// 和 MySQL 有些许不同
Account [] accts = [SELECT Id FROM Account LIMIT 2 FOR UPDATE];

增量相关

LastModifiedDate可用于增量更新

select id,LastModifiedDate,CreatedDate From Account where LastModifiedDate >= 2024-12-03T00:00:00+08:00