Spring Data JPA中的分页(限制和偏移)

kpbwa7wx  于 2022-12-23  发布在  Spring
关注(0)|答案(8)|浏览(543)

我希望用户能够在我的查询方法中指定限制（返回数量的大小）和偏移（返回的第一条记录/返回的索引）。
以下是我的类，没有任何分页功能。我的实体：

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

现在，我尝试提供支持偏移和限制的分页功能。我的实体类保持不变。
我的“new”存储库接受一个可分页参数：

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

我的“新”服务接口接受两个附加参数：

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指定页面和大小（页码和页面大小）。现在指定大小正是我想要的，但是，我不想指定起始页码，我希望用户能够指定起始记录/索引。我想要类似于

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指定偏移量的方法？

jpa

8条答案

按热度按时间
8hhllhi2

8hhllhi21#

下面的代码应该做到这一点。我正在使用我自己的项目和测试的大多数情况下。
用法：

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();
    }
}
赞(0）分享  回复(0）举报  2022-12-23
vwhgwdsa

vwhgwdsa2#

您可以通过创建自己的Pageable来实现这一点。
试试这个基本的例子。对我来说很好用：

import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

public class ChunkRequest implements Pageable {

    private final int offset;
    private final int limit;

    // this attribute can be let out if you don't need it
    private Sort sort;

    public ChunkRequest(int offset, int limit, Sort sort) {
        if (offset < 0)
            throw new IllegalArgumentException("Offset must not be less than zero!");

        if (limit < 0)
            throw new IllegalArgumentException("Limit must not be less than zero!");

        this.offset = offset;
        this.limit = limit;

        if (sort != null) {
            this.sort = sort;
        }
    }

    public ChunkRequest(int offset, int limit) {
        this(offset, limit, null);
    }

    @Override
    public int getPageNumber() { return 0; }

    @Override
    public int getPageSize() { return limit; }

    @Override
    public long getOffset() { return offset; }

    @Override
    public Sort getSort() { return this.sort; }

    @Override
    public Pageable next() { return null; }

    @Override
    public Pageable previousOrFirst() { return this; }

    @Override
    public Pageable first() { return this; }

    @Override
    public Pageable withPage(int pageNumber) { return null; }

    @Override
    public boolean hasPrevious() { return false; }
}
赞(0）分享  回复(0）举报  2022-12-23
rekjcdws

rekjcdws3#

给你：

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);
}
赞(0）分享  回复(0）举报  2022-12-23
0tdrvxhp

0tdrvxhp4#

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

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

我会建议改变架构。改变你的控制器或任何调用服务的东西，如果可能的话，最初给予你页面和限制。

赞(0）分享  回复(0）举报  2022-12-23
oewdyzsn

oewdyzsn5#

对于spring data jpa，您可能无法做到这一点。如果偏移量非常小，您可能只需要在检索后从查询中删除前X条语句。
否则，您可以将页面大小定义为偏移量并从page+1开始。

赞(0）分享  回复(0）举报  2022-12-23
h5qlskok

h5qlskok6#

使用long offset和**Sort.by（）**修改good @codingmonkey awnser。

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.Pageable;
import org.springframework.data.domain.Sort;
import java.io.Serializable;

public class OffsetBasedPageRequest implements Pageable, Serializable {

    private static final long serialVersionUID = -25822477129613575L;

    private final int limit;
    private final long 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(long 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(long offset, int limit, Sort.Direction direction, String... properties) {
        this(offset, limit, Sort.by(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 Math.toIntExact(offset / limit);
    }

    @Override
    public int getPageSize() {
        return limit;
    }

    @Override
    public long 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();
    }
}
赞(0）分享  回复(0）举报  2022-12-23
iqjalb3h

iqjalb3h7#

试试看：

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);        
}
赞(0）分享  回复(0）举报  2022-12-23
k4emjkb1

k4emjkb18#

假设您正在同时进行过滤、排序和分页，下面的@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);
赞(0）分享  回复(0）举报  2022-12-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com