StackPuz

Posted on Jul 2 • Originally published at blog.stackpuz.com on Jun 28

Create an API for DataTables with Spring Boot

#springboot #datatables

DataTables is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will demonstrate you how to create a Spring Boot API to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables, you need to understand the information that DataTables will send to the API through the query string.

draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false

draw the request ID that is used to synchronize between the client and server.
columns[x][data] the column's field name that we define on the client-side.
order[0] the sorting information.
start the start index of the record. We do not use it, because Spring Boot pagination uses a page index instead. We will write some JavaScript to generate this page index later.
length the length per page (page size).
search[value] the search value information.

The DataTables expected data will require these information.

draw DataTables sends this ID to us, and we just send it back.
recordsTotal Total records number before filtering.
recordsFiltered Total records number after filtering.
data The records data.

Prerequisites

JAVA 17
Maven
MySQL

Setup project

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ pom.xml
└─ src
   └─ main
      ├─ java
      │  └─ com
      │     └─ stackpuz
      │        └─ example
      │           ├─ App.java
      │           ├─ controller
      │           │  └─ ProductController.java
      │           ├─ dto
      │           │  └─ DataTables.java
      │           ├─ entity
      │           │  └─ Product.java
      │           └─ repository
      │              └─ ProductRepository.java
      └─ resources
         ├─ application.properties
         └─ static
            └─ index.html

Project files

pom.xml

This file contains the configuration and dependencies of the Maven project.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.stackpuz</groupId>
    <artifactId>example-datatables</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>example-datatables</name>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.10</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
     </dependencies>
</project>

application.properties

This file contains the database configuration.

spring.datasource.url = jdbc:mysql://localhost/example
spring.datasource.username = root
spring.datasource.password = 
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect

App.java

This file is the main entry point for the Spring Boot application.

package com.stackpuz.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

ProductRepository.java

This file defines the product repository by utilizing the JpaRepository class, which has the basic CRUD operations and the pagination feature, so we can use it to implement the pagination with less effort.

package com.stackpuz.example.repository;

import com.stackpuz.example.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Integer> {

    Page<Product> findByNameContains(Pageable pageable, String name);

}

findByNameContains is described by their name, this method is used to get the product paginated data filter by its name.

Product.java

This file defines the product entity that maps to our database table named "Product".

package com.stackpuz.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;

@Entity
@Getter
@Setter
@NoArgsConstructor
public class Product {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private String name;
    private BigDecimal price;
}

We use the lombok library features to reduce the amount of code written for our entity by using @Getter @Setter @NoArgsConstructor annotations.

DataTables.java

This file defines the DTO (Data Transfer Object) for DataTables.

package com.stackpuz.example.dto;

import java.util.List;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class DataTables<T> {
    private int draw;
    private long recordsTotal;
    private long recordsFiltered;
    private List<T> data;

    public DataTables(int draw, long recordsTotal, long recordsFiltered, List<T> data) {
        this.draw = draw;
        this.recordsTotal = recordsTotal;
        this.recordsFiltered = recordsFiltered;
        this.data = data;
    }
}

DataTables<T> meaning this is the Generic Class that can be use for any type of the Entity, to return DataTables data to the client.

ProductController.java

This file is used to handle incoming requests and produce the paginated data for the client.

package com.stackpuz.example.controller;

import java.util.Optional;

import jakarta.servlet.http.HttpServletRequest;

import com.stackpuz.example.entity.Product;
import com.stackpuz.example.repository.ProductRepository;
import com.stackpuz.example.dto.DataTables;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.web.bind.annotation.*;

@RestController
public class ProductController {

    @Autowired
    private ProductRepository repository;

    @Autowired
    private HttpServletRequest request;

    @GetMapping("/api/products")
    public DataTables<Product> getProducts(@RequestParam("page") Optional<Integer> pageParam, @RequestParam("length") Optional<Integer> sizeParam, @RequestParam("order[0][dir]") Optional<String> directionParam, @RequestParam("search[value]") String search, @RequestParam("draw") int draw) {
        int page = pageParam.orElse(1) - 1;
        int size = sizeParam.orElse(10);
        String order = request.getParameter("order[0][column]") != null ? request.getParameter("columns[" + request.getParameter("order[0][column]") + "][data]") : "Id";
        String direction = directionParam.orElse("asc");
        PageRequest pageRequest = PageRequest.of(page, size, Sort.by(Direction.fromString(direction), order));
        long recordsTotal = repository.count();
        Page<Product> pageProduct = (search.isEmpty() ? repository.findAll(pageRequest) : repository.findByNameContains(pageRequest, search));
        return new DataTables<Product>(draw, recordsTotal, pageProduct.getTotalElements(), pageProduct.getContent());
    }
}

We utilize the query string to get page, size, order, direction and create the PageRequest object by using PageRequest.of()
We pass the PageRequest object as a parameter to the repository.findAll() and repository.findByNameContains() methods to get the Page<Product> result.
Because Spring Boot page index is starts at zero, so we use pageParam.orElse(1) - 1 for this purpose.
We return all DataTables required information including: draw, recordsTotal, recordsFiltered, data as a DataTables<Product> object.

index.html

This file will be used to setup the DataTables HTML and JavaScript to work with our API.

<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        var dataTable = new DataTable('#table', {
            ajax: {
                url: '/api/products',
                data: {
                    page: () => (dataTable && dataTable.page() + 1) || 1
                }
            },
            processing: true,
            serverSide: true,
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'price' }
            ]
        })
    </script>
</body>
</html>

processing: true show a loading indicator when making the request.
serverSide: true makes the request to the server (API) for all operations.
because Spring Boot can't utilize the start query string, so we need to write the JavaScript to generate the page query string instead.

Run project

mvn spring-boot:run

Open the web browser and goto http://localhost:8080

You will find this test page.

Testing

Page size test

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

Sorting test

Click on the header of the first column. You will see that the id column will be sorted in descending order.

Search test

Enter "no" in the search text-box, and you will see the filtered result data.

Conclusion

In this article, you have learned how to create a Spring Boot API to work with the DataTables. Understand all the DataTables parameters sent to the API and utilize them to produce the appropriate data and send it back. You also learn how to setup the DataTables on the client-side using HTML and JavaScript. I hope this article will help you integrate DataTables into your project.

Source code: https://github.com/stackpuz/Example-DataTables-Spring-Boot-3

Create a CRUD Web App in Minutes: https://stackpuz.com

DEV Community