java - 具有窗口函数的 Sql 到 JOOQ

Question

我有这个查询:

SELECT id, url, name, email, count(*) OVER() AS overallCount 
FROM images 
WHERE email = ? 
OFFSET ? 
LIMIT ?

我想将其翻译成JOOQ，我该如何实现？

Answer 1

假设这个静态导入

import static org.jooq.impl.DSL.*;

然后

String email = ...
int offset = ...
int limit = ...

using(configuration)
  .select(
    IMAGES.ID,
    IMAGES.URL,
    IMAGES.EMAIL,
    count().over().as("overallCount"))
  .from(IMAGES)
  .where(IMAGES.EMAIL.eq(email))
  .offset(offset)
  .limit(limit)
  .fetch();