Pagination in Struts 2 using jQuery jTable plugin

 

 
This is the third article on jQuery jTable plugin in Struts 2 that describes on how to implement pagination feature to do server side paging in struts 2 using the JQuery jTable plugin and here I have not explained about how to setup jTable plugin in struts 2. So If you have not read my previous articles “Setting up JQuery jTable plugin in Struts 2” and “Ajax based curd operation in Struts 2 using JQuery jTables plugin”, I will recommend that you read that article first because first one explains how you can integrate the JTable plugin in Struts 2 application and second article explains on how to implement ajax based curd operation. This article will assume that the code for the integration of the jQuery JTable plugin is implemented, and only the code required for implementing pagination in Struts 2 using jTable is explained here.
 

Setup

 
As described above, the prerequisite for this code is that you integrate the jQuery jTable plugin into the Struts 2. You can find detailed instructions here, at JQuery jTable plugin in Struts 2, so now 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.
 
1. Download sample application from here and import the project in eclipse
2. Follow the steps in this article here to create table in database.
 

Steps to enable Paging:

From the browser perspective: jTable

 
To enable paging, paging option must set to true. You can also set pageSize option (default value is 10) in jQuery Script code.
 

$('#StudentTableContainer').jtable({
    //...
    paging: true, //Set paging enabled
    pageSize: 3, //Set page size
    actions: {
        //...
    },
    fields: {
        //...
    }
});

 
Note: pageSize sets the initial number of records to be displayed per page.
 

Modified Jsp page

	
jTable Pagination in Java Web Applications
	

From the server’s perspective: Servlet

 
If paging is enabled, jTable sends two query string parameters to the server on listAction AJAX call:
• jtStartIndex: Start index of records for current page.
• jtPageSize: Count of maximum expected records.
And it expects additional information from server:
• TotalRecordCount: Total count of records.
 
In our previous example the url specified in the ‘listAction‘ option has business logic to fetch all records from database. Now in order to handle pagination this ‘listAction’ option should fetch only a part of records for each page, So handle this there are two changes that has to be done in the server side .
 
1. Modify oracle query to fetch only a subset of records based on the jtStartIndex and jtPageSize.

Since these values are sent along with the request as string parameters by jTable so add following member variable in struts 2 action class and create getters and setters for the same

// Holds Start Page Index
private int jtStartIndex;
// Hold records to be displayed per Page
private int jtPageSize;

 
2. As mentioned above, jTable need TotalRecordCount to be present in the json response, For which add a member variable totalRecordCount in struts 2 action class and create getters and setters for the same.
 
Now replace the list method in action class with the below code,
 

public String list() {
try {
	// Fetch Data from Student Table
	records = dao.getAllStudents(jtStartIndex, jtPageSize);
	// Get Total Record Count for Pagination
	totalRecordCount = dao.getStudentCount();
	result = "OK";
} catch (Exception e) {
	result = "ERROR";
	message = e.getMessage();
}
return Action.SUCCESS;
}

Changes made at Dao class

 
Add below two methods in ‘CrudDao.java’ file

1. Method to get the count of total number of records in the result set.
 

public int getStudentCount()
{
int count=0;
try
{
	Statement stmt = dbConnection.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS COUNT FROM STUDENT");
	while (rs.next())
	{
		count=rs.getInt("COUNT");
	}
}
catch (SQLException e)
{
	System.err.println(e.getMessage());
}
return count;
}

 
2. In order to return only a subset of records according to the page offset (jtStartIndex and jtPageSize), oracle query should be modified as follows,

In case of Oracle database:
“SELECT * from (Select M.*, Rownum R from STUDENT M) where r > ” + < jtStartIndex> +” and r <= "+< jtStartIndex + jtPageSize >;
 
In case of MySql database:

select * from STUDENT limit ,

Now modify getAllUsers in CurdDao.java using below code
 

public List getAllStudents(int startPageIndex, int recordsPerPage) {
List students = new ArrayList();
int range = startPageIndex+recordsPerPage;
String query="SELECT * from (Select M.*, Rownum R From STUDENT M) where r >" + startPageIndex +" and r <= "+range;
try
{
	Statement stmt = dbConnection.createStatement();
	ResultSet rs = stmt.executeQuery(query);
	while (rs.next())
	{
		Student student = new Student();
		student.setStudentId(rs.getInt("STUDENTID"));
		student.setName(rs.getString("NAME"));
		student.setDepartment(rs.getString("DEPARTMENT"));
		student.setEmailId(rs.getString("EMAIL"));
		students.add(student);
	}
}
catch (SQLException e)
{
	System.err.println(e.getMessage());
}
return students;
}

 
This function returns only a subset of records according to the page offset (jtStartIndex and jtPageSize).
 
Now on running the application, with the above changes, the final demo looks as shown below:
 

 

 

Reference

 
jTable official website
AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in