This tutorial walks you through the process of mapping a Hibernate many-to-many extra columns relationship with a joined entity and composite primary keys in Spring Boot, Spring Data JPA, and MySQL

What you'll need

  • JDK 1.8+

  • Maven 3+

  • MySQL Server 5+

Init project structure

You can create and init a new Spring Boot project by using Spring Initializr or your IDE

Following is the final project structure with all the files we would create

├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── hellokoding
│       │           └── jpa
│       │               ├── model
│       │               │   ├── Book.java
│       │               │   ├── BookPublisher.java
│       │               │   ├── BookPublisherId.java
|       |               |   └── Publisher.java
│       │               ├── repository
|       |               |   ├── BookRepository.java
│       │               │   ├── BookPublisherRepository.java
│       │               │   └── PublisherRepository.java
│       │               └── Application.java
│       └──resources
│          └── application.properties
└── pom.xml

The Many To Many Extra Columns relationship mapping would be implemented in Book.java, Publisher.java, and BookPublisher.java

The joined entity would be defined in BookPublisher.java

The composite primary keys would be defined in BookPublisherId.java and embedded into BookPublisher.java as a primary key

Project dependencies

We will use the following dependencies

  • spring-boot-starter-data-jpa provides Hibernate and autoconfigure Spring DataSource

  • mysql provides MySQL Java Client

  • lombok for generating boilerplate code

<dependency>  
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>  
<dependency>  
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>  
<dependency>  
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

Many-To-Many Relationship

The book and publisher tables have a many-to-many relationship via book_publisher table.

Besides two composite primary keys: book_id and publisher_id, the joined table book_publisher has published_date as an extra column

Define and map JPA Entities

The many to many with extra columns mapping would be able to do with a joined entity and composite primary keys

Let's create Book, Publisher JPA Entities corresponding to book, publisher tables in the database

[Book.java]

package com.hellokoding.jpa.model;

import lombok.Data;

import javax.persistence.*;  
import java.util.HashSet;  
import java.util.Set;

@Data
@Entity
public class Book{  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @OneToMany(mappedBy = "book", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<BookPublisher> bookPublishers = new HashSet<>();

    public Book(String name) {
        this.name = name;
    }
}

[Publisher.java]

package com.hellokoding.jpa.model;

import lombok.Data;

import javax.persistence.*;  
import java.util.HashSet;  
import java.util.Set;

@Data
@Entity
public class Publisher {  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @OneToMany(mappedBy = "publisher")
    private Set<BookPublisher> bookPublishers = new HashSet<>();

    public Publisher(String name){
        this.name = name;
    }
}

The composite primary keys would be defined in a Serializable class with @Embeddable class annotation and injected into the joined entity as a primary key with @EmbeddedId

Let's create BookPublisherId to define composite primary keys

[BookPublisherId.java]

package com.hellokoding.jpa.model;

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

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

@Data @AllArgsConstructor @NoArgsConstructor
@Embeddable
public class BookPublisherId implements Serializable {  
    @Column(name = "book_id")
    private Integer bookId;

    @Column(name = "publisher_id")
    private Integer publisherId;
}

Create BookPublisher as a joined entity and define its primary key as BookPublisherId with @EmbeddedId

[BookPublisher.java]

package com.hellokoding.jpa.model;

import lombok.Getter;  
import lombok.NoArgsConstructor;  
import lombok.Setter;

import javax.persistence.*;  
import java.util.Date;

@Getter @Setter @NoArgsConstructor
@Entity
@Table(name = "book_publisher")
public class BookPublisher {  
    @EmbeddedId
    private BookPublisherId id;

    @ManyToOne
    @MapsId("bookId")
    @JoinColumn(name = "book_id")
    private Book book;

    @ManyToOne
    @MapsId("publisherId")
    @JoinColumn(name = "publisher_id")
    private Publisher publisher;

    @Column(name = "published_date")
    private Date publishedDate;

    public BookPublisher(Book book, Publisher publisher, Date publishedDate) {
        this.id = new BookPublisherId(book.getId(), publisher.getId());
        this.book = book;
        this.publisher = publisher;
        this.publishedDate = publishedDate;
    }
}

Spring Data JPA Repository

Spring Data JPA contains some built-in Repository implemented some common functions to work with the database: findOne, findAll, save,...All we need for this example is to extend it

[BookRepository.java]

package com.hellokoding.jpa.repository;

import com.hellokoding.jpa.model.Book;  
import org.springframework.data.jpa.repository.JpaRepository;

public interface BookRepository extends JpaRepository<Book, Integer>{  
}

[PublisherRepository.java]

package com.hellokoding.jpa.repository;

import com.hellokoding.jpa.model.Publisher;  
import org.springframework.data.jpa.repository.JpaRepository;

public interface PublisherRepository extends JpaRepository<Publisher, Integer> {  
}

[BookPublisherRepository.java]

package com.hellokoding.jpa.repository;

import com.hellokoding.jpa.model.BookPublisher;  
import com.hellokoding.jpa.model.BookPublisherId;  
import org.springframework.data.jpa.repository.JpaRepository;

public interface BookPublisherRepository extends JpaRepository<BookPublisher, BookPublisherId> {  
}

Application Properties

Configure the Spring Datasource JDBC URL, user name, and password of your local MySQL server in application.properties

[application.properties]

spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false  
spring.datasource.username=root  
spring.datasource.password=hellokoding  
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.hibernate.ddl-auto=create  
spring.jpa.database-platform=org.hibernate.dialect.MySQL57Dialect  
spring.jpa.generate-ddl=true  
spring.jpa.show-sql=true

Create the test database in your local MySQL server if not exists

We don't have to create table schemas, the ddl-auto=create config allows JPA and
Hibernate does that based on the entity-relationship mappings. In practice, consider to use ddl-auto=none (default) and use a migration tool such as Flyway for better database management

spring.jpa.show-sql=true for showing generated SQL queries in the application logs, consider to disable it on production environment

Creating data with JPA and Hibernate

To persist the joined entity in many-to-many, the @EmbededId value need to be filled manually as Hibernate would not be able to set the value via reflection, otherwise, you would get the following error in the console

Caused by: org.hibernate.PropertyAccessException: Could not set field value by reflection

On the above entity mapping for BookPublisher, we did the configuration via its constructor

@Entity
public class BookPublisher {

