递归过程中的Spring异常

Question

我有以下列

ID username placementID sponsorID
4   G1             3       3
5   G2             4       4
6   G3             5       5
3   mzys        null    null

我正在尝试使用JPA来计算第三代mzys上有多少用户。

答案应该是一个，因为mzys > G1 > G2 > G3

但是当我使用

userRepository.countByPlacementPlacementPlacementUsername(placementPlacementPlacement.getUsername())

它给了我

java.util.NoSuchElementException
    at java.util.ArrayList$Itr.next(Unknown Source)
    at java.util.Collections$UnmodifiableCollection$1.next(Unknown Source)
    at org.springframework.data.jpa.repository.query.CriteriaQueryParameterBinder.bind(CriteriaQueryParameterBinder.java:63)

更新

User.java

@Entity
@Table(name = "USER")
public class User extends BasePersist<UserBO> {

    private static final long serialVersionUID = 1L;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PLACEMENT_ID", referencedColumnName = "ID", nullable = true)
    private User placement;

    ...

现在，我正在尝试用

@Query(value="select count(u.id) from User u "
            + "left join u.placement u1 on u.placement.id = u1.placement.id "
            + "left join u.placement u2 on u1.placement.id = u2.placement.id "
            + "left join u.placement u3 on u2.placement.id = u3.placement.id "
            + "where u3.username =?1 and deletedflag =?2")
    int countByPlacementPlacementPlacementUsername(String username, boolean deletedFlag);

我得到了

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements

知道为什么会这样吗？有没有更好的方法来达到我所需要的？

Answer 1

主要问题是ON word。我把它去掉后，问题就解决了。

@Query(value="select count(u.id) from User u "
            + "left outer join u.placement u1 "
            + "left outer join u.placement u2 "
            + "left outer join u.placement u3 "
            + "where u3.username =?1 and u3.deletedFlag =?2")
    int countByPlacementPlacementPlacementUsername(String username, boolean deletedFlag);