Oracle转PostgreSQL：Spring Data JPA分页查询是否生成LIMIT子句？

问题 #

原本有一段基于Oracle的HQL查询，通过rownum = 1获取单条profileId：

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null and rownum = 1")
String getProfileId(@Param("id") String id);

现在迁移到PostgreSQL，找到的解决方案是：

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null")
String getProfileId(@Param("id") String id, Pageable pageable);

// 调用方式
repository.getProfileId("somId", PageRequest.of(0, 1));

不清楚该写法会先查询HrDepartment表的所有数据再在Java代码中取第一条，还是会生成带LIMIT 1子句的SQL，在数据库端仅查询单条数据？

回答 #

这种写法会在数据库端生成带LIMIT 1子句的SQL，仅查询单条数据，不会先查所有数据再在Java端处理。

Spring Data JPA在处理带有Pageable参数的自定义查询时，会自动将分页参数转换为对应数据库的语法：当你传入PageRequest.of(0, 1)时，JPA会给PostgreSQL的查询语句追加LIMIT 1 OFFSET 0的条件，数据库执行时只会返回符合条件的第一条数据，之后再将结果返回给Java代码。

另外还有更简洁的写法可以替代这种带Pageable的方式，比如直接在HQL中使用PostgreSQL的语法：

@Query("select profileId from HrDepartment where profileId = :id and profileId is not null limit 1")
String getProfileId(@Param("id") String id);

这种写法不需要额外传入Pageable参数，同样会在数据库端限制返回条数，效果和带Pageable的方式一致。

内容的提问来源于stack exchange，提问作者Peter Penzov