在 SQL 中对结果进行分页时,我们使用标准 SQL或供应商特定的版本,例如。例如:OFFSET .. FETCH
LIMIT .. OFFSET
SELECT first_name, last_name
FROM actor
ORDER BY actor_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
|
与往常一样,我们将Sakila 数据库用于此示例。
这是相当简单的。它将为我们提供 N 页中的第 2 页,页面大小为 10。但是我们如何计算这些值呢?我们怎么知道我们在第 2 页?我们怎么知道页数?我们可以在没有额外往返的情况下计算这一点,例如计算演员总数:N
-- Yuck, a second round-trip!
SELECT COUNT (*)
FROM actor
|
我们可以使用单个SQL查询和窗口函数来做到这一点,但是在我解释如何做到这一点之前,请考虑阅读这篇文章,了解为什么OFFSET分页对您的性能不利。
如果您仍然确信分页是您需要的,而不是键集分页,让我们看看如何使用SQL计算上述元数据。OFFSET
我们需要什么元数据?
我们通常需要使用以下元数据进行分页:OFFSET
TOTAL_ROWS
:未分页时的记录总数CURRENT_PAGE
:我们所在的当前页面MAX_PAGE_SIZE
:最大页面大小ACTUAL_PAGE_SIZE
:实际页面大小(在最后一页上时)ROW
:返回行的实际偏移量LAST_PAGE
:我们是否在最后一页
最大页面大小是我们为查询设置的,因此不必计算。其他一切都需要计算。以下是在单个查询中执行此操作的方法
SELECT
t.first_name,
t.last_name,
-- Calculate some meta data
COUNT (*) OVER () AS actual_page_size,
MAX (row) OVER () = total_rows AS last_page,
-- Metadata from the subquery
total_rows,
row,
((row - 1) / :max_page_size) + 1 AS current_page
FROM (
SELECT
u.*,
-- Calculate some meta data, repeating the ORDER BY from
-- the original query
COUNT (*) OVER () AS total_rows,
-- Order by some deterministic criteria, e.g. a primary key
ROW_NUMBER () OVER ( ORDER BY u.actor_id) AS row
-- Original query with all the predicates, joins, as a derived table
FROM (
SELECT *
FROM actor
) AS u
-- Ordering and pagination done here, where :offset is
-- The maximum row value of the previous page + 1
ORDER BY u.actor_id
OFFSET :offset ROWS
FETCH NEXT :max_page_size ROWS ONLY
) AS t
ORDER BY t.actor_id
|
就是这样。令人 印象 深刻?不要害怕,我会一步一步地引导你完成这些事情。如果您对 SQL 语法感到困惑,请考虑本文解释 SQL 操作的逻辑顺序,对于我们的示例:
FROM
(派生表的递归排序)WHERE
(示例省略)WINDOW
计算SELECT
(投影)ORDER BY
OFFSET .. FETCH
注释我们的查询,将操作逻辑排序为 1.1、1.2、2.1、2.2、2.3、2.4、2.5、3.1、3.2、3.3、3.4:
-- 3.3
SELECT
t.first_name,
t.last_name,
-- 3.2
COUNT (*) OVER () AS actual_page_size,
MAX (row) OVER () = total_rows AS last_page,
-- 3.3
total_rows,
row,
((row - 1) / :max_page_size) + 1 AS current_page
-- 3.1
FROM (
-- 2.3
SELECT
u.*,
-- 2.2
COUNT (*) OVER () AS total_rows,
ROW_NUMBER () OVER ( ORDER BY u.actor_id) AS row
-- 2.1
FROM (
-- 1.2
SELECT *
-- 1.1
FROM actor
) AS u
-- 2.4
ORDER BY u.actor_id
-- 2.5
OFFSET :offset ROWS
FETCH NEXT :max_page_size ROWS ONLY
) AS t
-- 3.4
ORDER BY t.actor_id
|
分步说明
首先,原始查询包装为调用的派生表。您几乎可以使用此原始查询执行任何操作,只需应用几个转换:SELECT * FROM actor
u
- 1.1、1.2、2.1:您需要投影(子句)原始查询投影的列以及所需的列。因为我在最外面的查询中投影了正确的内容,并且原始查询中没有子句,所以我方便地投影了。或者,我可以投影,(因为这是在原始查询中投影的),并且(因为这就是我们)。
SELECT
ORDER BY
DISTINCT
*
FIRST_NAME
LAST_NAME
ACTOR_ID
ORDER BY
- 2.2:在该派生表上,我们现在能够计算一些元数据,包括asandas。窗口函数有一个空的窗口规范,这意味着它计算由,,,子句产生的所有行,即from在我们的特定示例中。没有第二次往返!对所有行进行排序,并根据该顺序为它们分配唯一的行号。确保顺序条件是确定性的,否则如果排序产生关系,您可能会得到随机结果。
u
TOTAL_ROWS
COUNT(*) OVER ()
ROW
ROW_NUMBER () OVER (ORDER BY t.actor_id)
COUNT(*) OVER ()
OVER ()
FROM
WHERE
GROUP BY
HAVING
u
ROW_NUMBER () OVER (ORDER BY u.actor_id)
u
u.actor_id
- 2.3:窗口函数是隐式计算的,因为它们位于此派生表的投影中。我们还将再次方便地投影所有内容,因为最外层的查询是显式投影列的查询。
u.*
- 2.4:原始排序已移至此处,因为无法保证如果我们订购了内容,则不会保持排序。但是我们需要排序在之后立即计算
u
OFFSET .. FETCH
- 2.5:这是我们分页的地方。对应于我们之前遇到的最大值。我们从下一页开始,页面大小为,我们使用下一页。请记住,虽然索引基于SQL,但基于SQL。
OFFSET
ROW
0
15
15
1
OFFSET
0
- 3.1:以上所有内容再次包装在一个派生表中,以便对其进行进一步计算,即:
- 3.2:我们可以再次计算,计算由 ,,, 子句(即 from)产生的总行数在我们的特定示例中。这一次,行数不能超过,因为这就是里面的(或)子句所说的。但它也可以更少,所以这就是我们用来计算的。最后,我们比较看看我们是否在最后一页,这意味着当前页面中产生的最大值与总行数相比。计算值的另一种方法是如果,即
COUNT(*) OVER ()
FROM
WHERE
GROUP BY
HAVING
t
MAX_PAGE_SIZE
FETCH
LIMIT
t
ACTUAL_PAGE_SIZE
MAX(row) OVER () = total_rows
row
t
LAST_PAGE
ACTUAL_PAGE_SIZE < MAX_PAGE_SIZE
COUNT(*) OVER () < :MAX_PAGE_SIZE
- 3.3:除了原始列的通常投影(我们现在不再投影!),我们正在做一些最终计算,包括除法以获得页码。您可以计算更多的东西,例如获取值。
FIRST_NAME
LAST_NAME
*
ROW / TOTAL_ROWS
TOTAL_ROWS / MAX_PAGE_SIZE
TOTAL_PAGES
- 3.4:最后,我们必须再次重申,不要让任何人告诉你。在 SQL 中,如果不这样做,则排序是未定义的。当然,对于优化器来说,在没有任何充分理由的情况下重新排序是愚蠢的。我们已经在 2.4 中对子查询的内容进行了排序,但不能保证这种排序是稳定的。只需向查询添加或导致哈希连接或其他一些随机运算符,排序就会中断。所以,总是如果订购对您很重要。
ORDER BY t.actor_id
ORDER BY
DISTINCT
UNION
JOIN
ORDER BY
我们完成了!
如何在jOOQ中做到这一点?
这是jOOQ真正闪耀的用例,因为所有这些都与动态SQL有关。您的实际业务逻辑包含在深度嵌套表中。其他一切都是“表示逻辑”,它在SQL中实现的原因非常明显:提高性能。u
而且,由于您希望仅在自己的某个库中实现所有这些操作一次,因此不必在每个查询上都玩此游戏,因此可以使这种查询动态化。该实用程序将如下所示:
// Assuming as always the usual static imports, including:
// import static org.jooq.impl.DSL.*;
// import com.generated.code.Tables.*;
static Select<?> paginate(
DSLContext ctx,
Select<?> original,
Field<?>[] sort,
int limit,
int offset
) {
Table<?> u = original.asTable( "u" );
Field<Integer> totalRows = count().over().as( "total_rows" );
Field<Integer> row = rowNumber().over().orderBy(u.fields(sort))
.as( "row" );
Table<?> t = ctx
.select(u.asterisk())
.select(totalRows, row)
.from(u)
.orderBy(u.fields(sort))
.limit(limit)
.offset(offset)
.asTable( "t" );
Select<?> result = ctx
.select(t.fields(original.getSelect().toArray(Field[]:: new )))
.select(
count().over().as( "actual_page_size" ),
field(max(t.field(row)).over().eq(t.field(totalRows)))
.as( "last_page" ),
t.field(totalRows),
t.field(row),
t.field(row).minus(inline( 1 )).div(limit).plus(inline( 1 ))
.as( "current_page" ))
.from(t)
.orderBy(t.fields(sort));
// System.out.println(result);
return result;
}
|
注意到要调试的 println 了吗?它将再次打印类似于我们原始查询的内容(但默认情况下,您还将在调试日志输出中看到jOOQ):
select
t.ACTOR_ID,
t.FIRST_NAME,
t.LAST_NAME,
count (*) over () as actual_page_size,
( max (t.row) over () = t.total_rows) as last_page,
t.total_rows,
t.row,
((t.row / 15) + 1) as current_page
from (
select
u.*,
count (*) over () as total_rows,
row_number() over ( order by u.ACTOR_ID) as row
from (
select
ACTOR.ACTOR_ID,
ACTOR.FIRST_NAME,
ACTOR.LAST_NAME
from ACTOR
) as u
order by u.ACTOR_ID
offset 30 rows
fetch next 15 rows only
) as t
order by t.ACTOR_ID
|
以下是您如何调用该实用程序:
System.out.println(
paginate(
ctx,
ctx.select(ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME)
.from(ACTOR),
new Field[] { ACTOR.ACTOR_ID },
15 ,
30
).fetch()
);
|
请注意,您可以将任意 SQL 片段插入到该实用程序中并对其进行分页。无论复杂性如何(包括连接、其他窗口函数、分组、递归等等),jOOQ 都会为您提供帮助,现在将为您分页。
上述结果为:
+--------+----------+---------+----------------+---------+----------+----+------------+
|ACTOR_ID|FIRST_NAME|LAST_NAME|actual_page_size|last_page|total_rows| row|current_page|
+--------+----------+---------+----------------+---------+----------+----+------------+
| 31|SISSY |SOBIESKI | 15|false | 200| 31| 3|
| 32|TIM |HACKMAN | 15|false | 200| 32| 3|
| 33|MILLA |PECK | 15|false | 200| 33| 3|
| 34|AUDREY |OLIVIER | 15|false | 200| 34| 3|
| 35|JUDY |DEAN | 15|false | 200| 35| 3|
| 36|BURT |DUKAKIS | 15|false | 200| 36| 3|
| 37|VAL |BOLGER | 15|false | 200| 37| 3|
| 38|TOM |MCKELLEN | 15|false | 200| 38| 3|
| 39|GOLDIE |BRODY | 15|false | 200| 39| 3|
| 40|JOHNNY |CAGE | 15|false | 200| 40| 3|
| 41|JODIE |DEGENERES| 15|false | 200| 41| 3|
| 42|TOM |MIRANDA | 15|false | 200| 42| 3|
| 43|KIRK |JOVOVICH | 15|false | 200| 43| 3|
| 44|NICK |STALLONE | 15|false | 200| 44| 3|
| 45|REESE |KILMER | 15|false | 200| 45| 3|
+--------+----------+---------+----------------+---------+----------+----+------------+
或者,在最后一页上,偏移量为 195
+--------+----------+---------+----------------+---------+----------+----+------------+
|ACTOR_ID|FIRST_NAME|LAST_NAME|actual_page_size|last_page|total_rows| row|current_page|
+--------+----------+---------+----------------+---------+----------+----+------------+
| 196|BELA |WALKEN | 5|true | 200| 196| 14|
| 197|REESE |WEST | 5|true | 200| 197| 14|
| 198|MARY |KEITEL | 5|true | 200| 198| 14|
| 199|JULIA |FAWCETT | 5|true | 200| 199| 14|
| 200|THORA |TEMPLE | 5|true | 200| 200| 14|
+--------+----------+---------+----------------+---------+----------+----+------------+
结论
jOOQ是关于动态SQL的。几乎没有任何jOOQ不支持的SQL功能。例如,这包括窗口函数,但也要确保动态SQL在大量SQL方言上工作,而不考虑小的语法细节。
如本文所示,您可以构建自己的库,以从其他 SQL 构建基块构造可重用的 SQL 元素,以动态创建单查询分页元数据计算,而无需执行额外的数据库往返。OFFSET