CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
Pushkar
Pushkar

Reputation: 7580

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

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';

Upvotes: 18

Views: 36068

Answers (3)

Palash
Palash

Reputation: 159

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

  1. 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);


    }
}

Upvotes: 0

burna
burna

Reputation: 2962

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.

Upvotes: 30

Hari Menon
Hari Menon

Reputation: 35405

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

Upvotes: 4

Related Questions