一. SOQL和SOSL数据检索

SOQL基础使用

SOQL执行以后，返回的结果可以是单条sObject记录(使用limit 1)，sObject列表或者一个数字来返回当前查询结果集满足的数量(使用count函数)。针对查询的方法，可以使用中括号([])括起来查询语句或者使用Database.query方法来执行。如果查询语句是一个确定的语句，可以直接使用中括号，如果是基于不同条件拼接最终形成的查询语句，可以使用 Database.query方法。

返回单条sObject记录：查询时使用 LIMIT 1用来限制返回的数据仅有一条。demo如下：

Account accountItem = [SELECT Id,Name FROM Account LIMIT 1];

实际项目中尽量避免使用 LIMIT1然后通过中括号方式直接赋值给变量，否则因为没有满足的结果返回时，会报错：System.QueryException: List has no rows for assignment to SObject

返回多条sObject记录：如果不添加 LIMIT 1，默认的返回都是sObject列表。当然，LIMIT 1也可以使用列表来接。demo如下：

List<Account> accountList = [SELECT Id,Name FROM Account where name = '123'];

String queryStatement = 'SELECT Id,Name FROM Account where name = \\'123\\'';
List<Account> accountList2 = Database.query(queryStatement); 

//获取1条的情况，此种写法保险
List<Account> accountList3 = [SELECT Id,Name FROM Account where name = '123' LIMIT 1];
Account accountItem;
if(accountList3 != null && !accountList3.isEmpty()) {
	accountItem = accountList3.get(0);
}

apex针对查询有很多 government limitation。比如一个transaction中最多只允许查询50000条记录等，详情可以查看下方的 government limitation进行更好的学习。如果需要庞大的数据处理，推荐使用batch。

返回查询语句的数量：使用count函数返回查询条件在系统中数据的数量。demo如下：

Integer queryCount = [SELECT COUNT() FROM Account WHERE Name = '123'];
system.debug('*** queryCount: ' + queryCount);

String queryStatement = 'SELECT COUNT() FROM Account WHERE Name = \\'123\\'';
Integer queryCount2 = Database.countQuery(queryStatement);
system.debug('*** queryCount2: ' + queryCount2);

SOQL 关系查询

我们在Admin 基础篇的创建字段关于 lookup 以及master detail进行过简单介绍，这里我们再针对两个场景进行举例。

自定义的表关系。系统中有两个表：Demo__c 以及 Demo_Son__c，其中 Demo_Son__c表中有一个自定义字段Demo__c作为 Lookup字段关联到 Demo__c。 Child Relationship Name为Demo_Son，apex中可以针对表关系进行两种查询方式。对父表进行数据查询同时查所需要的满足的子表的数据以及对子表进行数据查询同时查询关联的父表的数据。

Untitled

Demo如下：

对父表进行数据查询同时查所需要的满足的子表的数据：找到子表中的关联字段，查看 Child Relationship Name，针对自定义字段，后缀添加 __r，即可访问到子表的字段信息，当然，子表也可以进行过滤操作等，详情查看SOQL语句

List<Demo__c> demoList = [SELECT Id, Name, 
							(SELECT Id,Name FROM Demo_Son__r)
						 FROM Demo__c
						 LIMIT 10];
system.debug(JSON.serialize(demoList));

返回结果：

[
    {
        "attributes": {
            "type": "Demo__c", 
            "url": "/services/data/v57.0/sobjects/Demo__c/a002w00000XEmvJAAT"
        }, 
        "Demo_Son__r": {
            "done": true, 
            "totalSize": 2, 
            "records": [
                {
                    "Name": "sub demo1", 
                    "attributes": {
                        "type": "Demo_Son__c", 
                        "url": "/services/data/v57.0/sobjects/Demo_Son__c/a012w000013ngWPAAY"
                    }, 
                    "Id": "a012w000013ngWPAAY", 
                    "Demo__c": "a002w00000XEmvJAAT"
                }, 
                {
                    "Name": "sub demo2", 
                    "attributes": {
                        "type": "Demo_Son__c", 
                        "url": "/services/data/v57.0/sobjects/Demo_Son__c/a012w000013ngWUAAY"
                    }, 
                    "Id": "a012w000013ngWUAAY", 
                    "Demo__c": "a002w00000XEmvJAAT"
                }
            ]
        }, 
        "Id": "a002w00000XEmvJAAT", 
        "Name": "test demo record1"
    }
]

