Pages Navigation Menu

Coding is much easier than you think

Pagination in Servlet and JSP using jQuery jTable plugin

 
pagination in j2ee
 
This is the 3rd article on jQuery jTable plugin that describes on how to implement pagination feature to do server side paging and here I have not explained about how to setup jTable plugin in java web application. So If you have not read my previous articles “Setting up JQuery jTable plugin in Java web application” and “Ajax based curd operation in Java web application 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 Java web application and in second article explains on how to implement ajax based curd operation. This article will assume that the code for the integration of jQuery JTable plugin is implemented, and only the code required for implementing pagination in Java web application using jTable will be explained here.
 

Setup

 
Now download the sample application of my previous tutorial and import the project in eclipse. Now follow the steps in previous tutorial to create table in database and make sure you have atleast 5 records in the table.
 
Now on running this application, you will see a table displaying records without pagination such as the one shown below.
 
Integrating-jQuery-jTable-plugin-with-Struts2-framework
Now the following steps are needed to be followed to enable paging feature in jTable
 

Changes 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 is shown below
 





Pagination in Java Web Applications using jTable plugin













Pagination in Java Web Applications jTable

 

Changes from the server’s perspective: Servlet

 
If paging is enabled in jsp then 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 return only the part of resultset for each page, So handle this there are two changes that has to be done in the server side .
 
1. In order to return only a subset of records according to the page offset (jtStartIndex and jtPageSize), sql query used in CRUDDao should be modified with query below,
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 ,

 
Changes made in CRUDDao at getAllStudents function
 

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;
	System.out.println(query);
	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;
}

 
2. As mentioned above, jTable need TotalRecordCount to be present in the json response, For which add the following function in CRUDDao which returns total Record Count value present in database.
 

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;
}

 

Changes made in Controller

 
The following changes where made in the logic inside the if loop -> 'if(action.equals("list"))'

HashMap JSONROOT = new HashMap();

if (action.equals("list")) {
	try {
	// Fetch Data from User Table
	int startPageIndex = Integer.parseInt(request.getParameter("jtStartIndex"));
	int recordsPerPage = Integer.parseInt(request.getParameter("jtPageSize"));

	// Fetch Data from Student Table
	studentList = dao.getAllStudents(startPageIndex, recordsPerPage);
	// Get Total Record Count for Pagination
	int userCount = dao.getStudentCount();

	// Return in the format required by jTable plugin
	JSONROOT.put("Result", "OK");
	JSONROOT.put("Records", studentList);
	JSONROOT.put("TotalRecordCount", userCount);

	// Convert Java Object to Json
	String jsonArray = gson.toJson(JSONROOT);

	response.getWriter().print(jsonArray);
	} catch (Exception ex) {
		JSONROOT.put("Result", "ERROR");
		JSONROOT.put("Message", ex.getMessage());
		String error = gson.toJson(JSONROOT);
		response.getWriter().print(error);
	}
}

 
Now on running the application, with the above changes, the final demo looks as shown below:
 
Pagination-Cover
 
download
 

Reference

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

8 Comments

  1. Hi to all. I’ve downloaded and tried to execute both CRUD Operations and Pagination code. But i m getting same error i.e. “An error occured while communicating to the server”. In discussion some body said servlet is not triggered via jtable jquery java script. But i did not get it. Can anyone please solve this. Thanks in advance..!

    • Please download the source code from this link, https://drive.google.com/file/d/0B3C5CUeU01xUZlhrenBZU2tELVU/view and import into your workspace. Make sure that you add gson library in your project path.

      • Hi,I used login application and it is validating form if userName and password is blank.

        Now I am sending userName and password like this

        http://localhost:8080/LoginApp/loginAction.action?userName=jagannath&password=123 then also logged in successfully instead of filling login.jsp form page. In this case user should not logged in. How can avoid it using struts2.

        • For that reason you need to maintain the ‘session’.

  2. The Pagination in the table displays like this 1 2 NaN NaN 3.
    Say for example If I have three pages, between two and three. NaN displays. How to resolve this ?

    • Hi Mohaideen Jamil, how solve Pagination in the table displays like this 1 2 NaN NaN 3.???
      Help …

  3. this error:

    Every derived table must have its own alias in MYSQL workbench SELECT * from (Select M.*, Rownum R From pessoa M) where r > 0 and r <= 10

    • Hi, I have used oracle table to demonstrate above scenario, in case of mysql , you have to use query as below

      [code]
      select * from STUDENT limit ,
      [/code]