JPA ManyToMany where annotation

Question

I'm using JPA / EclipseLink 2.5.2 and want to add an additional where clause to my ManyToMany Mapping. The SQL Code would look like this:

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

So I managed to do the JoinMapping with:

@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;
}

But I dont know how to add the WHERE f.verified=1

How can this be realized?

Thanks in advance, Cassidy

Answer 1

As advised in my comment you cannot use @ManyToMany as you require an additional column in the join table to indicate whether verified or not.

You then need to use a @OneToMany with an additional Entity, say Friendship. We can use the verified column as a discriminator and use a simple class hierarchy to distinguish between Unconfirmed and Confirmed friends.

This will then look something like the below (haven't tested it fully).

Note, I tested this with Hibernate but there is an issue so will need to look at again. These posts suggests the issue may be Hibernate specific:

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

So may be worth trying with 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;
    }
}

Base Entity for Friendship:

@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;
    }
}

Two subclassses which use the verified column as discriminator:

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

}

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

}

Answer 2

I dont think there is JPA standard to include the "Where" option. Personally I use NamedQuery to retrieve the entities. You can also use CriteriaQuery.

Well, In eclipseLink, you can use @AdditionalCriteria("this.verified=1")