+ Start a Discussion
hari azmeera 8hari azmeera 8 

paginate values using datatable/pageblocktable

Mahesh DMahesh D
Hi Hari,

Please find the below code:

Visualforce Page:

 
<apex:page title="Salesforce SOQL Offset Example Using Visualforce" controller="soql_offset_example" showHeader="false" sidebar="false" readOnly="true" cache="false">

<apex:sectionHeader subtitle="SOQL Offset Example" title="Square Root Table"/>

<apex:pageBlock >   
   <apex:pageBlockButtons location="top" >
   <apex:outputPanel id="myButtons">
   <apex:form >
        <apex:commandButton action="{!Beginning}" title="Beginning" value="<<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>
        <apex:commandButton action="{!Previous}" title="Previous" value="<" disabled="{!disablePrevious}" reRender="myPanel,myButtons"/>        
        <apex:commandButton action="{!Next}" title="Next" value=">" disabled="{!disableNext}" reRender="myPanel,myButtons"/>
        <apex:commandButton action="{!End}" title="End" value=">>" disabled="{!disableNext}" reRender="myPanel,myButtons"/>        
   </apex:form>
   </apex:outputPanel>
   </apex:pageBlockButtons>

   <apex:pageBlockSection columns="1">
       <p>This Visualforce Page demonstrates the usage of the "OFFSET" clause in a SOQL SELECT statement.  You can "Paginate" through 
       a large result set quickly.  This example allows a user to paginate up and down through a result set 20 records at a time.  
       Using the "VCR" buttons above, you can also quickly skip to the end or the beginning of the set.</p>
       <p>The accompanying code can be viewed here:  <a href="http://blog.redpointsolutions.com/bid/182738/Add-Pagination-to-your-Visualforce-Pages-using-the-SOQL-OFFSET-Clause" target="_blank">RedPoint Solutions BLOG</a></p>
   </apex:pageBlockSection>

   <apex:pageBlockSection title="Numbers and their Square Roots (Total List Size: {!total_size})" collapsible="false">
   <apex:outputPanel id="myPanel">
   <apex:pageMessages id="theMessages" />
   <apex:pageBlockTable value="{!accounts}" var="acc" align="center">
        <apex:column value="{!acc.Name}" />
        <apex:column value="{!acc.AccountNumber}" />
        <apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}</apex:facet>
   </apex:pageBlockTable>
   </apex:outputPanel>
   </apex:pageBlockSection>

</apex:pageBlock>

</apex:page>

Controller:
 
public with sharing class soql_offset_example {

   private integer counter=0;  //keeps track of the offset
   private integer list_size=20; //sets the page size or number of rows
   public integer total_size; //used to show user the total size of the list

   public soql_offset_example() {
   total_size = [select count() from Account]; //set the total size in the constructor
   }

   public Account[] getAccounts() {
      try {
         Account[] accounts = [select Id, Name, AccountNumber from Account order by Name limit :list_size offset :counter];

         return accounts;
      } catch (QueryException e) {
         ApexPages.addMessages(e);   
         return null;
      }
   }

   public PageReference Beginning() { //user clicked beginning
      counter = 0;
      return null;
   }

   public PageReference Previous() { //user clicked previous button
      counter -= list_size;
      return null;
   }

   public PageReference Next() { //user clicked next button
      counter += list_size;
      return null;
   }

   public PageReference End() { //user clicked end
      counter = total_size - math.mod(total_size, list_size);
      return null;
   }

   public Boolean getDisablePrevious() { 
      //this will disable the previous and beginning buttons
      if (counter>0) return false; else return true;
   }

   public Boolean getDisableNext() { //this will disable the next and end buttons
      if (counter + list_size < total_size) return false; else return true;
   }

   public Integer getTotal_size() {
      return total_size;
   }

   public Integer getPageNumber() {
      return counter/list_size + 1;
   }

   public Integer getTotalPages() {
      if (math.mod(total_size, list_size) > 0) {
         return total_size/list_size + 1;
      } else {
         return (total_size/list_size);
      }
   }
}

I also tested the above and below is the output:

User-added image



In the above example if you want to use the Datatable then replace the PageBlockTable with DataTable:
 
<apex:pageBlockTable value="{!accounts}" var="acc" align="center">
        <apex:column value="{!acc.Name}" />
        <apex:column value="{!acc.AccountNumber}" />
        <apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}</apex:facet>
   </apex:pageBlockTable>

to
 
