Many-many relationship with extra columns in JPA

In the previous tutorial, I introduced you to the @ManyToMany annotation to express many-to-many relationships between any two tables in the database, such as a developer can work on multiple projects and vice versa: a project can have many developers. But in fact, we need to save some additional information regarding these two tables. For example, in a project, a developer can be assigned to do a task. In this case, how do we represent by entities in JPA? In this tutorial, let’s learn together.

First, I will create a Maven project as an example:

I will use Hibernate as JPA implementation so I will add Hibernate dependency as follows:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.6.1.Final</version>
</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-core</artifactId>

<version>5.6.1.Final</version>

</dependency>

MySQL Connector:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

Project Lombok:

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.22</version>
    <scope>provided</scope>
</dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<scope>provided</scope>

</dependency>

Assume, now I define the structure of the two tables developer and project as follows:

CREATE TABLE `developer` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `project` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `developer` (

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `project` (

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To illustrate the many-to-many relationship between two tables above, I also define a joinTable as follows:

CREATE TABLE `developer_project` (
  `developer_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  FOREIGN KEY (`developer_id`) REFERENCES `developer` (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `developer_project` (

`developer_id` int(11) NOT NULL,

`project_id` int(11) NOT NULL,

FOREIGN KEY (`developer_id`) REFERENCES `developer` (`id`),

FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As the example at the beginning of this tutorial, now I want to store more information: what the developer will do in the project. As you can see, here we can only save this information in the joinTable. So, I’ll add a column in the developer_project table as follows:

CREATE TABLE `developer_project` (
  `developer_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `task` varchar(255) DEFAULT NULL,
  FOREIGN KEY (`developer_id`) REFERENCES `developer` (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `developer_project` (

`developer_id` int(11) NOT NULL,

`project_id` int(11) NOT NULL,

`task` varchar(255) DEFAULT NULL,

FOREIGN KEY (`developer_id`) REFERENCES `developer` (`id`),

FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To represent these tables via entities, we cannot use the @ManyToMany annotation anymore. Because if you use the @ManyToMany annotation, the joinTable’s information will be hidden, and therefore we cannot display the information of ‘task’ column in developer_project table.

Solution for this problem, we can use @OneToMany annotation bidirectional way. How is it in detail? Keep continuing reading.

To use the bidirectional way with @OneToMany annotation, we need to do the following:

First, you need to build an entity for the developer_project table.

Since the developer_project table contains a Composite Primary Key, we will define a class with the @Embeddable annotation as follows:

package com.huongdanjava.jpamanymanyextracolumns;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import java.io.Serializable;

@Embeddable
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DeveloperProjectId implements Serializable {

    @Column(name = "developer_id")
    private Integer developerId;

    @Column(name = "project_id")
    private Integer projectId;

}

package com.huongdanjava.jpamanymanyextracolumns;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import javax.persistence.Column;

import javax.persistence.Embeddable;

import java.io.Serializable;

@Embeddable

@Data

@AllArgsConstructor

@NoArgsConstructor

public class DeveloperProjectId implements Serializable {

@Column(name = "developer_id")

private Integer developerId;

@Column(name = "project_id")

private Integer projectId;

}

Entity for the developer_project table will have the following contents:

package com.huongdanjava.jpamanymanyextracolumns;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name = "developer_project")
@Data
public class DeveloperProject {

    @EmbeddedId
    private DeveloperProjectId developerProjectId;

    @ManyToOne
    @MapsId("developerId")
    private Developer developer;

    @ManyToOne
    @MapsId("projectId")
    private Project project;

    @Column
    private String task;
}

package com.huongdanjava.jpamanymanyextracolumns;

import lombok.Data;

import javax.persistence.*;

@Entity

@Table(name = "developer_project")

@Data

public class DeveloperProject {

@EmbeddedId

private DeveloperProjectId developerProjectId;

@ManyToOne

@MapsId("developerId")

private Developer developer;

@ManyToOne

@MapsId("projectId")

private Project project;

@Column

private String task;

}

In this entity, I used the @ManyToOne annotation to express the many-to-one relationship between the developer_project table and the developer and project tables.

Here, I also used an @MapsId annotation. This annotation is used in the entity that declared the @EmbeddedId annotation to map to the primary key column defined in the Embeddable object. Therefore, the value of the @MapsId annotation will be the variable name in the @Embeddable object map with the primary key column.

The next step is to define the entity for the developer and project tables.

The entities of these two tables will use @OneToMany annotation:

package com.huongdanjava.jpamanymanyextracolumns;

import java.io.Serializable;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import lombok.Data;

@Table
@Entity
@Data
public class Developer implements Serializable {

  @Id
  @GeneratedValue
  private Integer id;

  @Column
  private String name;

  @OneToMany(mappedBy = "developer")
  private List<DeveloperProject> projects;
}

package com.huongdanjava.jpamanymanyextracolumns;

import java.io.Serializable;

import java.util.List;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.OneToMany;

import javax.persistence.Table;

import lombok.Data;

@Table

@Entity

@Data

public class Developer implements Serializable {

@Id

@GeneratedValue

private Integer id;

@Column

private String name;

@OneToMany(mappedBy = "developer")

private List<DeveloperProject> projects;

}

package com.huongdanjava.jpamanymanyextracolumns;

import java.io.Serializable;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import lombok.Data;

@Table
@Entity
@Data
public class Project implements Serializable {

  @Id
  @GeneratedValue
  private Integer id;

  @Column
  private String name;

  @OneToMany(mappedBy = "project")
  private List<DeveloperProject> developers;
}

package com.huongdanjava.jpamanymanyextracolumns;

import java.io.Serializable;

import java.util.List;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.OneToMany;

import javax.persistence.Table;

import lombok.Data;

@Table

@Entity

@Data

public class Project implements Serializable {

@Id

@GeneratedValue

private Integer id;

@Column

private String name;

@OneToMany(mappedBy = "project")

private List<DeveloperProject> developers;

}

OK, so we have already defined the entities. Now try running an example.

I will add the configuration file for JPA, persistence.xml, located in the /src/main/resources/META-INF directory with the following contents:

<persistence
        version="2.1"
        xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd">

    <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL">
        <class>com.huongdanjava.jpamanymanyextracolumns.Project</class>
        <class>com.huongdanjava.jpamanymanyextracolumns.Developer</class>
        <class>com.huongdanjava.jpamanymanyextracolumns.DeveloperProject</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>

        <properties>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpaexample" />
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="123456" />

            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.use_sql_comments" value="true" />
        </properties>
    </persistence-unit>
</persistence>

<persistence

version="2.1"

xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd">

<persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL">

<class>com.huongdanjava.jpamanymanyextracolumns.Project</class>

<class>com.huongdanjava.jpamanymanyextracolumns.Developer</class>

<class>com.huongdanjava.jpamanymanyextracolumns.DeveloperProject</class>

<exclude-unlisted-classes>true</exclude-unlisted-classes>

</properties>

</persistence-unit>

</persistence>

Suppose, you have the following data in your database:

then when running the following example:

package com.huongdanjava.jpamanymanyextracolumns;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class Application {

    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample");
        EntityManager em = emf.createEntityManager();
        EntityTransaction transaction = em.getTransaction();

        Project project  = em.find(Project.class, 1);

        System.out.println("Project: " + project.getName());
        project.getDevelopers().forEach(
                (developer) -> System.out.println("Developer: "+ developer.getDeveloper().getName() + " | Task: " + developer.getTask())
        );

        em.close();
        emf.close();
    }
}

package com.huongdanjava.jpamanymanyextracolumns;

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.EntityTransaction;

import javax.persistence.Persistence;

public class Application {

public static void main(String[] args) {

EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample");

EntityManager em = emf.createEntityManager();

EntityTransaction transaction = em.getTransaction();

Project project = em.find(Project.class, 1);

System.out.println("Project: " + project.getName());

project.getDevelopers().forEach(

(developer) -> System.out.println("Developer: "+ developer.getDeveloper().getName() + " | Task: " + developer.getTask())

);

em.close();

emf.close();

}

the result will be:

2 thoughts on “Many-many relationship with extra columns in JPA”

Tiru

11 May, 2022 at 12:11 pm

Hello Sir,

Irrespective of the column names that i mentioned @Column, in DB i am seeing only default one

case -1)
@Column(name = “developer_id”)
private Integer developerId;

@Column(name = “project_id”)
private Integer projectId;

CREATED Columns are : developer_id, project_id

case-2) if i change column names such as
@Column(name = “developer_id_1”)
private Integer developerId;

@Column(name = “project_id_1”)
private Integer projectId;

still generating same column names developer_id, project_id

can you please help me here.

Thanks
Thiru
Đặng Đình Quyền

22 October, 2020 at 1:40 am

Please show me how to add more data to this kind of relationship

Khanh Nguyen

Share this post

Tags

Many-many relationship with extra columns in JPA

2 thoughts on “Many-many relationship with extra columns in JPA”

Add Comment Cancel reply

Khanh Nguyen

Share this post

Tags

You might also like...

Talking about persistence.xml file in JPA

Connect to MSSQL Server in JPA

Ignore case sensitive for table name in JPA with Hibernate implementation

Learn about JPA Query Language

2 thoughts on “Many-many relationship with extra columns in JPA”

Add Comment Cancel reply