Home » Enterprise Java » JPA Many-to-Many association in SQL and NoSQL databases under Payara

About Alin Constantin

Alin Constantin
Java Software Developer

JPA Many-to-Many association in SQL and NoSQL databases under Payara

1. Introduction

In this article we will explore the JPA bidirectional @ManyToMany association in a SQL and NoSQL fashion. A @ManyToMany association occurs when multiple records in one table, each correspond to multiple records in a related table. If the records from the second table have an inverse association back to the first table, we say that we have a bidirectional @ManyToMany association. For SQL databases, in a bidirectional @ManyToMany association, an extra table is needed, called junction table, and its purpose is to hold a composite unique key that consists of the two foreign key fields that refer to the primary keys of both data tables. Also, one entity must be the owner of the relationship and the other entity must use the mappedBy attribute to define its mapping. Without the mappedBy attribute, the persistence provider will assume there are two independent relationships and you will end up having duplicate records into the join table. Alternatively, a @ManyToMany can be decorated with lazy loading, cascading, etc.

For developing the applications presented in this article we used NetBeans IDE 8.1, Payara 4.1.1.154 (Full Java EE) application server, Apache Derby Server 10.11.1.2 (that comes bundled with Payara), and MongoDB 3.0.7. You will also need a JDK environment, 1.7 or 1.8.

2. Problem and use-case

Let’s suppose we have the following two tables: Players and Tournaments. In this use-case, one player can participate to multiple tournaments during a year, and obviously, registered in our tournaments we can have multiple players. At a specific time, we might want to see all players’ information, along with the tournaments names in which they play. Similarly, we might want to see all tournaments information, along with each players’ information that has been registered. This can be accomplished using a bidirectional @ManyToMany association, as illustrated below:

Figure 2.1

Figure 2.1

3. @ManyToMany in a SQL database

3.1 Introduction

In this section, we have developed an EAR application, called ManyToMany_EclipseLink_and_ApacheDerby, which aims to illustrate the use-case presented in the previous section. The application contains two modules, an EJB module in which we will develop our EJB beans and entities and a WAR module needed to simply display our data in a web page. To create the application, we used NetBeans IDE 8.1 and Payara 4.1 as the application server. We also used Apache Derby, which comes bundled with Payara, as the database layer.

You can download the complete application from here. Now, let’s focus on the relevant parts!

3.2 Creating the @ManyToMany relationship

Inside the EJB module, in the eclipselink.apachederby.entity package, we have two entities. The first one is Players, which looks like below:

package eclipselink.apachederby.entity;

// Imports

@Entity
@Table(name = "atp_players")
public class Players implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @Column(name = "player_name")
    private String name;

    @Column(name = "player_surname")
    private String surname;

    @Column(name = "player_age")
    private int age;

    @Temporal(javax.persistence.TemporalType.DATE)
    @Column(name = "player_birth")
    private Date birth;

    @ManyToMany(targetEntity = eclipselink.apachederby.entity.Tournaments.class, 
        cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
    private Collection tournaments;

    // Getters and setters

}

We have highlighted the @ManyToMany relationship which in simple words says that:

  • the Players entity is the owner entity of our bidirectional many-to-many relationship, since the mappedBy attribute is omitted
  • the entity class that is the target of the association is the Tournaments entity
  • persist operations should be cascaded automatically to entity objects that are referenced by the tournaments field
  • the association must be eagerly fetched

The second entity we see is called Tournaments and it looks like below:

package eclipselink.apachederby.entity;

// Imports

@Entity
@Table(name = "atp_tournaments")
public class Tournaments implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String tournament;

    @ManyToMany(targetEntity = eclipselink.apachederby.entity.Players.class, 
        mappedBy = "tournaments")
    private Collection players;

    public Collection getPlayers() {
        return players;
    }

    // Getters and setters

}

We have highlighted the @ManyToMany relationship which in simple words says that:

  • the entity class that is the target of the association is the Players entity
  • the field that owns the relationship is called tournaments, and we saw above that it is a field in the Players entity

