java - JPA ManyToMany where 注释

Question

我正在使用 JPA/EclipseLink 2.5.2，并且想要向我的 ManyToMany 映射添加一个额外的 where 子句。 SQL 代码如下所示:

SELECT * FROM Friends f
INNER JOIN User u ON f.friend_id = u.id 
WHERE f.verified=1; 

所以我设法通过以下方式进行 JoinMapping:

@Entity
@NamedQueries({
    @NamedQuery(name="User.findAll", query="SELECT u FROM User u"),
    @NamedQuery(name="User.login", query="SELECT a FROM User a WHERE a.username = :name and a.password = :password"),
    @NamedQuery(name="User.findId", query="SELECT a FROM User a WHERE a.id = :id"),
    @NamedQuery(name="User.findName", query="SELECT a FROM User a WHERE a.username = :name")

})
public class User implements Serializable {
...
@ManyToMany
@JoinTable(
    name="Friends"
    , joinColumns={
        @JoinColumn(name="user_id")
        }
    , inverseJoinColumns={
        @JoinColumn(name="friend_id")
        }
    )
List<User> friends;
}

但我不知道如何添加WHERE f.verified=1

如何实现这一点？

提前致谢，卡西迪

Answer 1

正如我的评论中所建议的，您不能使用@ManyToMany，因为您需要在连接表中添加一个附加列来指示是否已验证。

然后，您需要将 @OneToMany 与附加实体(例如友谊)一起使用。我们可以使用已验证列作为鉴别器，并使用简单的类层次结构来区分未确认和已确认的 friend 。

这将如下所示(尚未完全测试)。

注意，我用 Hibernate 对此进行了测试，但存在问题，因此需要再次查看。这些帖子表明该问题可能是 Hibernate 特有的:

• https://stackoverflow.com/a/1631055/1356423 。
• Why @OneToMany does not work with inheritance in Hibernate

因此 EclipseLink 可能值得尝试。

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private int id;

    @OneToMany(mappedBy = "user")
    private Set<ConfirmedFriendship> confirmedFriendships;

    @OneToMany(mappedBy = "user")
    private Set<UnconfirmedFriendship> unconfirmedFriendships;

    public List<User> getConfirmedFriends() {
        return getFriends(confirmedFriendships);
    }

    public List<User> getUnconfirmedFriends() {
        return getFriends(unconfirmedFriendships);
    }

    private List<User> getFriends(Set<?  extends Friendship> friendships){
        List<User> friends = new ArrayList<User>();

        for(Friendship friendship : friendships) {
            friends.add(friendship.getFriend());
        }

        return friends;
    }
}

友谊的基础实体:

@Entity
@Table(name = "friendships")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "verified")
public abstract class Friendship {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private int id;

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

    @ManyToOne
    @JoinColumn(name = "friend_id")
    private User friend;

    @Column(name = "verified")
    private boolean verified;

    public int getId() {
        return id;
    }

    public User getUser() {
        return user;
    }

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

    public User getFriend() {
        return friend;
    }

    public void setFriend(User friend) {
        this.friend = friend;
    }

    public boolean isVerified() {
        return verified;
    }

    public void setVerified(boolean verified) {
        this.verified = verified;
    }
}

使用已验证列作为鉴别器的两个子类:

@Entity
@DiscriminatorValue(value = "1")
public class ConfirmedFriendship extends Friendship {

}

@Entity
@DiscriminatorValue(value = "0")
public class UnconfirmedFriendship extends Friendship {

}