Spring Data JPA 中的分页(限制和偏移) [英] 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
}
我的仓库:
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);
}
我的服务接口:
public interface EmployeeService {
public List<Employee> findByName(String name);
}
我的服务实现:
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);
}
我的新"服务实现:
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);
}
}
然而这不是我想要的.PageRequest 指定页面和大小(页号和页面大小).现在指定大小正是我想要的，但是，我不想指定起始页#，我希望用户能够指定起始记录/索引.我想要类似的东西
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();
}
特别是 setFirstResult() 和 setMaxResult() 方法.但是我不能使用这种方法，因为我想使用 Employee 存储库接口.(或者通过 entityManager 定义查询实际上更好吗?)无论如何，有没有办法在不使用 entityManager 的情况下指定偏移量?提前致谢！
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!
推荐答案
下面的代码应该可以做到.我在我自己的项目中使用并测试了大多数情况.
Below code should do it. I am using in my own project and tested for most cases.
用法:
Pageable pageable = new OffsetBasedPageRequest(offset, limit);
return this.dataServices.findAllInclusive(pageable);
和源代码:
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();
}
}
这篇关于Spring Data JPA 中的分页(限制和偏移)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
