java - 如何使用不直接在实体 hibernate spring JPA 上的属性进行 findBy 操作

Question

我有三个具有以下关系的实体实体用户有许多带有连接表的 Activity (单向关系)，因为我需要关系属性，规则实体有很多 Activity (双向关系)

我可以执行 findBy 来搜索来自一个特定规则的 Activity 吗？听起来有可能，因为 Activity 有规则，但我不知道如何使用 Hibernate 来做到这一点

这是我来自两种关系的代码

编辑:我已经用 MySQL 进行了测试，我需要的查询是这个

select * from user_activity where user_id = userIdValue and activity_id in (
select id from activity where rule_id = rule_idValue);

如何在 spring 上使用 hibernate 执行该查询？

用户 Activity (带有关系属性的连接表)

@Entity
@Table(name="USER_ACTIVITY")
public class JoinedUserActivity {

public JoinedUserActivity() {}

public JoinedUserActivity(JoinedUserActivityId joinedUserActivityId, String fileName) {
    this.joinedUserActivityId = joinedUserActivityId;
    this.fileName  = fileName;
    this.score = 0;
    this.lastHint = 0;
}
private String fileName; //user activity filename
@ManyToOne
@JoinColumn(name="ACTIVITY_ID", insertable=false, updatable=false)
private Activity activity;


private Integer score;
private Integer lastHint; 

public Activity getActivity() {
    return activity;
}

public void setActivity(Activity activity) {
    this.activity = activity;
}
//more set and gets
@EmbeddedId
private JoinedUserActivityId joinedUserActivityId;


// required because JoinedUserAchievments contains composite id
@Embeddable
public static class JoinedUserActivityId implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -9180674903145773104L;


    @ManyToOne
    @JoinColumn(name="USER_ID")
    private User user;

    @ManyToOne
    @JoinColumn(name="ACTIVITY_ID")
    private Activity activity;

    // required no arg constructor
    public JoinedUserActivityId() {}

    public JoinedUserActivityId(User user, Activity activity) {
        this.user = user;
        this.activity = activity;
    }

    public JoinedUserActivityId(Integer userId, Integer activityId) {
        this(new User(userId), new Activity(activityId));
    }

    public User getUser() {
        return user;
    }


    public Activity getActivity() {
        return activity;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public void setActivity(Activity activity) {
        this.activity = activity;
    }
    @Override

    public boolean equals(Object instance) {
        if (instance == null)
            return false;

        if (!(instance instanceof JoinedUserActivityId))
            return false;

        final JoinedUserActivityId other = (JoinedUserActivityId) instance;
        if (!(user.getId().equals(other.getUser().getId())))
            return false;

        if (!(activity.getId().equals(other.getActivity().getId())))
            return false;

        return true;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 47 * hash + (this.user != null ? this.user.hashCode() : 0);
        hash = 47 * hash + (this.activity != null ? this.activity.hashCode() : 0);
        return hash;
    }
  }
}

用户

@OneToMany(cascade=CascadeType.ALL, mappedBy="joinedUserActivityId.user")
private List<JoinedUserActivity> joinedUserActivityList = new ArrayList<JoinedUserActivity>();

Activity 没有有关用户的信息，因为它是单向的。

现在我的 Activity 规则没有连接表并且是双向的，代码如下Activity.java

@JsonIgnore
@ManyToOne
Rule rule;

规则.java

 @OneToMany(cascade=CascadeType.ALL, mappedBy="rule")
 private List<Activity> ListActivities = new ArrayList<Activity>();

所以...在连接表 user_activity 上，我可以定义一个 findBy 函数来返回哪些 Activity 具有来自一条规则的一个用户吗？

我已经做到了

@Override
JoinedUserActivity findOne(JoinedUserActivityId id);

这会返回一个用户的一个 Activity但我不知道如何按规则过滤

Answer 1

不需要您的子查询。您所需要的只是加入。

在 JPQL 中:

select distinct jua from JoinedUserActivity jua
where jua.user.id = :userId
and jua.activity.rule.id = :ruleId

或者，使用显式连接:

select distinct jua from JoinedUserActivity jua
join jua.user u
join jua.activity a
join a.rule r
where u.id = :userId
and r.id = :ruleId

现在只需在 Spring-data 存储库接口(interface)中添加所需的方法，并用 @Query 对其进行注释，并将上述查询之一作为值。