DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • An Overview of SQL Server Joins
  • How to Pivot and Join Time Series Data in Flux
  • Top 10 Web Developer Communities Developers Should Join in 2021
  • MongoDB to Couchbase: An Introduction to Developers and Experts

Trending

  • Creating a Web Project: Caching for Performance Optimization
  • Cosmos DB Disaster Recovery: Multi-Region Write Pitfalls and How to Evade Them
  • IoT and Cybersecurity: Addressing Data Privacy and Security Challenges
  • How to Merge HTML Documents in Java
  1. DZone
  2. Data Engineering
  3. Databases
  4. JPA Join Table With Additional State

JPA Join Table With Additional State

By 
Felipe Gaúcho user avatar
Felipe Gaúcho
·
Oct. 26, 09 · Interview
Likes (0)
Comment
Save
Tweet
Share
25.6K Views

Join the DZone community and get the full member experience.

Join For Free

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

Database Joins (concurrency library)

Opinions expressed by DZone contributors are their own.

Related

  • An Overview of SQL Server Joins
  • How to Pivot and Join Time Series Data in Flux
  • Top 10 Web Developer Communities Developers Should Join in 2021
  • MongoDB to Couchbase: An Introduction to Developers and Experts

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: