Here is another small demo app showing some features of Spring Boot for handling a Thymeleaf template using a JSON request, with Spring JdbcTemplate.

Remember, for DataTables server-side processing, sorting/filtering/pagination logic is no longer performed by DataTables. Instead, all that logic is performed by your server-side web application (in this case, by a Spring Boot webapp). This means you only ever need to send one (typically very small) page of data to the DataTable - the page which is currently requested by the user, based on their sorting/filtering/pagination choices.

Demo Comparison

This differs from a previous demo I created:

  • The previous demo always pulled all data from the database, and processed that data in the Spring app, before sending one page of results to the DataTable (i.e. to the browser). That demo used Java records, lambdas and streams for all data sorting, filtering and pagination logic.

  • Here, we only pull the required single page of data from the database into the Spring app. In this case, the demo dynamically builds a SQL prepared statement which selects only the data required for one page of results to be displayed in the DataTable.

Queries Needed

However, there are some important trade-offs between the two approaches which need to be mentioned:

  1. If you really do have a large volume of data, you probably don’t want to fetch it all from the database to your web application, repeatedly. This approach fetches only one page of data (i.e. only the displayed data) from the database; the older approach always fetches all data from the database.

  2. But if you want pagination to function correctly, you need to know how many rows you would have fetched if you were not fetching only one display page of results. This means you need one additional COUNT query. This approach performs both these additional queries.

  3. If you also want DataTables to show the grand total of available records (ignoring all applied filters), you may need an extra COUNT query for that. This is optional - but is used in this demo.

What do these three statistics mean in practical terms?

Consider the DataTables informational message:

Showing 1 to 10 of 42 entries (filtered from 57 total entries)

The numbers 1 to 10 mean you are showing 10 records in your DataTable - and these are the first page of 10 results in the overall list. This is the only set of data which is displayed in your DataTable. For server-side processing, it is the only data sent to the DataTable. This is in contrast to the more typical client-side processing approach, where the DataTable stores the entire data set and performs all filtering/sorting/pagination logic.

The number 42 means you have a total of 42 results which can be displayed (10 rows at a time, with pagination). This is the number of records to be displayed when filtering is taken into account.

The number 57 means that the grand total of records available in the data source is 57 - which is larger than the displayable total of 42 because a filter has been applied.

This last “grand total” number is not needed for pagination - but can help the user to understand the relative size of the currently selected subset of data they are accessing.

If you want your DataTable to display the 1 to 10 of 42 values, not only for the informational message, but also for pagination to work correctly, then you need two SQL queries:

a) a query to count how many records would be selected if you ran a full select statement with the filter applied;

b) a query to select only the relevant subset of those records from (a), for the currently selected page of results.

(c) If you also want to show the value 57, you need a third query to count all avaialble records (with no filtering applied). This is optional - it is not strictly needed for pagination to work.

Only one of these 3 queries actually selects a SQL dataset. The other two only perform SQL counts. Even so, that could be a lot of database processing, if you have a sufficiently large set of data. At least the dataset is guaranteed to be small - only one page of results (10 records, or 100 records, or whatever the DataTable is configured to display).

Performance Still Needs to be Addressed

The main point is: DataTables server-side processing is not an automatic guarantee that you can handle large volumes of data efficiently. It pushes the problem to your server-side application and your database. Both need to be tuned to support your specific data characteristics.

The Code

You can see the code for this new approach here in GitHub.

Specifically, see the SqlQuery class which is where the custom SQL prepared statement is generated.

The three separate queries discussed above are the ones executed here:

1
2
3

long tableCount = demoRepository.countTable();
long filteredCount = demoRepository.countFiltered(sqlQuery);
List<Employee> currentPage = demoRepository.getCurrentPage(sqlQuery);

These are:

  • our overall table count;
  • a count of the filtered data rows;
  • one page of that filtered data.

MySQL Restrictions

This approach assumes the use of MySQL v8. Specifically, it assumes:

  • The database default collation is utf8mb4_0900_ai_ci, where ai means accent-insensitive and ci means case-insensitive. This allows us to filter data without needing explicit logic to handle upper/lower case differences and accented/unaccented differences. In other words, the strings Eglise and église will be treated as equal.

  • The database assumes support for the MySQL limit and offset keywords - which are used here to return one page of results.

If your database does not support these keywords, then your application will behave differently.