Spring Data Pageable with custom query in Spring Boot

In this tutorial, I will show you how to implement Spring Boot Pagination using Spring Data Pageable with custom query.

Associations:
– JPA One To One example with Hibernate in Spring Boot
– JPA One To Many example with Hibernate and Spring Boot
– JPA Many to Many example with Hibernate in Spring Boot

Contents

Spring Data Pageable
Spring Data Pageable with Custom Query
Conclusion
Further Reading
Source Code

Spring Data Pageable

The Pageable interface contains the information about the requested page such as the size, the number of the page, or sort information with Sort object.

public interface Pageable {
  int getPageNumber();
  int getPageSize();
  long getOffset();
  Sort getSort();
  Pageable next();
  Pageable previousOrFirst();
  Pageable first();
  boolean hasPrevious();
  ...
}

So when we want to make paging and sorting (with or without filter) in the results, we just add Pageable to the definition of the method as a parameter.

This is how we create Pageable objects using PageRequest class which implements Pageable interface:

Pageable paging = PageRequest.of(page, size, sort);

page: zero-based page index, must NOT be negative.
size: number of items in a page to be returned, must be greater than 0.
sort: the Sort object.

Spring Data also supports many useful Query Creation from method names (Derived Query) that we’re gonna use to filter result in this example such as:

Page<Tutorial> findByPublished(boolean published, Pageable pageable);
Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

For more details, please visit:
Spring JPA Derived query example in Spring Boot

Let’s notice the Pageable parameter in Repository methods above. Spring Data infrastructure will recognizes this parameter automatically to apply pagination and sorting to database.

So, how about using Custom Query with Spring Data Pageable?
Let’s explore it.

Spring Data Pageable with Custom Query

Create Entity

In model package, we define Tutorial class.

Tutorial has six fields: id, title, level, description, published, createdAt.

model/Tutorial.java

package com.bezkoder.spring.query.model;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "tutorials")
public class Tutorial {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  private String title;

  private String description;
  
  private int level;

  private boolean published;
  
  @Temporal(TemporalType.TIMESTAMP)
  private Date createdAt;

  public Tutorial() {

  }

  public Tutorial(String title, String description, int level, boolean published, Date createdAt) {
    this.title = title;
    this.description = description;
    this.level = level;
    this.published = published;
    this.createdAt = createdAt;
  }

  // getters and setters
}

– @Entity annotation indicates that the class is a persistent Java class.
– @Table annotation provides the table that maps this entity.

– @Id annotation is for the primary key.
– @GeneratedValue annotation is used to define generation strategy for the primary key.

– @Temporal annotation converts back and forth between timestamp and java.util.Date or time-stamp into time. For example, @Temporal(TemporalType.DATE) drops the time value and only preserves the date.

@Temporal(TemporalType.DATE)
private Date createdAt;

Define Repository with Custom Query methods

Let’s create a repository to interact with database.
In repository package, create TutorialRepository interface that extend JpaRepository.

repository/TutorialRepository.java

package com.bezkoder.spring.query.repository;

import com.bezkoder.spring.query.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {

}

In this interface, we will write JPA Custom Query (with where condition) to fetch data from database.
I will show you how to use JPQL Query and Native Query with Pageable.

Assume that we’ve already have tutorials table like this:

Custom Query with Pageable using JPQL

Spring Data JPA Query example using Pageable class for Pagination (with sorting and filtering):

@Query("SELECT t FROM Tutorial t")
Page<Tutorial> findAllWithPagination(Pageable pageable);

@Query("SELECT t FROM Tutorial t WHERE t.published=?1")
Page<Tutorial> findByPublishedWithPagination(boolean isPublished, Pageable pageable);
  
@Query("SELECT t FROM Tutorial t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))")
Page<Tutorial> findByTitleWithPagination(String title, Pageable pageable);

Result:

int page = 0;
int size = 3;

Pageable pageable = PageRequest.of(page, size);
tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
*/

pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

pageable = PageRequest.of(page, size);   
tutorials = tutorialRepository.findByTitleWithPagination("ring", pageable).getContent();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findByPublishedWithPagination(false, pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

Custom Query with Pageable using Native Query

Spring Data JPA Native Query example using Pageable class for Pagination (with sorting and filtering):

@Query(value = "SELECT * FROM tutorials", nativeQuery = true)
Page<Tutorial> findAllWithPagination(Pageable pageable);

@Query(value = "SELECT * FROM tutorials t WHERE t.published=?1", nativeQuery = true)
Page<Tutorial> findByPublished(boolean isPublished, Pageable pageable);

@Query(value = "SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))", nativeQuery = true)
Page<Tutorial> findByTitleLike(String title, Pageable pageable);

Result:

int page = 0;
int size = 3;

Pageable pageable = PageRequest.of(page, size);
tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=3, title=Hibernate, description=Tut#3 Description, level=3, published=true, createdAt=2022-04-26 00:00:00.0]
*/

pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findAllWithPagination(pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=5, title=Spring Data JPA, description=Tut#5 Description, level=3, published=true, createdAt=2022-05-19 00:00:00.0]
*/

pageable = PageRequest.of(page, size);
tutorials = tutorialRepository.findByTitleLike("ring", pageable).getContent();
show(tutorials);
/*
Tutorial [id=1, title=Spring Data, description=Tut#1 Description, level=3, published=true, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=2, title=Java Spring, description=Tut#2 Description, level=1, published=false, createdAt=2022-03-11 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

pageable = PageRequest.of(page, size, Sort.by("level").descending());
tutorials = tutorialRepository.findByPublished(false, pageable).getContent();
show(tutorials);
/*
Tutorial [id=7, title=Spring Security, description=Tut#7 Description, level=5, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=6, title=Spring Batch, description=Tut#6 Description, level=4, published=false, createdAt=2022-05-19 00:00:00.0]
Tutorial [id=4, title=Spring Boot, description=Tut#4 Description, level=2, published=false, createdAt=2022-04-26 00:00:00.0]
*/

Conclusion

Today we’ve known how to use Spring Data JPA Pageable with custom query in Spring Boot example using JPQL and Native Query.

You can continue to write Pagination and Sorting Rest APIs with:
Spring Boot Pagination and Sorting example

If you want to write Unit Test for the JPA Repository:
Spring Boot Unit Test for JPA Repository with @DataJpaTest

You can also know:
– how to deploy this Spring Boot App on AWS (for free) with this tutorial.
– dockerize with Docker Compose: Spring Boot and MySQL example
– or: Docker Compose: Spring Boot and Postgres example
– way to upload an Excel file and store the data in MySQL database with this post
– upload CSV file and store the data in MySQL with this post.

Happy learning! See you again.