In this article, I’m using the example from the wikipedia authors and books as a guideline to show you how to create association in many-to-many relationship.
In general idea, we needs 3 table. The table author_book will store foreign keys of book table and author table. Mapping association in Java class only focus on Books and Authors entity, and no direct association to the authors_books table.
POM
Below is the minimum requirement to test with Spring Data JPA.
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>Bidirectional Association Mapping
There are 2 option for many-to-many association mapping. Bidirectional and Unidirectional. Bidirectional is the most common association mapping, all we need is to do is to defined the owning and reference side.
In this example, I’m using author table as owning side and book table as reference side and both entity require to annotate @ManyToMany. For the owning side set the attributes mappedBy given a named that is from the referencing side.
Author.java (Owning side)
package com.example.demo.model;
import lombok.*;
import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;
@Entity
@Data
@NoArgsConstructor
@EqualsAndHashCode(exclude="books")
public class Author {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "author_seq")
@SequenceGenerator(name = "author_seq", sequenceName = "author_sequence")
@Setter(AccessLevel.NONE)
private Long id;
private String name;
/**
* Defined as owning side association
*/
@ManyToMany
@JoinTable(name= "authors_and_books",
joinColumns = @JoinColumn(name = "a_id"),
inverseJoinColumns = @JoinColumn(name = "b_id"))
private Set<Book> books = new HashSet<>();
public void addBook(Book book) {
this.books.add(book);
book.getAuthors().add(this);
}
public void removeBook(Book book) {
this.books.remove(book);
book.getAuthors().remove(this);
}
}
By default the generated table will named as author_book table name with 2 column author_id and book_id. If the table association name, or any of the foreign keys is different from the default name eg author_id & book_id. You can customise it with additional @JoinTable annotation. With the first joinColumns refer to the owning table foreign key while inverseJoinColumns refer to references table foreign key
@ManyToMany
@JoinTable(name= "authors_and_books",
joinColumns = @JoinColumn(name = "a_id"),
inverseJoinColumns = @JoinColumn(name = "b_id"))
private Set<Book> books = new HashSet<>();Book.java (Reference side)
package com.example.demo.model;
import lombok.AccessLevel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Setter;
import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;
@Entity
@Data
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "book_seq")
@SequenceGenerator(name = "book_seq", sequenceName = "book_sequence")
@Setter(AccessLevel.NONE)
private Long Id;
private String title;
private String isbn;
/**
* Defined as references association
*/
@ManyToMany(mappedBy = "books")
private Set<Author> authors = new HashSet<>();
public void addAuthor(Author author) {
this.getAuthors().add(author);
author.getBooks().add(this);
}
public void removeAuthor(Author author) {
this.getAuthors().remove(author);
author.getBooks().remove(this);
}
}
Lombok @Data
If you’re using Lombok project library annotate with @Data, you’ll need to add additional annotation to avoid java.lang.StackOverflowError: null error. You can exclude it either in Author class or Book class. As in my example, I’m added into Author class
@EqualsAndHashCode(exclude="authors")Testing
package com.example.demo;
import com.example.demo.model.Author;
import com.example.demo.model.Book;
import com.example.demo.repository.AuthorRepository;
import com.example.demo.repository.BookRepository;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.annotation.Commit;
import org.springframework.test.context.jdbc.Sql;
import static org.assertj.core.api.Assertions.assertThat;
@DataJpaTest
@Slf4j
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class ManyToManyTest {
@Autowired
BookRepository bookRepository;
@Autowired
AuthorRepository authorRepository;
@Test
public void testInsert() {
Book book1 = new Book();
book1.setTitle("My Book 1");
book1.setIsbn("98765123");
bookRepository.save(book1);
Book book2 = new Book();
book2.setTitle("My Book 2");
book2.setIsbn("34567892");
bookRepository.save(book2);
Book book3 = new Book();
book3.setTitle("Others Book");
book3.setIsbn("77828829");
bookRepository.save(book3);
Author mingch = new Author();
mingch.setName("mingch");
authorRepository.save(mingch);
Author others = new Author();
others.setName("others");
authorRepository.save(others);
assertThat(book1.getId()).isNotNull();
assertThat(book2.getId()).isNotNull();
assertThat(book3.getId()).isNotNull();
}
@Test
@Sql("/data-test.sql")
@Commit
public void testQuery() {
Book book1 = bookRepository.findBookByIsbn("98765123").orElse(null);
Book book2 = bookRepository.findBookByIsbn("34567892").orElse(null);
Book book3 = bookRepository.findBookByIsbn("77828829").orElse(null);
Book book4 = bookRepository.findBookByIsbn("xxxxxxxx").orElse(null);
assertThat(book1.getId()).isNotNull();
assertThat(book2.getId()).isNotNull();
assertThat(book3.getId()).isNotNull();
assertThat(book4).isNull();
Author mingch = authorRepository.findAuthorByName("mingch").orElse(null);
Author others = authorRepository.findAuthorByName("others").orElse(null);
assertThat(mingch.getId()).isNotNull();
assertThat(others.getId()).isNotNull();
}
@Test
@Commit
public void testAssociated() {
Book book1 = bookRepository.findBookByIsbn("98765123").orElse(null);
Book book2 = bookRepository.findBookByIsbn("34567892").orElse(null);
Author mingch = authorRepository.findAuthorByName("mingch").orElse(null);
// add association
mingch.addBook(book1);
mingch.addBook(book2);
assertThat(authorRepository.findAuthorByName("mingch").get().getBooks().size()).isEqualTo(2);
Book book3 = bookRepository.findBookByIsbn("77828829").orElse(null);
Author others = authorRepository.findAuthorByName("others").orElse(null);
// add association
others.addBook(book2);
others.addBook(book3);
assertThat(authorRepository.findAuthorByName("others").get().getBooks().size()).isEqualTo(2);
// you don't need to save or update again as in all entity now are MANAGED state,
// persistence context will update it after flush is called (dirty checked)
}
}
Test Container
To run separate postgresql in testcontainer, add the dependencies below.
</dependencies>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>junit-jupiter</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<scope>test</scope>
</dependency>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-bom</artifactId>
<version>${testcontainers.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>Modified the test case
package com.example.demo;
import com.example.demo.model.Author;
import com.example.demo.model.Book;
import com.example.demo.repository.AuthorRepository;
import com.example.demo.repository.BookRepository;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Commit;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.springframework.test.context.jdbc.Sql;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import static org.assertj.core.api.Assertions.assertThat;
@Testcontainers
@DataJpaTest
@Slf4j
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class ManyToManyTest {
@Container
public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:11")
.withDatabaseName("many2manyExample")
.withPassword("postgres")
.withUsername("postgres");
@DynamicPropertySource
static void postgresqlProperties(DynamicPropertyRegistry registry) {
registry.add("spring.datasource.url", postgreSQLContainer::getJdbcUrl);
registry.add("spring.datasource.password", postgreSQLContainer::getPassword);
registry.add("spring.datasource.username", postgreSQLContainer::getUsername);
}
@Autowired
BookRepository bookRepository;
@Autowired
AuthorRepository authorRepository;
@Test
public void testInsert() {
Book book1 = new Book();
book1.setTitle("My Book 1");
book1.setIsbn("98765123");
bookRepository.save(book1);
Book book2 = new Book();
book2.setTitle("My Book 2");
book2.setIsbn("34567892");
bookRepository.save(book2);
Book book3 = new Book();
book3.setTitle("Others Book");
book3.setIsbn("77828829");
bookRepository.save(book3);
Author mingch = new Author();
mingch.setName("mingch");
authorRepository.save(mingch);
Author others = new Author();
others.setName("others");
authorRepository.save(others);
assertThat(book1.getId()).isNotNull();
assertThat(book2.getId()).isNotNull();
assertThat(book3.getId()).isNotNull();
}
@Test
@Sql("/data-test.sql")
public void testQuery() {
Book book1 = bookRepository.findBookByIsbn("98765123").orElse(null);
Book book2 = bookRepository.findBookByIsbn("34567892").orElse(null);
Book book3 = bookRepository.findBookByIsbn("77828829").orElse(null);
Book book4 = bookRepository.findBookByIsbn("xxxxxxxx").orElse(null);
assertThat(book1.getId()).isNotNull();
assertThat(book2.getId()).isNotNull();
assertThat(book3.getId()).isNotNull();
assertThat(book4).isNull();
Author mingch = authorRepository.findAuthorByName("mingch").orElse(null);
Author others = authorRepository.findAuthorByName("others").orElse(null);
assertThat(mingch.getId()).isNotNull();
assertThat(others.getId()).isNotNull();
}
@Test
@Sql("/data-test.sql")
public void testAssociated() {
Book book1 = bookRepository.findBookByIsbn("98765123").orElse(null);
Book book2 = bookRepository.findBookByIsbn("34567892").orElse(null);
Author mingch = authorRepository.findAuthorByName("mingch").orElse(null);
// add association
mingch.addBook(book1);
mingch.addBook(book2);
assertThat(authorRepository.findAuthorByName("mingch").get().getBooks().size()).isEqualTo(2);
Book book3 = bookRepository.findBookByIsbn("77828829").orElse(null);
Author others = authorRepository.findAuthorByName("others").orElse(null);
// add association
others.addBook(book2);
others.addBook(book3);
assertThat(authorRepository.findAuthorByName("others").get().getBooks().size()).isEqualTo(2);
// you don't need to save or update again as in all entity now are MANAGED state,
// persistence context will update it after flush is called (dirty checked)
}
}
application.properties
In this example, set the ddl-auto to update, not create or created-drop during development. It’s because hibernate.hbm2ddl.auto Automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop, the database schema will be dropped when the SessionFactory is closed explicitly.
e.g. validate | update | create | create-drop
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialectorg.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
alter table authors_and_books Reference
https://gitlab.com/loongest/many-to-many/-/tree/master
Many-to-Many Association