对子表进行数据查询同时查询关联的父表的数据：找到子表的关联字段，对API Name，由 __c修改成 __r即可访问到父表的字段信息。这种操作除了针对查询，也可以进行过滤使用，详情查看SOQL语句解析

List<Demo_Son__c> demoSonList = [SELECT Id,Name, Demo__r.Name
									FROM Demo_Son__c
									LIMIT 10];
system.debug(JSON.serialize(demoSonList));

返回结果：

[
    {
        "attributes": {
            "type": "Demo_Son__c", 
            "url": "/services/data/v57.0/sobjects/Demo_Son__c/a012w000013ngWPAAY"
        }, 
        "Demo__c": "a002w00000XEmvJAAT", 
        "Id": "a012w000013ngWPAAY", 
        "Demo__r": {
            "attributes": {
                "type": "Demo__c", 
                "url": "/services/data/v57.0/sobjects/Demo__c/a002w00000XEmvJAAT"
            }, 
            "Id": "a002w00000XEmvJAAT", 
            "Name": "test demo record1"
        }, 
        "Name": "sub demo1"
    }, 
    {
        "attributes": {
            "type": "Demo_Son__c", 
            "url": "/services/data/v57.0/sobjects/Demo_Son__c/a012w000013ngWUAAY"
        }, 
        "Demo__c": "a002w00000XEmvJAAT", 
        "Id": "a012w000013ngWUAAY", 
        "Demo__r": {
            "attributes": {
                "type": "Demo__c", 
                "url": "/services/data/v57.0/sobjects/Demo__c/a002w00000XEmvJAAT"
            }, 
            "Id": "a002w00000XEmvJAAT", 
            "Name": "test demo record1"
        }, 
        "Name": "sub demo2"
    }
]

标准的表关系，demo中以account和opportunity举例。其中 Opportunity有一个字段关联到Account，关联的字段API Name为 AccountId， Child Relationship Name为 Opportunities。

Untitled

对父表进行数据查询同时查所需要的满足的子表的数据：针对标准的字段，直接使用 Child RelationName，不需要添加任何的后缀。

List<Account> accountList = [SELECT Id,Name, (SELECT Id,Name FROM Opportunities)
							FROM Account
							LIMIT 2];
system.debug(JSON.serialize(accountList));

返回结果：

[
    {
        "attributes": {
            "type": "Account", 
            "url": "/services/data/v57.0/sobjects/Account/0012w00001HVFCXAA5"
        }, 
        "Id": "0012w00001HVFCXAA5", 
        "Name": "授权的示例帐户"
    }, 
    {
        "attributes": {
            "type": "Account", 
            "url": "/services/data/v57.0/sobjects/Account/0012w00001HSGjxAAH"
        }, 
        "Opportunities": {
            "done": true, 
            "totalSize": 3, 
            "records": [
                {
                    "Name": "GenePoint Standby Generator", 
                    "attributes": {
                        "type": "Opportunity", 
                        "url": "/services/data/v57.0/sobjects/Opportunity/0062w00000IjpuOAAR"
                    }, 
                    "AccountId": "0012w00001HSGjxAAH", 
                    "Id": "0062w00000IjpuOAAR"
                }, 
                {
                    "Name": "GenePoint SLA", 
                    "attributes": {
                        "type": "Opportunity", 
                        "url": "/services/data/v57.0/sobjects/Opportunity/0062w00000IjpupAAB"
                    }, 
                    "AccountId": "0012w00001HSGjxAAH", 
                    "Id": "0062w00000IjpupAAB"
                }, 
                {
                    "Name": "GenePoint Lab Generators", 
                    "attributes": {
                        "type": "Opportunity", 
                        "url": "/services/data/v57.0/sobjects/Opportunity/0062w00000IjpuoAAB"
                    }, 
                    "AccountId": "0012w00001HSGjxAAH", 
                    "Id": "0062w00000IjpuoAAB"
                }
            ]
        }, 
        "Id": "0012w00001HSGjxAAH", 
        "Name": "GenePoint"
    }
]