Spring Data JPA apply sorting, pagination along with a where clause

Question

I am currently Using Spring JPA and utilizing the Sorting and Pagination as described here -

How to query data via Spring data JPA by sort and pageable both out of box？

Now my question is how do I apply a 'where' clause to this?

E.g.

where PRODUCT_NAME='abc';

Answer 1

Just create a query method in your repository interface for this.

Take a look in the docs, here.

Add a query method with paging and where clause.

Page<User> findByLastname(String lastname, Pageable pageable);

So you can find User by a property "lastname" and configure Paging settings.

Answer 2

The easiest way I could find is to use QueryDSL or Specifications. I prefer QueryDSL.

Answer 3

To fetch Spring Data JPA apply sorting, pagination along with a where clause check bellow codes

1. Entity class

   @Entity
   @Table(name = "users")
   Public Class User{
       private String name;
       private Integer id;
       private String email;
       // add required setter getter//
   }
   

2. Repository class

   @Repository("userRepository")
   @Transactional`
       public interface UserRepository extends JpaRepository<User, Integer>{
   
       }
   

3. Service layer to pass where clause using Example object and Pagination+Sorting together using Pagable and Sort Class. We will implement Pageable in controller to inject Sorting logic then will pass that Pagable object to service Layer.

    @Service
    Public class UserService{
       public Page<User> getUserListPaginated(`EXAMPLE<User>` searchTerm, Pageable pageable) {
           return userRepo.findAll(searchTerm,pageable);
       }
   }
   

Example<T> does not take the attribute if its value is null. for that I used Integer id in Entity class as I can set it's value null for first call

4. Now Controller Class

   @Controller
   Public class UserController{
   
       @Autowired UserService userService;
       @GetMapping("/users")
       public String users(Model model,@RequestParam(value="page",defaultValue = "0") Integer  pageNumber, @SortDefault(sort = "name", direction = Sort.Direction.ASC) Sort sort){
           Pageable pageable = PageRequest.of(pageNumber,10, sort);
           Example<User> searchTerm = Example.of(new User());
           Page<User> userList = userService.getUserListPaginated(searchTerm, pageable);
   
   
       }
   }