A many-to-many relationship allows multiple instances on each side to relate to multiple instances on the other. Relational schemas represent it with a join table containing foreign keys to both entity tables. section 1.2.4 section 9.3.1
Implement the association according to the persistence technology and required behavior:
- Create a link table such as CATEGORY_ITEM or USER_BILLINGDETAILS, usually using both foreign-key columns as a composite primary key. section 1.2.4 section 9.3.1
- Map a JPA association with @ManyToMany and @JoinTable; use a Set when duplicate links are forbidden by the schema. section 9.3.1
- Make the association bidirectional with mappedBy on the inverse collection; persistence follows the collection on the owning side. section 9.3.1
- Model the intermediary explicitly in Spring Data JDBC, connecting User to UserAddress and storing the related Address identifier. section 15.3.4
Categories and items
Category owns a Set<Item> annotated with @ManyToMany and @JoinTable(name = "CATEGORY_ITEM"); Item exposes Set<Category> with @ManyToMany(mappedBy = "items"). Linking two categories and two items permits an item to belong to several categories while preventing duplicate pairs. section 9.3.1
Listing 15.22 The schema.sql file for many-to-many relationships
Path: Ch15/spring-data-jdbc6/src/main/resources/schema.sql
DROP TABLE IF EXISTS USERS_ADDRESSES;
DROP TABLE IF EXISTS USERS;
DROP TABLE IF EXISTS ADDRESSES;
CREATE TABLE USERS (
ID INTEGER AUTO_INCREMENT PRIMARY KEY,
ACTIVE BOOLEAN,
USERNAME VARCHAR(30),
EMAIL VARCHAR(30),
LEVEL INTEGER,
REGISTRATION_DATE DATE
);
CREATE TABLE ADDRESSES (
ID INTEGER AUTO_INCREMENT PRIMARY KEY,
STREET VARCHAR(30) NOT NULL,
CITY VARCHAR(20) NOT NULL
);
CREATE TABLE USERS_ADDRESSES (
USER_ID INTEGER,
ADDRESS_ID INTEGER,
FOREIGN KEY (USER_ID)
REFERENCES USERS(ID)
ON DELETE CASCADE,
FOREIGN KEY (ADDRESS_ID)
REFERENCES ADDRESSES(ID)
ON DELETE CASCADE
);
Inspect the complete Spring Data JDBC schema, including USERS, ADDRESSES, and the USERS_ADDRESSES intermediary table. section 15.3.4
