How to use DataTable with Spring Boot for Server Side Processing

If you have worked on web development projects that deals with thousands of records getting displayed on page then you must have worked with or heard of Datatables. It is a JQuery plugin which is highly flexible and adds interactive controls to any HTML table.

These tables serve a great purpose when it comes to display huge information in tabular form. When you have a task to work with huge data its not advised to pull all the data to client side first and then use datatables to display.

In this scenario, we need to do the processing at the server side and send only relevant records back for display. In this article, we will see an example of this functionality. You will get a wrapper around this functionality so the integration becomes easy.

Software used for this example

  • Java 1.8
  • Spring Boot 1.5.1.RELEASE
  • JQuery
  • DataTable 1.10.12
  • MySQL
  • Eclipse IDE

When we use server side option of datatable we need to prepare data as per the datatable specification and send in JSON format. In this example you will find many such wrapper classes that will help us to prepare pagination, sorting, filtering data.

Project Structure

SpringBoot - Datatable Project Structure
When we specify data for any column on datatable we need to specify the data, name, searchable etc. fields. Below class holds all that data and we also have a method which processes HTTP request object and prepares the specification for each of the column.

After this we have another main class that holds data for complete datatable interaction including draw, search, start, end of data. It will also hold the list of above specification class for each column.

You can see that constructor is calling another method which will process the http request and prepare complete datatable request. At the same time it has methods to prepare supporting information required for sorting, filtering and pagination is prepared.

The Controller

As this is a web project, we will add a controller that will display our data. We are using the Thymeleaf along with Spring Boot to server our web content.

In above you can see when request comes to this controller, datatable request and pagination criteria that we need to pass to our data layer is prepared. After that using the pagination criteria which may contain sorting, filtering information a query is prepared and sent to execution for data. Below are both the queries base query and the one which gets prepared using the pagination information.

Once results are back, datatable results is prepared which will have information about total records, filtered records and actual data. This information is then converted to JSON and passed back to client. Below is our User model, the data that get fetched and passed.

The View

Now we will see how we have our html and java script put together. In html we need to put below js and css files in order to use datatable functionality

And after the we will initialize the datatable as shown below

Demo of Datatable

We are done with the code and config. Lets see it working.

Default Rendering

Datatable - Demo 1

FilteringDatatable - Demo 2

SortingDatatable - Demo 3

Update:

Many people asked and trying to work this example with Oracle database. I have added Oracle specific code to the same project. As this project is not equipped to connect to two database at the same time, I had to comment MySQL propertites to connect with Oracle. (I know its pain, but I had to do it as I wanted to keep focus of this article on Datatable and Oracle Integration.)

Change properties file to point to your Oracle database.

Refer file datatable.user.sql that is available in the source repository to create the table and sample data.

I have added couple of views and controllers to separea Oracle and MySQL flow.

The main change is how paginated query is built for Oracle.

The above function takes pagination criteria into account and prepares the query for Oracle. You can not use limit clause directly in Oracle, you have to rely on ROWNUM to limit the results. In this function, we calculate the page number and size and accordingly set them in the query.

I have done some minor changes to domain object. You can refer them in source code.

Conclusion

I hope this example and walkthrough will help you. You can download the source code and supporting sql from our GitHub

GitHub-Mark-64px

70 Comments
  1. Simon
    September 17, 2018 | Reply
    • Pavan
      September 18, 2018 | Reply
  2. Simon
    September 11, 2018 | Reply
    • Pavan
      September 12, 2018 | Reply
  3. August 30, 2018 | Reply
  4. Rahul
    August 28, 2018 | Reply
  5. August 27, 2018 | Reply
    • Pavan
      August 27, 2018 | Reply
      • August 28, 2018 | Reply
      • August 28, 2018 | Reply
        • Pavan
          August 28, 2018 | Reply
          • August 28, 2018 |
  6. M
    August 23, 2018 | Reply
    • Pavan
      August 23, 2018 | Reply
  7. Yeswanth
    August 23, 2018 | Reply
  8. Prasad
    July 28, 2018 | Reply
    • Pavan
      July 30, 2018 | Reply
      • Kumar
        July 30, 2018 | Reply
        • Pavan
          July 30, 2018 | Reply
          • Kumar
            July 30, 2018 |
          • Pavan
            July 30, 2018 |
          • Kumar
            July 30, 2018 |
          • Pavan
            August 7, 2018 |
          • Kumar
            July 31, 2018 |
          • August 7, 2018 |
          • Pavan
            August 7, 2018 |
  9. Prasad
    July 26, 2018 | Reply
    • Pavan
      July 27, 2018 | Reply
      • Kumar
        July 28, 2018 | Reply
        • Pavan
          July 30, 2018 | Reply
  10. Eshiett Oto-obong
    June 26, 2018 | Reply
  11. Wallace
    June 13, 2018 | Reply
    • Pavan
      June 14, 2018 | Reply
  12. Antoine
    June 6, 2018 | Reply
    • Pavan
      June 8, 2018 | Reply
  13. jahurul islam
    May 21, 2018 | Reply
  14. Antoine
    April 18, 2018 | Reply
    • Pavan
      April 18, 2018 | Reply
      • Antoine
        June 6, 2018 | Reply
        • Pavan
          August 7, 2018 | Reply
  15. Yoyo
    March 14, 2018 | Reply
    • August 30, 2018 | Reply
  16. Marc Collin
    March 12, 2018 | Reply
    • Pavan
      March 13, 2018 | Reply
  17. Vinodh
    February 16, 2018 | Reply
    • Pavan
      February 16, 2018 | Reply
      • Prasad
        July 27, 2018 | Reply
        • Pavan
          July 27, 2018 | Reply
          • Prasad
            July 27, 2018 |
  18. Sandeep
    February 12, 2018 | Reply
    • Pavan
      February 14, 2018 | Reply
  19. Ajinkya Shinde
    January 30, 2018 | Reply
  20. November 15, 2017 | Reply
    • Pavan
      November 16, 2017 | Reply
  21. Rajeshkumar
    November 15, 2017 | Reply
  22. Frank
    November 8, 2017 | Reply
    • Frank
      November 8, 2017 | Reply
      • Pavan
        November 8, 2017 | Reply
  23. Rizwana
    October 26, 2017 | Reply
    • Pavan
      October 26, 2017 | Reply
      • Rizwana
        October 27, 2017 | Reply
  24. August 8, 2017 | Reply
    • Pavan
      August 9, 2017 | Reply
      • August 9, 2017 | Reply
        • Yoyo
          March 14, 2018 | Reply
        • Kumar
          July 30, 2018 | Reply
  25. techsavvyprog
    July 15, 2017 | Reply
  26. Leandro S.
    May 11, 2017 | Reply
  27. Leandro S.
    May 11, 2017 | Reply

Add a Comment

Your email address will not be published. Required fields are marked *