3.3 Configuring the database connection

Our next step is the persistence.xml file, which contains several configurations specific to Apache Derby that are highlighted below:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
 <persistence-unit name="EclipseLink_ManyToMany-ejbPU" transaction-type="JTA">
  <class>eclipselink.apachederby.entity.Players</class>
  <class>eclipselink.apachederby.entity.Tournaments</class>
  <exclude-unlisted-classes>false</exclude-unlisted-classes>
  <properties>
   <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
   <property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:mapping_entities_db;create=true"/>
   <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
   <property name="javax.persistence.schema-generation.create-source" value="metadata"/>
   <property name="javax.persistence.schema-generation.drop-source" value="metadata"/>
  </properties>
 </persistence-unit>
</persistence>

These configurations specifies that the necessary tables will be created on the default schema (named sun-appserv-samples) when running our application. You can explore them by navigating to the Services tab in NetBeans IDE and connecting to the sun-appserv-samples database:

Figure 3.1

Figure 3.1

3.4 Creating the web page

Now let’s have a quick look to the WAR module. We will use the JavaServer Faces technology for the presentation layer of our application. There is nothing fancy here, there are no managed beans, just a simple .xhtml page which looks like below:

<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
    xmlns:h="http://java.sun.com/jsf/html">
  <h:head>
    <title>@ManyToMany</title>
  </h:head>
  <h:body>
    <h1>@ManyToMany via EclipseLink and Apache Derby</h1>
    <h:form>
      <h:commandButton action="#{bean.persistAction()}" value="Populate database"/>
    </h:form>
    <h:dataTable value="#{bean.loadData()}" var="t" border="1" rendered="#{facesContext.postback}">
      <h:column>
        #{t.name}
      </h:column>
      <h:column>
        #{t.surname}
      </h:column>
      <h:column>
        #{t.age}
      </h:column>
      <h:column>
        #{t.birth}
      </h:column>
      <h:column>
        <h:dataTable value="#{t.tournaments}" var="v" border="1">
          <h:column>
            #{v.tournament}
          </h:column>
        </h:dataTable>
      </h:column>
    </h:dataTable>
  </h:body>
</html>

Notice that CDI is used in order to reference #{bean} to the SampleBean session bean located in the eclipselink.apachederby.ejb package of our EJB module. So, when you press the “Populate database” button, it will call the persistAction() method and use a helper class (the Helper class inside the eclipselink.apachederby.helper package) in order to randomly generate some players and tournaments and populate the database.

3.5 Testing the application

As mentioned above, pressing the “Populate database” button will populate our database and then navigate to the same web page. When this happens, the #{bean.loadData()} method expression will be evaluated and the result of the loadData() method will be displayed in a table. A possible output is:

Figure 3.2

Figure 3.2

For quick testing, we used JP-QL. So, in order to select all players and tournaments (loaded eager) we have:

public List loadData() {
    List players = em.createQuery("SELECT e FROM Players e").getResultList();             
    return players;
}

The complete application is called ManyToMany_EclipseLink_and_ApacheDerby.

4. @ManyToMany in a NoSQL database

4.1 Introduction

In the last years, NoSQL databases have significantly gain popularity around the world and many companies and open source communities adopted these non-relational, distributed, open-source and horizontally scalable databases. Red Hat is one of them, releasing Hibernate OGM. Based on the Hibernate ORM Core engine and reusing the Java Persistence Query Language as an interface for querying stored data, Hibernate Object/Grid Mapper (OGM) provides the JPA support for some of the common NoSQL databases. In this example, we will see how we can create a many-to-many relationship via Hibernate OGM and MongoDB.

4.2 Storing associations

