We have just covered the subject of Many-To-Many relationships, which is a fun topic for the hole family! Now there are some particular cases on the Many-to-many front which are not particularly hard, but can lead to a bit of confusion. One of those particular cases is the M2M with same table.
A reader asked : "How would one create manyToMany relationship with the same entity. Entity user can have friends (user), and can be friend to other users. With note that junction table has to have some additional columns besides user keys. How can that be achieved?" (Thanks Felix for the feedback and question!)
So, to start of let us be clear that to follow this Tutorial you should :
Have a basic understanding of JPA2 , enough to create a few entities etc
Know how to create a Many to many relationship between two related Entities
If you have been following the Tutorials till now, then you can go right ahead and start this one. If you haven't been following but know the stuff that is fine as well. If you want to go and check them out : JPA2 Tutorials .
You'll need EclipseLink, JUnit and MySQL, so if you are missing any of these this is the time to go and get them:
Source Code for all of this can be found at Sourceforge
Lets get started
In the previous tutorial we have discussed about the* Join Table*, which is a table that has one only purpose, which is to allow the creation of a many to many relationship between two tables. This is not entirely correct, as it was pointed out by simply reading the question that was posed. The two tables can in fact be the same table, and in that case the Join Table is mapping a many to many relationship between elements of the same type. The following diagram exemplifies:
Let us start by creating a database with these two tables. The SQL should look something like this :
CREATE TABLE IF NOT EXISTS `jpatutorial3u1`.`person` (
`idperson` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`phonenumber` VARCHAR(45) NULL ,
PRIMARY KEY (`idperson`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `jpatutorial3u1`.`business` (
`seller` INT NOT NULL ,
`buyer` INT NOT NULL ,
PRIMARY KEY (`seller`, `buyer`) ,
INDEX `fk_person_has_person_person1_idx` (`buyer` ASC) ,
INDEX `fk_person_has_person_person_idx` (`seller` ASC) ,
CONSTRAINT `fk_person_has_person_person`
FOREIGN KEY (`seller` )
REFERENCES `jpatutorial3u1`.`person` (`idperson` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_person_has_person_person1`
FOREIGN KEY (`buyer` )
REFERENCES `jpatutorial3u1`.`person` (`idperson` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
As for the Entity Bean, let us create one Entity called PersonEntity, and add the existing columns to it, just the columns for now. The main bits of the PersonEntity class should look somewhat like this:
@Entity
@Table(name = "person", catalog = "jpatutorial3u1", schema = "")
public class PersonEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "idperson", nullable = false)
private Integer idperson;
@Basic(optional = false)
@Column(name = "name", nullable = false, length = 45)
private String name;
@Basic(optional = true)
@Column(name = "phonenumber", length = 45)
private String phonenumber;
Create now the Getters and Setters for these fields. While we are at it, lets go and take a look at the Test Packages folder and check the MemSelfTest JUnit class. For those not following the source code, the relevant bit is :
@Test
public void testMemSelf(){
// Start a transaction
em.getTransaction().begin();
// ------------
PersonEntity c = new PersonEntity();
c.setName("Rui");
em.persist(c);
em.flush();
Object result = em.find(PersonEntity.class, c.getIdperson());
assertEquals(c, result);
em.remove(c);
em.flush();
// Commit the Transaction
em.getTransaction().commit();
}
It doesn't really do much except for creating a Person, searching for it, and removing it.... just to make sure everything is working fine and we can proceed.
The basic stuff is now done, lets move on to the mapping of the relationship
Who Sold To Who?
Lets start with editing the PersonEntity class by adding the following bit:
@JoinTable(name = "business", joinColumns = {
@JoinColumn(name = "seller", referencedColumnName = "idperson", nullable = false)}, inverseJoinColumns = {
@JoinColumn(name = "buyer", referencedColumnName = "idperson", nullable = false)})
@ManyToMany
private Collection<PersonEntity>soldToCollection;
and, as per usual, add the Setters and Getters for soldToCollection.
If you are a bit lost right now with the notation used and the way it was used, then you should probably give a read to JPA 2 Tutorial – Relationships – Many To Many , but I'll recap a bit here.
The @JoinTable annoration is used on the Owning Entity side of the relationship, which is in fact the PersonEntity class (both the owning and the inverse to be honest, but it will depend on the context it is being used). The Join Table is specify as "friend" , which is the name of the Join Table and then we do the JoinColumns bit, in which we specify the Fields used to map the relationship. Please do give a read at JPA 2 Tutorial – Relationships – Many To Many if nothing seems to make sense at this point!
Let us modify the M2mSelfTest JUnit class now. First let us create another few users :
PersonEntity c = new PersonEntity();
c.setName("Rui");
PersonEntity f1 = new PersonEntity();
f1.setName("Felix");
PersonEntity f2 = new PersonEntity();
f2.setName("Gusy");
em.persist(c);
em.persist(f1);
em.persist(f2);
em.flush();
Now we got a few PersonEntity objects to play about with. After the assertEquals that we have created previously, add the following bit as well:
assertEquals(c.getSoldToCollection().size(),0);
c.getSolfToCollection().add(f1);
em.merge(c);
em.flush();
assertEquals(c.getSoldToCollection().size(),1);
System.out.println(c.getName() + " sold stuff to :");
for (PersonEntity tempP : c.getSoldToCollection() )
System.out.println(" - " + tempP.getName() );
and to finish it off , lets delete every object we have created and clean the DB :
em.remove(c);
em.remove(f1);
em.remove(f2);
em.flush();
// Commit the Transaction
em.getTransaction().commit();
Run it and, if it was done right, everything should have work fine:
[EL Info]: 2013-06-06 16:07:42.49--ServerSession(805125680)--EclipseLink, version: Eclipse Persistence Services - 2.3.2.v20111125-r10461
[EL Info]: 2013-06-06 16:07:42.753--ServerSession(805125680)--file:/home/rui/projects/syshex-code/JPATutorial3.1/trunk/build/classes/_JPATutorial3.1PU login successful
Rui sold stuff to :
- Felix
But you know what would be really cool too? For a us to know who he Bought stuff from, yeah?
Who did He Bought From?
First, lets modify the PersonEntity again and add the following:
@ManyToMany(mappedBy = "soldToCollection")
private Collection<PersonEntity> boughtFromCollection;
Feeling a bit lost? Check out JPA 2 Tutorial – Relationships – Many To Many where it is explained everything about @ManyToMany and the mappedBy property.
Go ahead and create the Setter and Getter for this new collection, and afterward , lets go to the MemSelfTest JUnit test and modify like so:
System.out.println(c.getName() + " sold stuff to :");
for (PersonEntity tempP : c.getSoldToCollection() )
System.out.println(" - " + tempP.getName() );
System.out.println(c.getName() + " Bought stuff from :");
for (PersonEntity tempP : c.getBoughtFromCollection())
System.out.println(" - " + tempP.getName() );
em.refresh(f1);
System.out.println(f1.getName() + " sold stuff to :");
for (PersonEntity tempP : f1.getSoldToCollection() )
System.out.println(" - " + tempP.getName() );
System.out.println(f1.getName() + " Bought stuff from :");
for (PersonEntity tempP : f1.getBoughtFromCollection())
System.out.println(" - " + tempP.getName() );
Lets run it !
[EL Info]: 2013-06-06 16:18:04.464--ServerSession(745084087)--EclipseLink, version: Eclipse Persistence Services - 2.3.2.v20111125-r10461
[EL Info]: 2013-06-06 16:18:04.73--ServerSession(745084087)--file:/home/rui/projects/syshex-code/JPATutorial3.1/trunk/build/classes/_JPATutorial3.1PU login successful
Rui sold stuff to :
- Felix
Rui Bought stuff from :
Felix sold stuff to :
Felix Bought stuff from :
- Rui
Wicked stuff really.
But, what if.... we need to store some information regarding the relationship?
Storing Info about Relationship
Well, then we would have something like this :
Now, as it was said in the last Tutorial :
The table that you see serving as a connection between the Company table and the Client table is what is called a “Join Table” , and its sole purpose in life is to keep track of the relations that exist between Company and Client Entities.
And storing information regarding Money in it will make it have* more the a "sole" purpose. Not that it is wrong to do this, if you need to have information regarding the relationship, but since it has information, it should be an Entity. It could be called *BusinessEntity, in which money , seller and buyer are in fact information that regard the Business.
In looking at this Business table and an Entity and not as a simple JoinTable, we can deduce that there are One-To-Many relationships to be used instead of the* Many-To-Many*.
So, wrapping it up, in this situation you should see this in a totally different light than a M2M
Hope his helped, and as per usual, leave a comment if you find errors, problems, or for a kind word. And again, thanks for checking out our blog.
Also, check the next tutorial on this series: JPA 2 Tutorial - Queries with JPQL