如何将含子查询LIMIT/OFFSET的SQL转换为JPQL？

核心问题分析 #

原SQL的逻辑是：先对users表按id正序取第4-6条（OFFSET 3，LIMIT 3），再将这3条结果按id倒序返回。JPQL本身不支持在子查询中直接使用LIMIT和OFFSET，且setMaxResults()和setFirstResult()只会作用于最外层查询，无法直接复刻原SQL的子查询分页逻辑。

可行的JPQL实现方案 #

因为原SQL的最终目的是先分页获取指定范围的数据，再反转排序，可以通过以下步骤实现：

1. 先使用JPQL配合分页API获取正序的目标数据范围
2. 在内存中反转结果列表，达到和原SQL子查询后倒序的效果

代码示例：

// 假设已获取EntityManager实例
int limit = 3;
int offset = 3;

// 1. 按id正序查询指定分页范围的用户
String jpql = "SELECT u FROM User u ORDER BY u.id ASC";
TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
query.setMaxResults(limit);
query.setFirstResult(offset);

List<User> users = query.getResultList();

// 2. 内存中反转列表，实现原SQL的倒序效果
Collections.reverse(users);

为什么需要在子查询中使用LIMIT和OFFSET #

原SQL的写法是为了减少排序的数据量，提升性能：如果直接在最外层执行ORDER BY id DESC LIMIT 3 OFFSET 3，数据库需要先对全表按id倒序排序，再跳过3条取3条；而原SQL的写法是先按正序筛选出小范围数据（仅6条中的后3条），再对这3条数据倒序，排序的数据量大幅减少，在数据量较大时性能差异会非常明显。

内容的提问来源于stack exchange，提问作者Sergey Zolotarev