本文共 1556 字,大约阅读时间需要 5 分钟。
jqa的query使用原生sql查询(nativeQuery = true)
的时候不支持排序的,需要在sql里面加上:ORDER BY ?#{#pageable}
@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)PagefindByProjectId(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(*)
ORDER BY ?#{#pageable}用于 Pageable. countQuery 用于Page<>。
如果不写countQuery,那么jpa会默认在select的参数上加count,例如上面例子就会成为 select (u.*) …
这样就会报错。所以应该写出自定义的countQuery
JpaRepository中的接口方法返回类型只能是实体类
或者Object[](值作为字符串列表返回,没有key)
,如果这时想要通过跨表连接查询,返回一个非实体类的DTO,就会报错。
利用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") PagefindByProjectId(String projectId, Pageable pageable);
注意:
@Query
内写的是HQL;SQL中的inner join
、on
在这里改为,
和where
。@AllArgsConstructor
,简单高效。ORDER BY ?#{#pageable}
转载地址:http://yiztb.baihongyu.com/