jqxGrid with Server-side Sorting, Paging and Filtering Using JSP

In this help topic you will learn how to bind a jqxGrid to a MySQL database using JSP (JavaServer Pages) and implement server-side sorting, paging and filtering.

Important: before proceeding, please make sure you have followed the instructions of the tutorial Configure MySQL, Eclipse and Tomcat for Use with jQWidgets.

1. Connect to the Database and Retrieve the Grid Data

To populate the grid, we need a JSP file that connects to the Northwind database and retieves data from it depending on the sorting and filtering applied to the grid, the current page it is on and the page size.

Create a new JSP by right-clicking the project's WebContent folder, then choosing New → JSP File. Name the file 'select-filtered-data.jsp' and place it in a subfolder named 'jsp'.

Import the necessary classes in the beginning of the JSP:

Finally, add a scriptlet to the JSP that does the following:

1. Gets the request parameters (based on the current sorting, paging and filtering).
2. Makes a database connection.
3. Gets the total count of the filtered records (needed for determining the page count).
4. Selects the necessary (filtered, ordered and limited) data from the database in a ResultSet.
5. Converts the ResultSet to a JSON array and passes the total count of the filtered records as a field of the first array member.
6. Prints (returns) the JSON array.

2. Create a Page with a jqxGrid

Create a new HTML page by right-clicking the project's WebContent folder, then choosing New → HTML File. Here is the code of the page in our example:

Through jqxDataAdapter, the grid is populated with the data retrieved from the database by select-filtered-data.jsp. In the formatData callback of data adapter, we can create new parameters to be passed to the server-side by adding them as properties to the data object. These parameters may be used in select-filtered-data.jsp. In the source callback beforeprocessing, we get the source.totalrecords from the data passed to the first element of the data array. The grid's filtering and sorting are synchronised with the database in the filter and sort callbacks.

To run the page, right-click it and select Run As → Run on Server. In the window that appears, select Tomcat v8.0 Server at localhost and click Finish.

You can find an online demo based on this tutorial here: Grid Server Sorting, Paging and Filtering.