    public BookPublisher(Book book, Publisher publisher, Date publishedDate) {
        this.id = new BookPublisherId(book.getId(), publisher.getId());
        this.book = book;
        this.publisher = publisher;
        this.publishedDate = publishedDate;
    }
}

Let's create Application.java to try creating data with Hibernate and launch the application with @SpringBootApplication

[Application.java]

package com.hellokoding.jpa;

import com.hellokoding.jpa.model.Book;  
import com.hellokoding.jpa.model.BookPublisher;  
import com.hellokoding.jpa.model.Publisher;  
import com.hellokoding.jpa.repository.BookPublisherRepository;  
import com.hellokoding.jpa.repository.BookRepository;  
import com.hellokoding.jpa.repository.PublisherRepository;  
import lombok.RequiredArgsConstructor;  
import org.springframework.boot.CommandLineRunner;  
import org.springframework.boot.SpringApplication;  
import org.springframework.boot.autoconfigure.SpringBootApplication;

import javax.transaction.Transactional;  
import java.util.Arrays;  
import java.util.Date;

@RequiredArgsConstructor
@SpringBootApplication
public class Application implements CommandLineRunner {  
    private final BookRepository bookRepository;
    private final PublisherRepository publisherRepository;
    private final BookPublisherRepository bookPublisherRepository;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    @Transactional
    public void run(String... strings) throws Exception {
        Book b1 = new Book("Spring Boot");
        Book b2 = new Book("Spring Data JPA");
        bookRepository.saveAll(Arrays.asList(b1, b2));

        Publisher p1 = new Publisher("HelloKoding 1");
        Publisher p2 = new Publisher("HelloKoding 2");
        publisherRepository.saveAll(Arrays.asList(p1, p2));

        BookPublisher bp1 = new BookPublisher(b1, p1, new Date());
        BookPublisher bp2 = new BookPublisher(b1, p2, new Date());
        BookPublisher bp3 = new BookPublisher(b2, p1, new Date());
        BookPublisher bp4 = new BookPublisher(b2, p2, new Date());
        bookPublisherRepository.saveAll(Arrays.asList(bp1, bp2, bp3, bp4));
    }
}

Run and Test

Type the below command at the project root directory, make sure your local MySQL Server is running

mvn clean spring-boot:run

Query the schema and data created by JPA/Hibernate based on your mapping

mysql> describe book;  
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> describe publisher;  
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> show create table book_publisher\G

Create Table: CREATE TABLE `book_publisher` (  
  `book_id` int(11) NOT NULL,
  `publisher_id` int(11) NOT NULL,
  `published_date` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`book_id`,`publisher_id`),
  KEY `FKnihk8b6sfx2mvtstq87wpjsfu` (`publisher_id`),
  CONSTRAINT `FK8ywuvxfycghsfmxvu363jllpq` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `FKnihk8b6sfx2mvtstq87wpjsfu` FOREIGN KEY (`publisher_id`) REFERENCES `publisher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> select * from book;  
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | Spring Boot     |
|  2 | Spring Data JPA |
+----+-----------------+

mysql> select * from publisher;  
+----+---------------+
| id | name          |
+----+---------------+
|  1 | HelloKoding 1 |
|  2 | HelloKoding 2 |
+----+---------------+

mysql> select * from book_publisher;  
+---------+--------------+----------------------------+
| book_id | publisher_id | published_date             |
+---------+--------------+----------------------------+
|       1 |            1 | 2020-10-31 21:45:40.814000 |
|       1 |            2 | 2020-10-31 21:45:40.814000 |
|       2 |            1 | 2020-10-31 21:45:40.814000 |
|       2 |            2 | 2020-10-31 21:45:40.814000 |
+---------+--------------+----------------------------+

Conclusion

In this tutorial, we learned to map a JPA and Hibernate Many to Many extra columns relationship with a joined entity and composite primary keys in Spring Boot and MySQL. You can find the source code on Github

JPA and HibernateSpring Boot