关于Spring Data Jpa的分页、跨表查询

分页

jqa的query使用原生sql查询（nativeQuery = true）的时候不支持排序的，需要在sql里面加上：ORDER BY ?#{#pageable}
Spring Data Jpa本地查询（带分页方式）

Example

@Query(value = "select u.* from user u INNER JOIN project_user pu " +
        "ON pu.project_id=?1 AND pu.user_id=u.user_id ORDER BY ?#{#pageable}",
        countQuery = "select count(*) from user u INNER JOIN project_user pu " +
                "ON pu.project_id=?1 AND pu.user_id=u.user_id",
        nativeQuery = true)
Page<User> findByProjectId(String projectId, Pageable pageable);

注意：
countQuery中不要写成：

“select count(u.*) from user u INNER JOIN project_user pu ON pu.project_id=?1 AND pu.user_id=u.user_id”

count(u.*)会报错，应该写为count(*)

About other

ORDER BY ?#{#pageable}用于 Pageable. countQuery 用于Page<>。
Dmitry Stolbov回答

如果不写countQuery，那么jpa会默认在select的参数上加count，例如上面例子就会成为 select (u.*) …
这样就会报错。所以应该写出自定义的countQuery

---

跨表查询

难点

JpaRepository中的接口方法返回类型只能是实体类或者Object[]（值作为字符串列表返回，没有key），如果这时想要通过跨表连接查询，返回一个非实体类的DTO，就会报错。

Solution

利用HQL可以轻松解决。例如：

@Query(
 value = "select new com.test.dto.GetProjectUserDto(u.userId, u.name, u.displayName, r.name, pu.description) from User u, ProjectUser pu, Role r " +
         "where pu.projectId=?1 AND pu.userId=u.userId AND pu.roleId=r.id",
 countQuery = "select count(*) from User u, ProjectUser pu, Role r " +
         "where pu.projectId=?1 AND pu.userId=u.userId AND pu.roleId=r.id")
    Page<GetProjectUserDto> findByProjectId(String projectId, Pageable pageable);

注意：

• @Query内写的是HQL；SQL中的inner join、on在这里改为,和where。
• HQL内的写的是实体类的类名、字段名（不是数据库中的表名、字段名）
• DTO必须有对应的constructor，否则会报错找不到。推荐用lombok的注解@AllArgsConstructor，简单高效。
• 不必再像上面在HQL中加入ORDER BY ?#{#pageable}