Pagination in Spring Data JPA (limit and offset)

Question

I want the user to be able to specify the limit (the size of the amount returned) and offset (the first record returned / index returned) in my query method.

Here are my classes without any paging capabilities. My entity:

@Entity public Employee {     @Id     @GeneratedValue(strategy=GenerationType.AUTO)     private int id;      @Column(name="NAME")     private String name;      //getters and setters } 

My repository:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {      @Query("SELECT e FROM Employee e WHERE e.name LIKE :name ORDER BY e.id")     public List<Employee> findByName(@Param("name") String name); } 

My service interface:

public interface EmployeeService {      public List<Employee> findByName(String name); } 

My service implementation:

public class EmployeeServiceImpl {      @Resource     EmployeeRepository repository;      @Override     public List<Employee> findByName(String name) {         return repository.findByName(name);     } } 

Now here is my attempt at providing paging capabilities that support offset and limit. My entity class remains the same.

My "new" repository takes in a pageable parameter:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {      @Query("SELECT e FROM Employee e WHERE e.name LIKE :name ORDER BY e.id")     public List<Employee> findByName(@Param("name") String name, Pageable pageable); } 

My "new" service interface takes in two additional parameters:

public interface EmployeeService {      public List<Employee> findByName(String name, int offset, int limit); } 

My "new" service implementation:

public class EmployeeServiceImpl {      @Resource     EmployeeRepository repository;      @Override     public List<Employee> findByName(String name, int offset, int limit) {         return repository.findByName(name, new PageRequest(offset, limit);     } } 

This however isn't what i want. PageRequest specifies the page and size (page # and the size of the page). Now specifying the size is exactly what I want, however, I don't want to specify the starting page #, I want the user to be able to specify the starting record / index. I want something similar to

public List<Employee> findByName(String name, int offset, int limit) {     TypedQuery<Employee> query = entityManager.createQuery("SELECT e FROM Employee e WHERE e.name LIKE :name ORDER BY e.id", Employee.class);     query.setFirstResult(offset);     query.setMaxResults(limit);     return query.getResultList(); } 

Specifically the setFirstResult() and setMaxResult() methods. But I can't use this method because I want to use the Employee repository interface. (Or is it actually better to define queries through the entityManager?) Anyways, is there a way to specify the offset without using the entityManager? Thanks in advance!

Answer 1

Below code should do it. I am using in my own project and tested for most cases.

usage:

   Pageable pageable = new OffsetBasedPageRequest(offset, limit);    return this.dataServices.findAllInclusive(pageable); 

and the source code:

import org.apache.commons.lang3.builder.EqualsBuilder; import org.apache.commons.lang3.builder.HashCodeBuilder; import org.apache.commons.lang3.builder.ToStringBuilder; import org.springframework.data.domain.AbstractPageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort;  import java.io.Serializable;  /** * Created by Ergin **/ public class OffsetBasedPageRequest implements Pageable, Serializable {      private static final long serialVersionUID = -25822477129613575L;      private int limit;     private int offset;     private final Sort sort;      /**      * Creates a new {@link OffsetBasedPageRequest} with sort parameters applied.      *      * @param offset zero-based offset.      * @param limit  the size of the elements to be returned.      * @param sort   can be {@literal null}.      */     public OffsetBasedPageRequest(int offset, int limit, Sort sort) {         if (offset < 0) {             throw new IllegalArgumentException("Offset index must not be less than zero!");         }          if (limit < 1) {             throw new IllegalArgumentException("Limit must not be less than one!");         }         this.limit = limit;         this.offset = offset;         this.sort = sort;     }      /**      * Creates a new {@link OffsetBasedPageRequest} with sort parameters applied.      *      * @param offset     zero-based offset.      * @param limit      the size of the elements to be returned.      * @param direction  the direction of the {@link Sort} to be specified, can be {@literal null}.      * @param properties the properties to sort by, must not be {@literal null} or empty.      */     public OffsetBasedPageRequest(int offset, int limit, Sort.Direction direction, String... properties) {         this(offset, limit, new Sort(direction, properties));     }      /**      * Creates a new {@link OffsetBasedPageRequest} with sort parameters applied.      *      * @param offset zero-based offset.      * @param limit  the size of the elements to be returned.      */     public OffsetBasedPageRequest(int offset, int limit) {         this(offset, limit, Sort.unsorted());     }      @Override     public int getPageNumber() {         return offset / limit;     }      @Override     public int getPageSize() {         return limit;     }      @Override     public int getOffset() {         return offset;     }      @Override     public Sort getSort() {         return sort;     }      @Override     public Pageable next() {         return new OffsetBasedPageRequest(getOffset() + getPageSize(), getPageSize(), getSort());     }      public OffsetBasedPageRequest previous() {         return hasPrevious() ? new OffsetBasedPageRequest(getOffset() - getPageSize(), getPageSize(), getSort()) : this;     }       @Override     public Pageable previousOrFirst() {         return hasPrevious() ? previous() : first();     }      @Override     public Pageable first() {         return new OffsetBasedPageRequest(0, getPageSize(), getSort());     }      @Override     public boolean hasPrevious() {         return offset > limit;     }      @Override     public boolean equals(Object o) {         if (this == o) return true;          if (!(o instanceof OffsetBasedPageRequest)) return false;          OffsetBasedPageRequest that = (OffsetBasedPageRequest) o;          return new EqualsBuilder()                 .append(limit, that.limit)                 .append(offset, that.offset)                 .append(sort, that.sort)                 .isEquals();     }      @Override     public int hashCode() {         return new HashCodeBuilder(17, 37)                 .append(limit)                 .append(offset)                 .append(sort)                 .toHashCode();     }      @Override     public String toString() {         return new ToStringBuilder(this)                 .append("limit", limit)                 .append("offset", offset)                 .append("sort", sort)                 .toString();     } } 

Answer 2

You can do that by creating your own Pageable.

Try out this basic sample. Works fine for me:

public class ChunkRequest implements Pageable {  private int limit = 0; private int offset = 0;  public ChunkRequest(int skip, int offset) {     if (skip < 0)         throw new IllegalArgumentException("Skip must not be less than zero!");      if (offset < 0)         throw new IllegalArgumentException("Offset must not be less than zero!");      this.limit = offset;     this.offset = skip; }  @Override public int getPageNumber() {     return 0; }  @Override public int getPageSize() {     return limit; }  @Override public int getOffset() {     return offset; }  @Override public Sort getSort() {     return null; }  @Override public Pageable next() {     return null; }  @Override public Pageable previousOrFirst() {     return this; }  @Override public Pageable first() {     return this; }  @Override public boolean hasPrevious() {     return false; }  }