In this article, I am going to show you an easy example of integrating DataTables using SpringBoot and its data JPA. Also, I have added this example on GitHub.
Tools used:
SpringBoot 2.3.9.RELEASE (data-jpa, web, tymeleaf)
H2 Database
DataTables 1.11.3
jQuery 3.6.0
Integration:
Firstly, I have created a SpringBoot project using IntelliJ IDEA. Then, in the maven dependencies, I have added spring-boot-starter-data-jpa, web, thymeleaf. Also, I have added Lombok for reducing boilerplate getters and setters, and H2 as our database.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</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-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>Then, in my application.properties file, I have used these properties,
server.port=80
spring.datasource.url=jdbc:h2:mem:datatable_db
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.mvc.view.prefix=classpath:/templates/
spring.jpa.show-sql=trueNow, I will add the DataTables library, which contains a CSS, and a JS file. Also, the DataTables requires jQuery.
To fulfill the minimum requirement, I will add these jquery.dataTables.min.css, jquery.dataTables.min.js, and jquery-3.6.0.min.js files under the resources/static/assets folder.
Also, I have added the sort direction indicator logos in the resources/static/assets/images folder as shown in the screenshot below.
Now, I will integrate the controller part where we will map our view.
@Controller
public class PersonController {
@Autowired
private PersonService personService;
@GetMapping("/")
public String getView() {
return "index.html";
}
}In this SpringBoot project, I have used only one page (index.html) to show and interact with our DataTables.
In the index.hmtl file, I have imported our required CSS and JS files. Then, I have specified datatable-rest as the id of our table and mentioned our table headers.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Datatable Example</title>
<link rel="stylesheet" href="/assets/DataTables-1.11.3/jquery.dataTables.min.css">
</head>
<body>
<h1 class="h1">Hello</h1>
<table id=" " class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>NAME</th>
<th>ADDRESS</th>
</tr>
</thead>
</table>
<script src="/assets/jQuery-3.6.0/jquery-3.6.0.min.js"></script>
<script src="/assets/DataTables-1.11.3/jquery.dataTables.min.js"></script>
<script>
$('#datatable-rest').DataTable({
"serverSide": true,
"bSort": true,
"ajax": {
"url": "/dt",
"type": "POST",
"dataType": "json",
"contentType": "application/json; charset=utf-8",
"data":
function (d) {
// this to see what is being sent to the server
console.log(d);
return JSON.stringify(d);
},
"dataFilter": function (response) {
// this to see what is being received from the server
console.log(response);
return response;
},
"error": function (xhr, error, code) {
alert(error + ' : ' + code);
}
},
"columns": [
{"data": "id"},
{"data": "name"},
{"data": "address"}
]
});
</script>
</body>
</html>A brief explanation is required to understand how the DataTables will communicate with SpringBoot.
Here, I am calling the DataTable() function over our table to initiate our implementation. We are setting ServerSide as true because this is a server-side processing implementation. I am also setting the bSort as true to enable sorting of every column in ascending or descending order. Then, I am initiating an ajax POST request on URL /dt
The next step will help us understand what is being requested by the DataTables library and how we need to process it.
I have logged the data parameter value with console.log(d) to see what is being sent as a request to the server.
Now, you can run the SpringBoot application.
To see what is being sent to the server as an ajax request, open your browser developer tool’s console, and refresh the page.
The log will output something like this,
Now, we will design our classes to receive and process this request.
@Setter
@Getter
@NoArgsConstructor
public class PagingRequest {
private int start;
private int length;
private List<Order> order;
private List<Column> columns;
private Search search;
}@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class Order {
private Integer column;
private Direction dir;
}@Setter
@Getter
@NoArgsConstructor
public class Column {
private String data;
private String name;
private Boolean searchable;
private Boolean orderable;
private Search search;
}@Setter
@Getter
@NoArgsConstructor
public class Search {
private String value;
private String regexp;
}public enum Direction {
asc,
desc
}The DataTables has a specific format to receive the data in the frontend and show it accordingly.
I am using the Page class to specify the format.
@Getter
@Setter
@NoArgsConstructor
public class Page<T> {
private List<T> data;
private int recordsFiltered;
private int recordsTotal;
public Page(List<T> data, int recordsFiltered, int recordsTotal) {
this.data = data;
this.recordsFiltered = recordsFiltered;
this.recordsTotal = recordsTotal;
}
}In spring-data-jpa, there is already a Page class (org.springframework.data.domain.Page) to manipulate pagination.
However, we have created a Page<T> class as per the DataTable requirement.
To function, we need to map them together.
I have created a PagingUtil class to do so.
public class PagingUtil {
public static String toSearchTerm(PagingRequest pagingRequest) {
return pagingRequest.getSearch().getValue();
}
public static Pageable toPageable(PagingRequest pagingRequest) {
int page = pagingRequest.getStart();
int size = pagingRequest.getLength();
if (page > 0) {
page = page / size;
}
Sort sort = toSort(pagingRequest);
return PageRequest.of(page, size, sort);
}
public static Sort toSort(PagingRequest pagingRequest) {
int columnNo = pagingRequest.getOrder().get(0).getColumn();
String columnName = pagingRequest.getColumns().get(columnNo).getData();
Direction direction = pagingRequest.getOrder().get(0).getDir();
Sort sort;
if (direction == Direction.asc) {
sort = Sort.by(columnName).ascending();
} else {
sort = Sort.by(columnName).descending();
}
return sort;
}
}Let us create our demo data to play with our DataTables.
To do so, I have put a data.sql file in the resources folder. Where I have set some SQL queries for creating an example table and inserted some data into it.
Every time we run our SpringBoot application, this file runs which provides us our demo data.
DROP TABLE IF EXISTS PERSON_TABLE;
CREATE TABLE PERSON_TABLE (
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(250) DEFAULT NULL,
ADDRESS VARCHAR(250) DEFAULT NULL
);
INSERT INTO PERSON_TABLE (NAME, ADDRESS) VALUES
('John', 'NY'),
('Karen', 'DC'),
('Rose', 'KR'),
('Smith', 'IN'),
('Shelby', 'NY'),
('Rickey', 'ND'),
('Stone', 'AU'),
('Emilia', 'KKK'),
('Daytona', 'OP'),
('Kickstar', 'GC'),
('Json', 'SR'),
('Moma', 'IN');Now, we will create our model and repository classes for our data.
@Data
@Entity
@Table(name = "PERSON_TABLE")
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private int id;
@Column(name = "NAME")
private String name;
@Column(name = "ADDRESS")
private String address;
}public interface PersonRepository extends JpaRepository<Person, Integer> {
Page<Person> findAll(Pageable pageable);
@Query("SELECT p FROM Person p " +
"WHERE (LOWER(p.id) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
"OR LOWER(p.name) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
"OR LOWER(p.address) LIKE LOWER(CONCAT('%',:searchTerm, '%'))) ")
Page<Person> getPagedData(@Param("searchTerm") String searchTerm, Pageable pageable);
@Query(value = "SELECT * FROM PERSON_TABLE " +
"WHERE (LOWER(ID) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
"OR LOWER(NAME) LIKE LOWER(CONCAT('%', :searchTerm, '%')) " +
"OR LOWER(ADDRESS) LIKE LOWER(CONCAT('%', :searchTerm, '%')))",
nativeQuery = true)
Page<Person> getPagedData_native(@Param("searchTerm") String searchTerm, Pageable pageable);
}In our PersonRepository class, I have shown demo implementation of paginating using spring-data-jpa method, JPQL, and native query. You can choose any of the methods.
I am choosing the JPQL method to demonstrate in this tutorial so lets move to our service interface and class.
public interface PersonService {
Page<Person> getPaginatedDatatable(PagingRequest pagingRequest);
}@Service
public class PersonServiceImpl implements PersonService {
@Autowired
private PersonRepository personRepository;
public Page<Person> getPaginatedDatatable(PagingRequest pagingRequest) {
//Mapping DataTables PagingRequest to spring's Pageable
Pageable pageable = PagingUtil.toPageable(pagingRequest);
String searchTerm = PagingUtil.toSearchTerm(pagingRequest);
org.springframework.data.domain.Page<Person> filteredRecordsPage =
personRepository.getPagedData_jpql(searchTerm, pageable);
int availableFilteredRecords = (int)filteredRecordsPage.getTotalElements();
int totalRecords = (int)personRepository.count();
return new Page<>(filteredRecordsPage.getContent(), availableFilteredRecords, totalRecords);
}
}Now, let us move to our controller class, and add our REST API method.
@PostMapping("/dt")
@ResponseBody
public Page<Person> getPaginatedDatatable(@RequestBody PagingRequest pagingRequest) {
return personService.getPaginatedDatatable(pagingRequest);
}Here we are, receiving the ajax payload as a POST request, and returning the paged data.
Finally, we can run the SpringBoot application and browse to localhost to see the DataTable fully functional.
It should look like this
Also, the major functions (sort, search, pagination) are implemented!
Congratulations! You have implemented the datatable with SpringBoot successfully!
Cleaning:
In our index.html page,
"dataFilter": function (response) {
// this to see what is being received from the server
console.log(response);
return response;
},This function is used to show you paged response in json format.
Which you can view from your browsers developer tool’s console, that looks something like this,
As this is used for learning purposes, you can remove the dataFilter parameter and the datatable will work just fine.
Cheers!