Spring MVC pagination - display all page numbers

Question

the database user table is huge, it would take 15 mins to finish a select statement so I am using this query:

select * from ( 
   select * from user order by userId desc 
) where ROWNUM > offset and ROWNUM <= offset + itemsPerPage;

then my DOA service method looks like

public List<User> getUserRange(int offset 1, limit itemsPerPage){

            mybatis code
.....}

The user object

public class User{
  String userName;
  String userDOB;
  String userAddress;
  .....setters and getters
}

and I want to display it in a simple JSP page, my problem is most examples on the internet would need to retrieve the User List in a single select statement

but I want the JSP page to display all the page numbers and when user clicks each page number, the number gets passed into the getUserRange() method, a fresh list of users can be rendered.

I did look into some taglibs on the github, haven't got much luck, if anyone could point me to the right direction, would be much appreciated.

Answer 1

there are a few options:

I am not including any samples here, because you can find on each one of those libraries official webwite

the old fashioned way using the displayTag

<dependency>
  <groupId>displaytag</groupId>
  <artifactId>displaytag</artifactId>
  <version>1.2</version>
</dependency>

which is a JSP oriented implementation doesn't utilizing new technologies such as Json, Jquery-UI, the development seems not so active

the popular way using the jquery-plugin datatables some say this is one of the most popular pagination plugin for the Java stack, currently very active, in my opinion offers most comprehensive features

    <dependency>
        <groupId>org.webjars</groupId>
        <artifactId>datatables</artifactId>
        <version>1.10.11</version>
    </dependency>

   <dependency>
      <groupId>org.webjars</groupId>
      <artifactId>jquery</artifactId>
      <version>2.2.3</version>
   </dependency>

the way I did it using another jquery-plugin jTable

   <dependency>
    <groupId>org.webjars</groupId>
    <artifactId>jTable</artifactId>
    <version>2.4.0</version>
   </dependency>

   <dependency>
    <groupId>org.webjars</groupId>
    <artifactId>jquery-ui</artifactId>
    <version>1.11.4</version>
   </dependency>

Comparisons: both jTable and datatables are based on json and jquery, at the time of the post it's very trendy thing to do, and better design compare to the old fashioned display tag

I found jTable is easier to understand, but datatables is more popular among my fellow developers. and I am certainly not recommend using any of the old fashioned displayTag technology

Webjars if anyone of you wondering what webjars is, please take a look of this in short it stops you manually download and dropping jquery.js or other JavaScript libs in your /webapp/WEB-INF/*** folder. by using Maven to manage all your web dependencies.

Answer 2

Here is the method to find out the total number of pages get the total number of rows using

select count(*) as total_rows from table_name;

now call the below method

setTotal_pages("10",total_rows);

where 10 is the total number of content you want to display

public void setTotal_pages(int display_result,int total_rows) {


    System.out.println("display_result :"+display_result+" total_rows "+total_rows);

    int total_pages_temp = total_rows/display_result;

    System.out.println("total_pages_temp "+total_pages_temp);


    if(total_rows % display_result !=0){
        System.out.println(" in if ");
        total_pages_temp = total_pages_temp+1;

    }
    System.out.println("total_pages_temp "+total_pages_temp);
    //return total_pages;

    total_pages = total_pages_temp;
}

now below method will help to get the limit accoring to the page_number let say user click on the hyper link page _number 2 now to pass this 2 to method getStaringLimit

call the below method int get start_limit = getStaringLimit('2','10'); this will give you the limit

//method using for pagination
    public int getStaringLimit(int page_number,int display_counts){

        if(page_number==1){
            return 0;
        }else{
            //if p.no=2 then (2-1) = 1 then 1*10(where 10 is the display_counts)
            //so 10 is the starting limit  in pagenumber 2
            return (page_number-1)*display_counts;
        }

    }

now you just have to run the query

select * from table_name limit  start_limit,display_counts;

and you will get the result according to the page numbers

so fisrt you have to call setTotal_pages(int display_result,int total_rows) this will return number of pages then set the value in the hyperlink according to the pagenmber after that you have to cal getStaringLimit(int page_number,int display_counts) it will return starting limit so that you can execut the select query

 select * from table_name limit     start_limit,display_counts;

I will suggest you to use ajax when user clicks on the page number

Answer 3

Suggestions

Before I give my proposals, I want to suggest that you use Spring data repository. Just do some research on that, you will be amazed how much it helps you.

Answer to your question

• SQL query
  Your query smells like ORACLE query, I am not very good at that but it looks fine. For MySql-like users looking at this post, here is the query I will use:
  SELECT * FROM user limit ?,? (first ? is offset, second is size)

• Model (It is better to have an object that carry the page data around
  Create a Pagination Class like so:

  class Page{
      private Integer offset; //offset
      private Integer size; //how many items per page
      private Integer currentPageNum; //current page number, 1 based
  
      // default construction prohibited
      private Page(){
      }
  
      Public Page(Integer currentPageNum, Integer size){
      this.currentPageNum = currentPageNum;
      this.size = size;
      this.offset = currentPage * size - size; // important 
      }
      // POJO methods ommitted
  }  
  

• In your controller (e.g. servlets or spring controller)**

  • call a DAO's method getUsersCount() so that you will know the total number of items(users)

    // total is used to find our exactly how many
    // pages are there
    // e.g. prev 1,2,3,4,5,6,7,8 next
    // total is 8 in this case
    Integer total = dao.getUserCount();
    
    Integer size = getSizeFromUserInterface...// jsp, predefined, etc..
    
    // find the page numbers 1,2,3,4,6,.. like above
    Integer maxPageNum = total % size == 0 ? (total / size) : (total / size + 1);
    // if total item count is 40, you have 40 users and
    // every page shows 5 users, you will have 8 pages total
    // according to the code above.
    // now you can generate a Interger List that contains the 8
    // page numbers from 1 through 8
    List<Integer> pageNumList = //trivial ommited 
    
    Integer currentPage = getCurrentPageFromUserInterface...// user click on a page number on your website
    Page page = new Page(currentPage, size);
    
    // call another dao method to retrieve the user 
    // according to the page info gathered above
    List<User> userList = dao.getUserRange(page.getOffSet, page.getSize);
    // save the pageNumList and userList and page in request scope
    // and forward to your jsp view, trivial.
    request.set...
    request.set...
    request.set...
    request.getRequestDispatcher("Your view").forward(request,response);
    

• In your dest view
  You will use the request scope's objects to show the page nav bar and the user list.

• Initialization
  You certainly need a controller to set default values of the page properties when the user list page first gets requested. e.g. pageNum is 1

Summary

This solution is by no means complete or perfect and it has a plenty of room to improve such as putting userList in Page as an attribute, make total number of items as a static member in page for the ease of later manipulation, but I think the idea will guide you to the right direction.