Spring Data JPA 中的分页（限制和偏移）

Pagination in Spring Data JPA (limit and offset)

提问by chinesewhiteboi

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

然而这不是我想要的。PageRequest 指定页面和大小（页号和页面大小）。现在指定大小正是我想要的，但是，我不想指定起始页#，我希望用户能够指定起始记录/索引。我想要类似的东西

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!

特别是 setFirstResult() 和 setMaxResult() 方法。但是我不能使用这种方法，因为我想使用 Employee 存储库接口。（或者通过 entityManager 定义查询实际上更好吗？）无论如何，有没有办法在不使用 entityManager 的情况下指定偏移量？提前致谢！

回答by codingmonkey

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, new Sort(Sort.Direction.ASC,"id"));
    }

    @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();
    }
}

回答by Tobias Michelchen

You can do that by creating your own Pageable.

您可以通过创建自己的 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;
}

}

回答by Sebastian

Maybe the answer is kind of late, but I thought about the same thing. Compute the current page based on offset and limit. Well, it is not exactly the same because it "assumes" that the offset is a multiple of the limit, but maybe your application is suitable for this.

也许答案有点晚，但我也想过同样的事情。根据偏移量和限制计算当前页面。好吧，它并不完全相同，因为它“假设”偏移量是限制的倍数，但也许您的应用程序适合于此。

@Override
public List<Employee> findByName(String name, int offset, int limit) {
    // limit != 0 ;)
    int page = offset / limit;
    return repository.findByName(name, new PageRequest(page, limit));
}

I would suggest a change of the architecture. Change your controller or whatever calls the service to initially give you page and limit if possible.

我建议改变架构。如果可能，请更改您的控制器或任何调用服务的内容，以最初为您提供页面和限制。

回答by supercobra

Here you go:

干得好：

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    @Query(value="SELECT e FROM Employee e WHERE e.name LIKE ?1 ORDER BY e.id offset ?2 limit ?3", nativeQuery = true)
    public List<Employee> findByNameAndMore(String name, int offset, int limit);
}

回答by membersound

You probably can't to this with spring data jpa. If the offset is very small, you might just remove the top X statements from the query after retrieval.

您可能无法使用 spring 数据 jpa。如果偏移量非常小，您可以在检索后从查询中删除前 X 条语句。

Otherwise, you could define the page size to be the offset and start at page+1.

否则，您可以将页面大小定义为偏移量并从 page+1 开始。

回答by Abhi

Try that:

试试看：

public interface ContactRepository extends JpaRepository<Contact, Long> 
{
    @Query(value = "Select c.* from contacts c where c.username is not null order by c.id asc limit ?1,  ?2 ", nativeQuery = true)         
    List<Contact> findContacts(int offset, int limit);        
}

回答by Raj Kumar Mishra

Suppose you are filtering and soring and paging at same time Below @Query will help you

假设您同时在过滤、排序和分页下面@Query 将帮助您

    @Query(value = "SELECT * FROM table  WHERE firstname= ?1  or lastname= ?2 or age= ?3 or city= ?4 or "
        + " ORDER BY date DESC OFFSET ?8 ROWS FETCH NEXT ?9 ROWS ONLY" , nativeQuery = true)
List<JobVacancy> filterJobVacancyByParams(final String firstname, final String lastname,
        final String age, final float city,int offset, int limit);