jpi多表联查_JPA中使用@Query注解多表联查

原生SQL：

select `user`.id, `user`.`name`,dept.name deptName,sum(sd.score) SumScore

from `user`

LEFT JOIN

score_detail sd

ON

`user`.id = sd.user_id

LEFT JOIN

department dept

ON

`user`.department_id = dept.id

GROUP BY `user`.`id` ORDER BY sum(sd.score);

经过查阅多方资料得知，

异常: org.hibernate.hql.ast.QuerySyntaxException: Path expected for join

HQL是面向对象的，所以join的条件是反映在对象的关系中的，所以，你在用join的时候，无论是left join还是right join，都是

FROM DomainA a LEFT JOIN a.b b WHERE

或者

FROM DomainA a RIGHT JOIN a.b b WHERE

而不能直接是两个对象直接JOIN。

最终结果：

@Query("SELECT NEW com.ygsm.pojo.vo.SumScoreRankVO(u.id, u.name, d.name, SUM(s.score)) "

+ "FROM ScoreDetail s "

+ "RIGHT JOIN s.user u "

+ "RIGHT JOIN u.department d "

+ "WHERE s.createDate BETWEEN ?1 AND ?2 "

+ "GROUP BY u.id "

+ "ORDER BY SUM(s.score) DESC")

List findUserScoreRank(Date startDate, Date endDate);