jTable Pagination in Java Web Applications

This article is a continuation of my previous articles on how to use jQuery jTable plugin in Java Web applications. jQuery jTable plugin offers an easy way to achieve AJAX based crud (create, read, update and delete) functionality and present the data in a table. This article explains how to use the pagination component to do server side paging for jTable in Java web application.

Articles on jTable in Java Web Applications

• jQuery jTable plugin in Java Web Applications - An Introduction
• Setup and Load Data in jTable using Servlets and JSP
• AJAX based CRUD Operations in Java Web Applications using jTable jQuery plugin
• Understanding User Interface Options
• jTable Pagination in Java Web Applications

These articles on jTable jQuery plugin have been included in the Official jTable Documentation. Thanks to Halil İbrahim Kalkan for that.

I am not going to elaborate on how to setup and load data in jTable as I have already done that in my previous articles. In case you are new to jTable, I suggest you to start from here. I am going to take the sample application I created for explaining AJAX based crud operations in jTable plugin and continue to implement paging for that application.

DEMO DOWNLOAD

1. First of all download the sample application from here.

2. Open Eclipse and create a new dynamic web project. Import the downloaded project to the newly created one.

3. Follow the steps in this article here to create mysql table and make sure you have more than 10 records in the table.

4. Now right click on index.jsp page and run the project in Apache Tomcat Server. You will see a table displaying records without pagination such as the one shown below.

5. Next step is to enable paging with desired initial page size so that the table displays paging component in the front end. To do this open 'index.jsp' file in editor and modify the jQuery script code to add two lines as shown below.

<script type="text/javascript">
    $(document).ready(function () {
        $('#PersonTableContainer').jtable({
            title: 'Table of people',
            paging: true, //Enable paging
            pageSize: 10, //Set page size (default: 10)          
            actions: {
                listAction: 'CRUDController?action=list',
                createAction:'CRUDController?action=create',
                updateAction: 'CRUDController?action=update',
                deleteAction: 'CRUDController?action=delete'
            },
            fields: {
                userid: {
                title:'S.NO',
                    key: true,
                    list: true,
                    create:true
                },
                firstName: {
                    title: 'First Name',
                    width: '30%',
                    edit:false
                },
                lastName: {
                    title: 'Last Name',
                    width: '30%',
                    edit:true
                },
                email: {
                    title: 'Email',
                    width: '20%',
                    edit: true
                }                
            }
        });
        $('#PersonTableContainer').jtable('load');
    });
</script>

Code to be added is highlighted in yellow color in the above code snippet. The first option - paging:true enables paging and the second option sets the initial number of records to be displayed per page. Note that page size can be set to 10, 25, 50 or 100 dynamically by the user. If you run this project, you will see paging component at the bottom of the table as shown in the screenshot below.

You can see some junk values in the paging component, it is because we have only enabled paging but we are yet to handle that at the server side.

6. Before implementing paging at the server side, let us understand how pagination works in jTable.
Once we have enabled pagination in the front end (explained in step 5), whenever a pagination element is clicked, jTable sends two query string parameters to the url specified in the 'listAction' url. 

--- jtStartIndex - Current page start index. For example, if the user clicks on third page and the number of records per page is 10, then the value for jtStartIndex will be 20. (Index starts from 0)
--- jtPageSize - Number of records to display per page

Remember that, the url specified in the 'listAction' option has business logic to fetch all records from the back end and return response in json format. Now in order to handle pagination there are two changes that has to be done in the server side.

-- Modify mysql query to fetch only a subset of records based on the current start index and page size. These values are sent along with the request as query string parameters by jTable.

-- Get Total number of records present in the result set and append that to the JSON response being sent

Open 'CRUDContoller.java' file. Press 'Ctrl+F' to open find window and look for 'if(action.equals("list"))'. Replace this if loop with the below code,

if(action.equals("list")){
   try{
       //Fetch Data from User Table
       int startPageIndex=
          Integer.parseInt(request.getParameter("jtStartIndex"));
       int numRecordsPerPage=
          Integer.parseInt(request.getParameter("jtPageSize"));
       lstUser=dao.getAllUsers(startPageIndex,numRecordsPerPage);
       //Get Total Record Count for Pagination
       int userCount=dao.getUserCount();
       //Convert Java Object to Json
       JsonElement element = 
          gson.toJsonTree(lstUser,new TypeToken<List<User>>()                 {}.getType());
       JsonArray jsonArray = element.getAsJsonArray();
       String listData=jsonArray.toString();        //Return Json in the format required by jTable plugin
       listData=           {\"Result\":\"OK\",\"Records\":"+listData+",\"TotalRecordCount\":"+userCount+"}";
       response.getWriter().print(listData);
   }catch(Exception ex){
       String error="     {\"Result\":\"ERROR\",\"Message\":"+ex.getMessage()+"}";
       response.getWriter().print(error);
       ex.printStackTrace();
   }
}

Added and modified code snippets are highlighted in yellow.

Open 'CrudDao.java' file and add below method to it,

public int getUserCount(){
int count=0;
try {
                Statement statement =                     
                  connection. createStatement();
ResultSet rs = 
                  statement.executeQuery
                      ("select count(*) as count from tblUser") while (rs.next()) {
count=rs.getInt("count");
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}

This method gets the count of total number of records in the result set.

Finally, to make mysql return only a subset of records according to the page offset (startindex and number of records per page), mysql query should be modified as follows,

select * from tblUser limit <startIndex>,<pageSize>

Find 'getAllUsers()' in 'CrudDao.java' file and replace this method with the below modified code,

public List<User> getAllUsers(int jtStartIndex, int jtPageSize) {
List<User> users = new ArrayList<User>();
String startIndex=Integer.toString(jtStartIndex);
String pageSize=Integer.toString(jtPageSize);
String query="select * from tblUser limit "+startIndex+","+pageSize;
try {
Statement statement =         connection.createStatement();
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
        User user = new User();
        user.setUserid(rs.getInt("userid"));
user.setFirstName(rs.getString("firstname"));
user.setLastName(rs.getString("lastname"));
user.setEmail(rs.getString("email"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}

We are done! Run the project in Apache Tomcat and the paging component should work perfectly. You can also download completed sample code and run that instantly, but make sure you have mysql table setup according to the table structure as mentioned in my article here and insert more than ten records in it.

DEMO DOWNLOAD

Keep yourself subscribed for getting programmingfree articles delivered directly to your inbox once in a week. Thanks for reading!

Subscribe to GET LATEST ARTICLES!