soql with pagenation

SOQL: ( Salesforce object Query Language  )

We write soql queries to fetch the data from the Sobjects .

Basic SOQL Syntax :

[Select fieldset from Sobject ];

SOQL will return List<Sobject > as a result

Ex: Write a soql query to fetch name,industry  values from Account  sobject

List<Account> acc=[select id,name ,Industry from Account];

Ex: Write soql query to fetch lastname,firstname from Contact sobject

List<Contact> cons=[select id,lastname,firstname from Contact];

Ex: Write a soql query to fetch  opportunity name and amount from opportunity sobject

List<Opportunity> opps=[select name ,amount from opportunity];

Note : Maximum no of record what soql query can return is 50000

Note : SOQL query  length cannot exceed 10,000 characters

Limit : LIMIT is an optional clause

This is  used to specify  maximum how many records should be  returned from the soql query

result set

Synatx : [select feildset from Sobject limit size];

Ex:Write a soql query to fetch first 5 records from the Account object

List<Account> accs=[select id, name ,Industry from Account limit 5];

Ex: Write a soql query to fetch 100 contacts from contact object

List<Contact> cons=[select id , lastname ,firstname from Contact  limit 100];

Offset:  This is used to specify starting record from which result should be fetched from the SOQL

query result set ;

Maximum value of offset is 2000

Synatax: List<Sobject> result=[select fieldset from Sobject offset size ];

Ex: Write a soql query on Account object to fetch all records from Account from 10 th record

List<Account> acc=[select id,name ,industry from Account offset 10];

Ex: Write SOQL query to fetch all the customer records  starting from 5th record

List<Customer__c> custs=[select id,Customer_name__c,AType__c from Customer__c offset 5];

Combination of Limit and offset

When you are taking limit and offset combination always limit followed by offset

Ex: Write a soql query to fetch 10 records from Account starting from 5th record

List<Account> accs=[select id,name,Industry from  Account limit 10 offset 5 ];

Secanrio :

Implementing the pagination concept on the Account object using the SOQL offset and limit

Create a visualforce page which displays a set of 5  records  at a time in  the pageBlocktable with

two Buttons previous and next

When you click on the next button next set of  5 records should be displayed on the vf page

pageBlockTable

When you click  on previous button it should display previous of set  5 records

If there are no next records then next button should be disabled

If  there are no previous records then previous button should be disabled

Apex Class :

public  class SOQLPagination {

public List<Account> accs{set;get;}

public  Integer size {set;get;}

public  Integer  flimit{set;get;}

public Integer acount{set;get;}

public SOQLPagination(){

size=5;

flimit=0;

    acount=[select count() from Account];

accs=[select id,name,industry from Account limit :size offset :flimit];

}

public void next(){

accs=[select id,name,industry from Account limit :size offset :flimit];

}

public void previous(){

flimit=flimit+5;

accs=[select id,name,industry from Account limit :size offset :flimit];

}

if(flimit>0){

flimit=flimit-5;

}

Visualforce page :

<apex:page controller="SOQLPagination" >

<apex:form>

<apex:pageBlock title="Account Pagination" id="one">

<apex:pageBlockTable value="{!accs}" var="a">

<apex:column value="{!a.name}" />

<apex:column value="{!a.industry}" />

</apex:pageBlockTable>

<apex:pageBlockButtons location="bottom">

<apex:outputPanel>

<apex:inputText value="{!flimit}" size="2" />

of<apex:inputText value="{!acount}" size="2"/>

disabled="{!flimit <=0}" />

disabled="{!flimit+size > acount }" />

</apex:outputPanel>

<apex:commandButton value="Previous" action="{!previous}"

<apex:commandButton value="Next" action="{!next}"

</apex:pageBlockButtons>

</apex:pageBlock>

</apex:form>

</apex:page>