In this tutorial you will see entity mappings for Many-to-Many data base table relations using Spring Data JDBC example.
1. Many-to-Many Tables Relation
Let’s have a look into following database tables relationship between Engineering Branch and Subjects that are having each Engineering Branch in a semester. In an Engineering college each Branch have many Subjects and each Subject may appear in many Engineering Branches in semester.
CREATE TABLE `BRANCH` ( `BRANCH_ID` int(11) NOT NULL AUTO_INCREMENT, `BRANCH_SHORT_NAME` varchar(45) NOT NULL, `BRANCH_NAME` varchar(100) NOT NULL, `DESCRIPTION` varchar(200) DEFAULT NULL, PRIMARY KEY (`BRANCH_ID`) ); CREATE TABLE `SUBJECT` ( `SUBJECT_ID` int(11) NOT NULL AUTO_INCREMENT, `SUBJECT_NAME` varchar(100) NOT NULL, `SUBJECT_DESC` varchar(200) DEFAULT NULL, PRIMARY KEY (`SUBJECT_ID`) ); CREATE TABLE `BRANCH_SUBJECT` ( `BRANCH_ID` int(11), `SUBJECT_ID` int(11), PRIMARY KEY (`BRANCH_ID`,`SUBJECT_ID`), CONSTRAINT `BRANCH_SUB_ID_FK` FOREIGN KEY (`BRANCH_ID`) REFERENCES `BRANCH` (`BRANCH_ID`), CONSTRAINT `SUBJECT_SUB_ID_FK` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `SUBJECT` (`SUBJECT_ID`) );
2. Project Configuration
1.1. Used Technologies :
- Spring Boot 2.3.0.RELEASE
- Spring Data JDBC 2.0.0.RELEASE
- Spring Framework 5.2.6.RELEASE
- H2 / MySql DB
- Lombok 1.18.12
- JUnit 5
1.2. Maven Dependencies :
To start working with Spring Boot with Spring Data JDBC you need dependency spring-boot-starter-data-jdbc
. Use Lombok to avoid boiler plate code. Here is complete dependency list used in the example application.
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
3. Mapping Entities
In Spring Data JDBC, to create Entities the class should follow the aggregate design of Domain Driven Design (DDD). An entity can only be part of 1 aggregate.
Spring Data JDBC does not support many-to-many relationships, because in many to many relation there must be two aggregate roots in entity mapping. But we can achieve Many-to-Many entity mapping using Id References. For more clear picture let’s have look into following entities mapping for the above many-to-many tables design.
3.1. Subject entity :
@Data // lombok public class Subject { @Id private Long subjectId; private String subjectDesc; private String subjectName; }
3.2. SubjectRef Entity :
SubjectRef entity is just to hold Subject entity Ids.
@Table("BRANCH_SUBJECT") @Data @AllArgsConstructor public class SubjectRef { Long subjectId; }
3.3. Branch Entity mapping :
@Data public class Branch { @Id private Long branchId; private String branchName; @Column("BRANCH_SHORT_NAME") private String branchShortName; private String description; @MappedCollection(idColumn = "BRANCH_ID") private Set<SubjectRef> subjects = new HashSet<>(); public void addSubject(Subject subject) { subjects.add(new SubjectRef(subject.getSubjectId())); } }
4. Test the mapping
4.1. Create a JDBC Repository for Branch and Subject entity to perform basic database operations.
@Repository public interface SubjectTestRepository extends CrudRepository<Subject, Long>{ }
@Repository public interface BranchTestRepository extends CrudRepository<Branch, Long>{ }
4.2. Test Case
@SpringBootTest public class ManyToManyMappingTest extends BaseTest { @Autowired private BranchTestRepository branchRepository; @Autowired private SubjectTestRepository subjectRepository; @Test @DisplayName("many-to-many-mapping-test") @Sql(scripts = "/many-to-many-mapping.sql") void embeddedMappingTest() { Subject subj1 = subjectRepository.save(getSubject1()); Subject subj2 = subjectRepository.save(getSubject2()); Subject subj3 = subjectRepository.save(getSubject3()); Branch branch1 = getBranch1(); branch1.addSubject(subj1); branch1.addSubject(subj2); Branch createdBranch1 = branchRepository.save(branch1); Assert.assertTrue(createdBranch1 != null); Branch branch2 = getBranch2(); branch2.addSubject(subj1); branch2.addSubject(subj3); Branch createdBranch2 = branchRepository.save(branch2); Assert.assertTrue(createdBranch2 != null); branchRepository.delete(branch1); Assert.assertTrue(!branchRepository.existsById(branch1.getBranchId())); branchRepository.findAll().forEach(b -> System.err.println(b)); } private static Subject getSubject1() { Subject subject = new Subject(); subject.setSubjectName("Software Engineering"); subject.setSubjectDesc( "Apply key aspects of software engineering processes for the development of a complex software system"); return subject; } private static Subject getSubject2() { Subject subject = new Subject(); subject.setSubjectName("Distributed System"); subject.setSubjectDesc("Explore recent advances in distributed computing systems"); return subject; } private static Subject getSubject3() { Subject subject = new Subject(); subject.setSubjectName("Business Analysis and Optimization"); subject.setSubjectDesc("understand the Internal and external factors that impact the business strategy"); return subject; } private static Branch getBranch1() { Branch branch = new Branch(); branch.setBranchName("Computer Science and Engineering"); branch.setBranchShortName("CSE"); branch.setDescription( "CSE department offers courses under ambitious curricula in computer science and computer engineering.."); return branch; } private static Branch getBranch2() { Branch branch = new Branch(); branch.setBranchName("Information Technology"); branch.setBranchShortName("IT"); branch.setDescription( "IT is the business side of computers - usually dealing with databases, business, and accounting"); return branch; } }
5. Conclusion
In this tutorial we have covered how to map Many-to-Many association in Spring Data JDBC.
Checkout source code at Git Hub.
Other Spring Data JDBC Examples :
- Spring Data JDBC – Embedded Entities
- Spring Data JDBC – Pagination
- Spring Data JDBC – One-to-Many
- Spring Data JDBC – One-to-One
- Spring Data JDBC – Query Derivation
- Spring Boot – Loading Initial data
6. References
- Spring Documentation
- Spring Aggregates
- Lombok @Data
- Jpa Many-to-Many Unidirectional
- Spring Boot H2
- Spring Data JDBC Example