Pagination in Servlet and JSP using jQuery jTable plugin
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.
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 ListgetAllStudents(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"))'
HashMapJSONROOT = 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:
Reference
jTable official website
AJAX based CRUD tables using ASP.NET MVC 3 and jTable jQuery plug-in
Wikipedia : JSON
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’.
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 …
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]