JPA Join Table With Additional State

JPA has its puzzles and from time to time it is useful to write down that tricky solution for our mapping needs.This entry describes a ManyToMany relationship with an additional state in the intermediate table. All my examples are related to the Arena-PUJ project, the pet project I am hard working nowadays. Arena is an online system to manage academic competitions, and within its several tables and mappings, there is a corner case I will explain below. First, let me define the entities and its relationship for modeling the data of the Institutions X Competition relationship.

Institutions X Competitions

We have two entities, with a ManyToMany relationship:

• Competition: is a virtual competition where students apply for the best academic homework of a region (a city, a state or even a country). Several competitions happen at same time in different places, sponsored and organized by different institutions.
• Institution: a company, a JUG or a school. It models the competitors' university, the JUG organizing a competition or a Company sponsoring a competition. Institutions have roles in a competition.

The problem: institution roles are dynamic

Competitions happens annually and for different competitions a same institution can have different roles. The classical example is about sponsorship: a company that was partner in the 2008 becomes platinum sponsor in 2009. The modeling of roles of the institutions is dynamic - institutions can have different roles in different competitions.

The solution: a Join Table with an additional state

The relation between competitions and institutions is a @ManyToMany relationship but we cannot just annotate the entities. In order to support the dynamic roles of institutions we need to customize the relation table, what means we need to add additional columns in the join table, as demonstrated in the diagram below.

Implementing the join table with JPA 1.x Annotations

The join table has @ManyToOne relationships with the two entities and also an enumeration with the possible roles an institution can have in a competition. In order to work as a real join table, you must use a @ClassId as composite primary key of the join table. You can check out the complete source code from here, but the relevant parts are in the below fragments.

1. The join table:

   @Entity
   @IdClass(PujInstitutionRoles_PK.class)
   public class PujInstitutionRoles implements Serializable {
       public enum Role {
           SCHOOL, PUJ_OWNER, SPONSOR, PARTNER
       }
   
       @Enumerated(EnumType.STRING)
       @Column(columnDefinition = "VARCHAR(20)")
       private PujInstitutionRoles.Role role;
   
       @Id
       @ManyToOne
       @PrimaryKeyJoinColumn(name = "INSTITUTION_ACRONYM", referencedColumnName = "acronym")
       private PujInstitutionEntity institution;
   
       @Id
       @ManyToOne
       @PrimaryKeyJoinColumn(name = "COMPETITION_NAME", referencedColumnName = "name")
       private PujCompetitionEntity competition;
   }
   

2. The composite primary key of the join table

   public class PujInstitutionRoles_PK implements Serializable {
       private String institution;
       private String competition;
   }
   

   * Notice that @IdClass is a simple Java Type, not an Entity.

   * Important detail: the field names of the ID Class should match the names of the ID fields of the Join Table.

3. The Institution model ^*

   @Entity
   public class PujInstitutionEntity implements Serializable {
       @Id
       @Column(length = 20)
       private String acronym;
   }
   

4. The Competition model ^*

   @Entity
   public class PujCompetitionEntity implements Serializable {
       @Id
       @Column(length = 12)
       private String name;
   }
   

* In my real model I also have the mapping from the entities to the join table, but I ommited here to make the examples shorter.

Using the model: our join serves for two basic purposes: to maintain the relationship between institutions and competitions and also to allow us to query that relationship. The insertion of a new relationship is a normal insert operation, but the queries on the join table requires the usage of named queries.

How to find competitions by institutions?

The proper way to find this relationship is to define a @NamedQuery where I can find institutions by competitions, as demonstrated below. I am using some constants to facilitate the reference to the queries in other classes.

@NamedQueries( {
        @NamedQuery(name = PujCompetitionEntity.SQL.FIND_BY_INSTITUTION,
        query = "SELECT roles.competition FROM PujInstitutionRoles roles JOIN roles.institution inst WHERE inst.acronym=:"
                + PujCompetitionEntity.SQL.PARAM_INSTITUTION_ACRONYM) })
@Entity
public class PujCompetitionEntity implements Serializable {
    public static final String FIND_BY_INSTITUTION = "findCompetitionByInstitution";
    public static final String PARAM_INSTITUTION_ACRONYM = "institutionAcronym";
}

Example of usage:

@Stateless
public class PujCompetitionFacadeImpl {
    @PersistenceUnit(name = "arenapuj")
    protected EntityManagerFactory emf;

    public Collection<PujCompetitionEntity> findByInstitution(String acronym,
            int start, int max) throws IllegalStateException, IllegalArgumentException {

        EntityManager manager = emf.createEntityManager();

        try {
            Query query = manager
                    .createNamedQuery(PujCompetitionEntity.FIND_BY_INSTITUTION);
            query.setParameter(PujCompetitionEntity.PARAM_INSTITUTION_ACRONYM,
                    acronym);
            query.setFirstResult(start);
            query.setMaxResults(max);
            return getResultList(query);
        } finally {
            if (manager != null && manager.isOpen()) {
                manager.close();
            }
        }
    }
}

Some live examples:

• listing all pairs of Institution X Competition

• finding all competitions linked to CEJUG

Summary

The solution for the above problem is predicted in the JPA specification but the annotations details for implementing a Join Table with an additional state is not so intuitive (IMO). I documented the solution for a future quick reference and I hope you can also benefit from that - if you disagree of my modeling or if you have any good suggestion, please give me your feedback.

From http://weblogs.java.net/blog/felipegaucho