<apex:dataTable value="{!accounts}" var="acc" align="center">
        <apex:column value="{!acc.Name}" />
        <apex:column value="{!acc.AccountNumber}" />
        <apex:facet name="footer">Showing Page # {!pageNumber} of {!totalPages}</apex:facet>
   </apex:dataTable>
write it outside the pageblock tag.

Please do let me know if it helps you.

Regards,
Mahesh
Amit Chaudhary 8Amit Chaudhary 8
StandardSetController objects allow you to create list controllers similar to, or as extensions of, the pre-built Visualforce list controllers provided by Salesforce
http://amitsalesforce.blogspot.in/2015/04/pagination-using-standardsetcontroller.html

You can instantiate a StandardSetController in either of the following ways:
List<account> accountList = [SELECT Name FROM Account LIMIT 20];
ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(accountList);
From a query locator:
ApexPages.StandardSetController ssc = 
new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Name,CloseDate FROM Opportunity]));
Visual force page
<apex:page controller="CustomPaginationController" sidebar="false">
    <apex:form >
        <apex:pageBlock >
            <apex:pageMessages ></apex:pageMessages>
            <apex:pageBlockButtons >
                <apex:commandButton action="{!Search}" value="Search" />
            </apex:pageBlockButtons>
            
            <apex:pageblockSection >
                <apex:inputText value="{!acc.Name}" label="Name"/> 
                <apex:inputText value="{!acc.Phone}" label="Phone" />
            </apex:pageblockSection>
        </apex:pageBlock>
        <apex:pageBlock id="resultId" rendered="{!if(lstAccount != null && lstAccount.size > 0, true,false )}">
            <apex:pageBlockButtons >
                <div style="text-align:right"> 
                  Total Records Found: {!Con.resultSize}  
      <apex:image url="/img/search_prevarrow_disabled.gif" styleClass="prevArrow" rendered="{!NOT(Con.HasPrevious)}"/>  
      <apex:image url="/img/search_prevarrow.gif" title="Previous Page" styleClass="prevArrow" rendered="{!Con.HasPrevious}"/>  
      <apex:commandLink action="{!Previous}" title="Previous Page" value="Previous Page" rendered="{!Con.HasPrevious}"/>  
      <apex:outputPanel styleClass="pShowLess noLink" style="color:grey" rendered="{!NOT(Con.HasPrevious)}">Previous Page</apex:outputPanel>           
      <apex:outputPanel styleClass="pShowLess noLink" style="color:grey" rendered="{!NOT(Con.HasNext)}">Next Page</apex:outputPanel>           
      <apex:commandLink title="Next Page" value="Next Page" rendered="{!Con.HasNext}" action="{!Next}"/>&nbsp;  
      <apex:image url="/img/search_nextarrow.gif" title="Next Page" styleClass="nextArrow" rendered="{!Con.HasNext}"/>  
      <apex:image url="/img/search_nextarrow_disabled.gif" rendered="{!NOT(Con.HasNext)}"/> 
      <img src="/s.gif" title="Last Page" alt="Last Page" class="last"/>         
                </div>
            </apex:pageBlockButtons>                
            <apex:pageBlockSection columns="1">
                <apex:pageBlockTable value="{!lstAccount}" var="acc" >
                    <apex:column value="{!acc.Name}"/>
                    <apex:column value="{!acc.Phone}"/>
                </apex:PageblockTable>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>
public with sharing class CustomPaginationController 
{
    public Account acc {get;set;}   
    public ApexPages.StandardSetController con{get; set;} 
    public CustomPaginationController ()
    {
       acc = new Account();
       lstAccount = new List<Account>();
    }
    public List<Account> lstAccount 
    {  
        get  
        {  
            if(con != null)  
                return (List<Account>)con.getRecords();  
            else  
                return null ;  
        }  
        set;
    }  
    public PageReference Search()
    {
        String query= '';
        String strFilter = '';
        if(acc.Name != null && (acc.Name ).trim() !='')
        {
           strFilter  = strFilter  +  ' where Name Like \''+acc.Name+'%\'' ;
        }
        if(acc.Phone != null && (acc.Phone).trim() !='' )
        {
           if(strFilter == '')
           { 
               strFilter  = strFilter  +  ' where Phone like \''+acc.Phone+'%\'' ;
           }
           else
           {
               strFilter  = strFilter  +  ' And Phone like \''+acc.Phone+'%\'' ;
           }
        }
        if(strFilter != '')
        {
            query = 'Select name ,id, phone from Account '+strFilter+ ' limit 1000';
            System.debug('Query ---->'+ query );
            con = new ApexPages.StandardSetController(Database.getQueryLocator(query)); 
            con.setPageSize(2);
        }
        else
        {
        }
       return null;
    }
    public Boolean hasNext  
    {  
        get  
        {  
            return con.getHasNext();  
        }  
        set;  
    }  
    public Boolean hasPrevious  
    {  
        get  
        {  
            return con.getHasPrevious();  
        }  
        set;  
    }  
    public Integer pageNumber  
    {  
        get  
        {  
            return con.getPageNumber();  
        }  
        set;  
    }  
    public void previous()  
    {  
        con.previous();  
    }  
    public void next()  
    {  
        con.next();  
    }  
   
}

