Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pagination in Spring Data JPA (limit and offset)

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!

like image 291
chinesewhiteboi Avatar asked Jul 29 '14 04:07

chinesewhiteboi


People also ask

How do I limit records in JPA repository?

JPA Setup In our repository method, we use the EntityManager to create a Query on which we call the setMaxResults() method. This call to Query#setMaxResults will eventually result in the limit statement appended to the generated SQL: select passenger0_.id as id1_15_, passenger0_.

Does JPQL support the limit keyword for pagination?

JPQL doesn't support the LIMIT keyword.

How does JPA pagination work?

The simplest way to implement pagination is to use the Java Query Language – create a query and configure it via setMaxResults and setFirstResult: Query query = entityManager. createQuery("From Foo"); int pageNumber = 1; int pageSize = 10; query. setFirstResult((pageNumber-1) * pageSize); query.


2 Answers

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();     } } 
like image 79
codingmonkey Avatar answered Sep 21 '22 23:09

codingmonkey


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; }  } 
like image 21
Tobias Michelchen Avatar answered Sep 18 '22 23:09

Tobias Michelchen