Querydsl - 分页功能 [英] Querydsl - paging functionality

问题描述

似乎在 Jpa QueryDsl 中我可以使用如下分页:

返回新的 JPAQueryFactory(getEntityManager()).selectFrom(实体).where(where_clause).orderBy(order_by_clause).offset(pageNumber * 20).limit(20).fetchResults();

问题是:

• 这是最佳方法吗?fetchResults 是否仅从 DB 加载 20 个元素并进行计数查询以获取有关 db 中实体总数的信息?
• 或者也许有像 .page(2).limit(20) 这样的选项?

是的，我知道 Spring-Data 已经为 QueryDsl 提供了分页和接口，但是由于 Spring-Data 不支持复杂的order by"子句，我无法使用它:(

解决方案

这里太晚了，但有人可能会觉得它有帮助.

<块引用>

这是最佳方法吗?fetchResults 是否仅从 DB 加载 20 个元素并进行计数查询以获取有关 db 中实体总数的信息?

是的 - 它将发出 2 个查询.一个用于使用 where 子句进行计数，另一个用于获取结果.当您有兴趣了解符合条件(where 子句)的记录数量以及根据页面大小和偏移量获取数据时，这是需要的.使用 .fetchResults() 时，您应该使用以下方法来获取总计数和返回的行数，如下所示.

QueryResults结果 = query.fetchResults();int totalCount = result.getTotal();列表<元组>行 = result.getResults();

<块引用>

或者也许有一些像 .page(2).limit(20) 这样的选项?

是的 - 如果您只想获取偏移量和页面大小的结果，您应该使用

List行 = query.limit(20).offset(2*20).fetch();

fetch() 方法将只发出 1 个查询以获取受指定页面大小和偏移量限制"的结果.

It seems that in Jpa QueryDsl I can use paging like:

return new JPAQueryFactory(getEntityManager())
    .selectFrom(entity)
    .where(where_clause)
    .orderBy(order_by_clause)
    .offset(pageNumber * 20)
    .limit(20)
    .fetchResults();

Questions are:

• Is it optimal approach? Does fetchResults load only 20 elements from DB and make count query to get information about total number of entities which are in db?
• Or maybe there is some option like .page(2).limit(20)?

Yes I know that Spring-Data has already Paging and interface for QueryDsl but because of the complicated "order by" clause which is not supported by Spring-Data I cannot use it :(

解决方案

Too late here but someone may find it helpful.

Is it optimal approach? Does fetchResults load only 20 elements from DB and make count query to get information about total number of entities which are in db?

Yes - it will issue 2 queries. One for count with the where clause and the other for fetching results. This is desired when you are interested in knowing the number of records which meets the criteria (where clause) along with fetching the data as per the page size and offset. With using .fetchResults(), you should use the following methods to get the total count and the rows returned as following.

QueryResults<Tuple> result = query.fetchResults();
int totalCount = result.getTotal();
List<Tuple> rows = result.getResults();

Or maybe there is some option like .page(2).limit(20)?

Yes - if you only want to fetch the results for offset and page size, you should use

List<Tuple> rows = query.limit(20).offset(2*20).fetch();

fetch() method will only issue 1 query to fetch the results 'limited' by the page size and offset specified.

这篇关于Querydsl - 分页功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！