User-added image

Please let us know if this will help you

Thanks
Amit Chaudhary


 
JyothsnaJyothsna (Salesforce Developers) 
Hi Hari,

Please check the below sample code.

Visualforce page
 
<apex:page controller="Pagenation1Controller" showHeader="false">
 <apex:form id="pg1">
 <apex:pageBlock >
 <apex:pageBlockTable value="{!leads}" var="ld">
 <apex:column headerValue="Name">
 <apex:outputField value="{!ld.Name}"/>
 </apex:column>
 <apex:column headerValue="Email">
 <apex:outputField value="{!ld.Email}"/>
</apex:column>
<apex:column headerValue="Company">
<apex:outputField value="{!ld.Company}"/>
</apex:column>
</apex:pageBlockTable>
<apex:outputLabel >Total no of recs:{!tot}</apex:outputLabel>

<div align="center">
 <apex:commandButton value="First Page" action="{!Firstbtn}" disabled="{!prv}" reRender="pg1"/>
 <apex:commandButton value="PreviousPage" action="{!prvbtn}" disabled="{!prv}" reRender="pg1"/>
 <apex:commandButton value="NextPage" action="{!Nxtbtn}" disabled="{!nxt}"  reRender="pg1"/>
 <apex:commandButton value="LastPage" action="{!lstbtn}" disabled="{!nxt}" reRender="pg1"/>
 </div>
 Display records per page <apex:selectList value="{!rec}" size="1" >
   <apex:selectOption itemLabel="1" itemValue="1" ></apex:selectOption>
   <apex:selectOption itemLabel="2" itemValue="2"></apex:selectOption>
   <apex:selectOption itemLabel="5" itemValue="5"></apex:selectOption>
   <apex:selectOption itemLabel="10" itemValue="10"></apex:selectOption>
   <apex:actionSupport event="onchange" reRender="pg1" action="{!updatePage}"/>
  </apex:selectList><br/>
 </apex:pageBlock>
 </apex:form>
</apex:page>

Controller
 
public with sharing class Pagenation1Controller {

  public Integer rec { get; set; }

    public Integer tot { get; set; }

   integer totalRecs = 0;
   integer count= 0;
   integer LimitSize= 1;
  
  public Pagenation1Controller()
   { 
     tot=totalRecs = [select count() from account];
    }public List<Lead> leads=new List<Lead>();
   public List<Lead> getLeads() {
   List<Lead> ld=[select Name,Email,Company from Lead LIMIT:limitsize OFFSET:count];
   system.debug('values are:' + ld);
        return ld;
    }
       public void updatePage() {
       leads.clear();
      limitsize=rec;
       leads=[select Name,Email,Company from Lead LIMIT:rec OFFSET:count];
    }
     public PageReference Firstbtn() {
     count=0;
        return null;
    }
      public PageReference prvbtn() {
      count=count-limitsize;
        return null;
    }
   
 public PageReference Nxtbtn() {
 count=count+limitsize;
        return null;
    }
 public PageReference lstbtn() {
 count= totalrecs - math.mod(totalRecs,LimitSize);
        return null;
    }


    public Boolean getNxt() {
        if((count+ LimitSize) > totalRecs)
       return true;
     else
       return false;
        
    }
     public Boolean getPrv() {
      if(count== 0)
          return true;
        else
          return false;
     
    
        }


    }

Screenshot

User-added image


Hope this helps you!
Best Regards,
Jytohsna
Vladimir KornienkoVladimir Kornienko

if integer count = 0; buttons  first page and previous  will not work should be corrected for integer count = 1; - which, when the page loads, will by default display the first page in the table