Create an API for DataTables with Spring Boot
Tags: springboot, datatables
Published: 2024-07-02 02:27:50

---
title: Create an API for DataTables with Spring Boot
published: true
date: 2024-06-28 03:51:00 UTC
tags: SpringBoot,DataTables
canonical_url: https://blog.stackpuz.com/create-an-api-for-datatables-with-spring-boot/
---

![Spring Boot API for DataTables](https://blog.stackpuz.com/media/posts/6/cover.jpg)

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](https://github.com/StackPuz/Example-Datatables-Spring-Boot-3/blob/main/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
<?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.

```java
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.

```java
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".

```java
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.

```java
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.

```java
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.<product></product>
- 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>` <product></product>object.  

### index.html 

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

```html
<!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.

![test page](https://blog.stackpuz.com/media/posts/6/default.png)

## 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.

![page size test](https://blog.stackpuz.com/media/posts/6/page-size.png)

### Sorting test 

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

![sorting test](https://blog.stackpuz.com/media/posts/6/sort.png)

### Search test 

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

![search test](https://blog.stackpuz.com/media/posts/6/search.png)

## 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](https://github.com/stackpuz/Example-DataTables-Spring-Boot-3)

Create a CRUD Web App in Minutes: [https://stackpuz.com](https://stackpuz.com)