For relational databases, in a bidirectional many-to-many association for example, the relational model usually uses three tables, two tables for data and an additional table, known as a junction table which holds a composite key that consists of the two foreign key fields that refer to the primary keys of both data tables. In a MongoDB many-to-many association, the junction table is stored as a document. You can define the way OGM stores association information in MongoDB. The following two strategies exist:

  • IN_ENTITY: store association information within the entity (we will use this one)
  • ASSOCIATION_DOCUMENT: store association information in a dedicated document per association

For ASSOCIATION_DOCUMENT, you can define how to store association documents. Possible strategies are:

  • GLOBAL_COLLECTION (default): stores the association information in a unique MongoDB collection for all associations
  • COLLECTION_PER_ASSOCIATION: stores the association in a dedicated MongoDB collection per association

4.3 Hibernate OGM and JPA 2.1 annotations support

Hibernate OGM translates each entity in accordance with the official JPA specification, but adapted to MongoDB capabilities. Between the supported annotation we have @ManyToMany also (for associations, Hibernate OGM also supports @OneToOne, @OneToMany and @ManyToOne). Moreover, Hibernate OGM supports unidirectional and bidirectional associations. In a unidirectional @ManyToMany association, Hibernate OGM will store the navigation information for associations in the owner collection, in fields that store the foreign keys in embedded collections. In a bidirectional @ManyToMany association, both sides will contain embedded collections for storing the corresponding navigation information (foreign keys) and when the COLLECTION_PER_ASSOCIATION or GLOBAL_COLLECTION strategies are used, a third collection will be used.

4.4 Testing the application

Below you can see a bidirectional many-to-many association (in Figure 4.1, we have the atp_players collection, and in Figure 4.2 the atp_tournaments collection). The output comes from interrogating our MongoDB database using a command line tool:

Figure 4.1

Figure 4.1

Figure 4.2

Figure 4.2

Supposing that you already have installed and configured MongoDB on localhost (127.0.0.1:27017). First stop is the persistence.xml file which contains several configurations specific to MongoDB:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
 <persistence-unit name="HOGM_ManyToMany-ejbPU" transaction-type="JTA">
 <provider>org.hibernate.ogm.jpa.HibernateOgmPersistence</provider>
  <class>hogm.mongodb.entity.Players</class>
  <class>hogm.mongodb.entity.Tournaments</class>
  <properties>
   <property name="hibernate.classloading.use_current_tccl_as_parent" value="false"/>
   <property name="hibernate.transaction.jta.platform" 
             value="org.hibernate.service.jta.platform.internal.SunOneJtaPlatform"/>
   <property name="hibernate.ogm.datastore.provider" value="mongodb"/>
   <property name="hibernate.ogm.datastore.document.association_storage" value="IN_ENTITY"/>      
   <property name="hibernate.ogm.datastore.database" value="mapping_entities_db"/>
   <property name="hibernate.ogm.datastore.create_database" value="true"/>
   <property name="hibernate.ogm.mongodb.host" value="127.0.0.1"/>
   <property name="hibernate.ogm.mongodb.port" value="27017"/>
  </properties>
 </persistence-unit>
</persistence>

Rest of our application remains the same as in the case presented for SQL, except for the query from loadData() method. In order to query a MongoDB database, we have three cases:

  • Using the Java Persistence Query Language (JP-QL) – query language defined as part of the Java Persistence API (JPA) specification.
  • Using the native backend query language – underlying native query language
  • Using Hibernate Search queries – offers a way to index Java objects into Lucene indexes and to execute full-text queries on them

For quick testing, we used JP-QL. So, in order to select all players and tournaments (loaded eager) we have:

public List loadData() {
    List players = em.createQuery("SELECT e FROM Players e").getResultList();             
    return players;
}

A possible output is:

Figure 4.3

Figure 4.3

The complete application is called ManyToMany_HOGM_and_MongoDB.

5. Conclusion

In this article we have explore the JPA bidirectional @ManyToMany association in a SQL and NoSQL fashion. For testing the @ManyToMany association we have developed two EAR applications, one using Apache Derby as the database layer, the other using MongoDB.

Download
You can download the full source code of this example here: JPA-ManyToMany.
(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of