Soql代码实例一般的数据展示到模糊查询

最新推荐文章于 2023-11-24 09:36:54 发布

原创最新推荐文章于 2023-11-24 09:36:54 发布 · 1.4k 阅读

2 ·

CC 4.0 BY-SA版权

本文介绍了几种使用SOQL进行高级查询的方法，包括排序、限制、偏移、分页及动态查询等，并展示了如何在Apex页面块中实现这些功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SoqlExample1:

<apex:page controller="SoqlExample1">
    <apex:form >
    	<apex:pageBlock title="SoqlExample1">
            <apex:pageBlockButtons location="top">
                <apex:commandButton value="Sort By Name" action="{!getSortByName}" reRender="dataTable"/>
                <apex:commandButton value="Sort By Industry" action="{!getDescOrder}" reRender="dataTable"/>
                <apex:commandButton value="Limits" action="{!getLimits}" reRender="dataTable"/>
                <apex:commandButton value="Offset" action="{!getOffset}" reRender="dataTable"/>
                <apex:commandButton value="All Records" action="{!getAllRecords}" reRender="dataTable"/>
                <apex:commandButton value="Recycle Bin" action="{!getDeletedRecords}" reRender="dataTable"/>
                <apex:commandButton value="Pagination" action="{!getPagination}" reRender="dataTable"/>
            </apex:pageBlockButtons>
            <apex:pageBlockTable value="{!accs}" var="a" id="dataTable">
                <apex:column value="{!a.Id}"/>
                <apex:column headerValue="Name">
                    <apex:outputLink value="/{!a.Id}">{!a.Name}</apex:outputLink>
                </apex:column>
                <apex:column value="{!a.industry}"/>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>
<!--
总结：
1、reRender控制着异步刷新某一指定的区块，通过Id关联展示数据的源；
2、location表示按钮出现的位置，不指定显示上下两个按钮；
-->

public class SoqlExample1 {
    public List<Account> accs {get;set;}
    
    // 初始化List<Sobject>，默认展示所有未被删除的记录 == [select id, name, industry from account where isDeleted = false all rows];
    public SoqlExample1() {
        accs = [select id, name, industry from account];
    }
    
    // 查询记录按姓名排序，默认为升序asc keywords：order by ／ asc
    public void getSortByName() {
        accs = [select id, name, industry from account order by name asc];
    }
    
    // 查询记录按行业降序desc排序 keywords：order by / desc
    public void getDescOrder() {
        accs = [select id, name, industry from account order by industry desc];
    }
    
    // 查询20条记录 keywords：limit
    public void getLimits() {
        accs = [select id, name, industry from account limit 20];
    }
    
    // 按偏移量为5查询记录
    public void getOffset() {
      accs = [select id, name, industry from account offset 5];
    }
    
    // 查询所有记录，包括已经被删除的记录 keywords：all rows
    public void getAllRecords() {
        accs = [select id, name, industry from account all rows];
    }
    
    // 查询所有被删除的记录 keywords：where isDeleted = true all rows
    public void getDeletedRecords() {
        accs = [select id, name, industry from account where isDeleted = true all rows];
    }
    
    // 分页
    public void getPagination() {
        accs = [select id, name, industry from account limit 5 offset 5];
    }
}
/*
Note: Offset value can not more than 2000
keywords: 
  1. Limit
  2. order by
  3. offset
  4. all rows
*/

SoqlExample2:

<apex:page controller="SoqlExample2">
    <apex:form >
    	<apex:pageBlock title="SoqlExample2">
            <apex:pageBlockButtons location="top">
                <apex:commandButton value="After 2016" action="{!closedAfter2016}" reRender="dataTable"/>
                <apex:commandButton value="Last Year" action="{!lastYear}" reRender="dataTable"/>
                <apex:commandButton value="Last Month" action="{!lastMonth}" reRender="dataTable"/>
                <apex:commandButton value="Next Week" action="{!nextWeek}" reRender="dataTable"/>
            </apex:pageBlockButtons>
            <apex:pageBlockTable value="{!opps}" var="o" id="dataTable">
                <apex:column headerValue="Name">
                    <apex:outputLink value="/{!o.Id}">{!o.Name}</apex:outputLink>
                </apex:column>
                <apex:column value="{!o.amount}"/>
                <apex:column value="{!o.closeDate}"/>
                <apex:column value="{!o.createdDate}"/>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

public class SoqlExample2 {
    public List<Opportunity> opps {get;set;}
    
    public SoqlExample2() {
        opps = [select id, name, amount, closeDate, createdDate from opportunity];
    }
    
    public void closedAfter2016() {
        opps = [select id, name, amount, closeDate, createdDate from opportunity where closeDate > LAST_N_YEARS:1];
    }
    
    public void lastYear() {
        opps = [select id, name, amount, closeDate, createdDate from opportunity where closeDate = LAST_YEAR];
    }
    
    public void lastMonth() {
        opps = [select id, name, amount, closeDate, createdDate from opportunity where createdDate = LAST_MONTH];
    }
    
    public void nextWeek() {
        opps = [select id, name, amount, closeDate, createdDate from opportunity where closeDate = NEXT_WEEK];
    }
}

SoqlExample3:

<apex:page controller="SoqlExample3">
    <apex:form >
    	<apex:pageBlock title="Soql Search">
            <apex:pageBlockButtons location="top">
            	<apex:commandButton value="Search" action="{!search}"/>
                <apex:commandButton value="Dynamic Search" action="{!dynamicSearch}"/>
            </apex:pageBlockButtons>
        	<apex:pageBlockSection >
            	<apex:pageBlockSectionItem >
                    <apex:outputLabel value="Search Name"/>
                    <apex:inputText value="{!aName}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Search Industry"/>
                    <apex:inputText value="{!aIndustry}"/>
                </apex:pageBlockSectionItem>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock id="dataTable" title="Result" rendered="{! !ISNULL(accs)}">
            <apex:pageBlockTable value="{!accs}" var="a">
            	<apex:column >
                    <apex:outputLink value="/{!a.Id}">{!a.Name}</apex:outputLink>
                </apex:column>
                <apex:column value="{!a.Industry}"/>
                <apex:column value="{!a.createdDate}"/>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

public class SoqlExample3 {
    public List<Account> accs {get;set;}
    public String aName {get;set;}
    public String aIndustry {get;set;}
    
    // 查询单个记录，精确查询
    public void search() {
        accs = [select id, name, industry, createdDate from account where name = :aName];
    }
    
    // 动态模糊查询
    public void dynamicSearch() {
        String nCdt = '%'+aName+'%';
        String iCdt = '%'+aIndustry+'%';
        String sql = 'select id, name, industry, createdDate from Account';
        
        // List集合级别：list.size() = 0, list = null, list.isEmpty()/字符串级别： str.length(), str = '', str = null, str.isEmpty()
        if(aName.length()>0 && aIndustry.length()>0) {
            sql += ' where name like :nCdt and industry like :iCdt';
        }else {
            if(aName.length()>0) {
              sql += ' where name like :nCdt';
            }
            if(aIndustry.length()>0) {
                sql += ' where industry like :iCdt';
            }
        }
        
        System.debug('sql: '+sql);
        accs = Database.query(sql);
        sql = '';// 清空sql条件语句
    }
}
/*
 * 
sucess：
String sql = 'select id, name from account where name like \'%test%\'';//模糊查询必须加\'% %/'
List<Account> accs = Database.query(sql);
System.debug('accs: '+accs);
fail：
String sql = 'select id, name from account where name like \'%test';
String sql = 'select id, name from account where name like test%\'';
String sql = 'select id, name from account where name like %test%';
*/