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:
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 themappedBy
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 thePlayers
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:
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:
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 associationsCOLLECTION_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:
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:
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.
You can download the full source code of this example here: JPA-ManyToMany.