Mapping many-to-many relationship with attributes with JPA

Question

Consider the following entity-relationship diagram

[PERSON] (*..N) ------ < is engaged into > ------ (*..N) [ACTIVITY]
                          /          \
                       hobby        duty

where [PERSON] and [ACTIVITY] are entities and <is engaged into> is a many-to-many relationship with attributes (flags) hobby and duty. The attributes are non-exclusive: both can be true or false independent of each other.

How would one map this ERD to the Java object model shown below using JPA (or Hibernate)?

@Entity
class Person {
    Collection<Activity> hobbies;     // only activities where hobby=true
    Collection<Activity> duties;      // only activities where duty=true
}


@Entity
class Activity{
    Collection<Person> aficionados;   // only people where hobby=true
    Collection<Person> professionals; // only people where duty=true
}

Answer 1

Really interesting case.

I made it work for me on JPA 2.1 / Hibernate 5.1.

Assuming that:

• Id of Person entity = PERSON_ID
• Id of Activity entity = ACTIVITY_ID
• Name of linking table = PERSON_ACTIVITY

Person entity:

@Entity
class Person {

    @ManyToMany
    @JoinTable(name="PERSON_ACTIVITY",
            joinColumns=@JoinColumn(name="PERSON_ID"),
            inverseJoinColumns=@JoinColumn(name="ACTIVITY_ID"))
    @WhereJoinTable(clause = "hobby = 1")
    Set<Activity>  hobbyActivities;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name="PERSON_ACTIVITY",
            joinColumns=@JoinColumn(name="PERSON_ID"),
            inverseJoinColumns=@JoinColumn(name="ACTIVITY_ID"))
    @WhereJoinTable(clause = "duty = 1")
    Set<Activity>  dutyActivities;

}

Activity entity:

@Entity
class Activity{

    @ManyToMany
    @JoinTable(name="PERSON_ACTIVITY",
            joinColumns=@JoinColumn(name="ACTIVITY_ID"),
            inverseJoinColumns=@JoinColumn(name="PERSON_ID"))
    @WhereJoinTable(clause = "hobby = 1")
    Set<Person>  hobbyPeople;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name="PERSON_ACTIVITY",
            joinColumns=@JoinColumn(name="ACTIVITY_ID"),
            inverseJoinColumns=@JoinColumn(name="PERSON_ID"))
    @WhereJoinTable(clause = "duty = 1")
    Set<Person>  dutyPeople;
}

Key points:

• The collection has to be explicitly a Set in all of the cases

• One of the @ManyToMany sets in each entity has to be marked as lazy loaded

• While doing some testing i found that i had to use DISTINCT a lot of times not to get repeating results (but that was only maybe my case).