Simple Guide to Use DataTable with Spring Boot – Example With Source Code

In this article, you will learn how to use Datatable with Sprint Boot for Serverside processing with example. Complete Source Code and supporting SQL is provided on GitHub to download.

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

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

Use DataTable with Spring Boot:

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 it 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. The below class holds all that data and we also have a method that processes HTTP request object and prepares the specification for each of the columns.

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 the above specification class for each column.

You can see that constructor is calling another method that will process the HTTP request and prepare a 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 the above you can see when a 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 are prepared which will have information about total records, filtered records, and actual data. This information is then converted to JSON and passed back to the 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 JavaScript put together. In HTML we need to put below js and CSS files in order to use datatable functionality

And after we will initialize the datatable as shown below

Demo of Datatable

We are done with the code and config. Let’s 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 the 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 properties to connect with Oracle. (I know its pain, but I had to do it as I wanted to keep the 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 the 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 the domain object. You can refer them in source code.

Conclusion

I hope this example of datatable with Spring Boot will help you. You can download the source code and supporting SQL from our GitHub

GitHub-Mark-64px

84 Comments
  1. Umar
    April 20, 2020 | Reply
  2. SUSHMITA MHATRE
    February 10, 2020 | Reply
  3. Teddy Heriyanto
    December 10, 2019 | Reply
  4. mok
    October 20, 2019 | Reply
  5. Arockia
    July 22, 2019 | Reply
    • Pavan
      July 23, 2019 | Reply
  6. Evghenii
    May 22, 2019 | Reply
    • Shilpa
      May 23, 2019 | Reply
  7. April 11, 2019 | Reply
    • Pavan
      April 14, 2019 | Reply
  8. Trịnh Đại
    February 20, 2019 | Reply
  9. Sumit Badaya
    January 28, 2019 | Reply
    • Pavan
      February 9, 2019 | Reply
  10. Simon
    September 17, 2018 | Reply
    • Pavan
      September 18, 2018 | Reply
  11. Simon
    September 11, 2018 | Reply
    • Pavan
      September 12, 2018 | Reply
  12. August 30, 2018 | Reply
  13. Rahul
    August 28, 2018 | Reply
  14. 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 |
        • Teddy Heriyanto
          December 9, 2019 | Reply
  15. M
    August 23, 2018 | Reply
    • Pavan
      August 23, 2018 | Reply
  16. Yeswanth
    August 23, 2018 | Reply
  17. 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 |
  18. Prasad
    July 26, 2018 | Reply
    • Pavan
      July 27, 2018 | Reply
      • Kumar
        July 28, 2018 | Reply
        • Pavan
          July 30, 2018 | Reply
  19. Eshiett Oto-obong
    June 26, 2018 | Reply
  20. Wallace
    June 13, 2018 | Reply
    • Pavan
      June 14, 2018 | Reply
  21. Antoine
    June 6, 2018 | Reply
    • Pavan
      June 8, 2018 | Reply
  22. jahurul islam
    May 21, 2018 | Reply
  23. Antoine
    April 18, 2018 | Reply
    • Pavan
      April 18, 2018 | Reply
      • Antoine
        June 6, 2018 | Reply
        • Pavan
          August 7, 2018 | Reply
  24. Yoyo
    March 14, 2018 | Reply
    • August 30, 2018 | Reply
  25. Marc Collin
    March 12, 2018 | Reply
    • Pavan
      March 13, 2018 | Reply
  26. Vinodh
    February 16, 2018 | Reply
    • Pavan
      February 16, 2018 | Reply
      • Prasad
        July 27, 2018 | Reply
        • Pavan
          July 27, 2018 | Reply
          • Prasad
            July 27, 2018 |
  27. Sandeep
    February 12, 2018 | Reply
    • Pavan
      February 14, 2018 | Reply
  28. Ajinkya Shinde
    January 30, 2018 | Reply
  29. November 15, 2017 | Reply
    • Pavan
      November 16, 2017 | Reply
  30. Rajeshkumar
    November 15, 2017 | Reply
  31. Frank
    November 8, 2017 | Reply
    • Frank
      November 8, 2017 | Reply
      • Pavan
        November 8, 2017 | Reply
  32. Rizwana
    October 26, 2017 | Reply
    • Pavan
      October 26, 2017 | Reply
      • Rizwana
        October 27, 2017 | Reply
  33. August 8, 2017 | Reply
    • Pavan
      August 9, 2017 | Reply
      • August 9, 2017 | Reply
        • Yoyo
          March 14, 2018 | Reply
        • Kumar
          July 30, 2018 | Reply
  34. techsavvyprog
    July 15, 2017 | Reply
  35. Leandro S.
    May 11, 2017 | Reply
  36. Leandro S.
    May 11, 2017 | Reply

Add a Comment

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