Spring Data JDBC – Many to Many Example

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 :

1. Spring Boot 2.3.0.RELEASE
2. Spring Data JDBC 2.0.0.RELEASE
3. Spring Framework 5.2.6.RELEASE
4. H2 / MySql DB
5. Lombok 1.18.12
6. 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.

6. References

1. Spring Documentation
2. Spring Aggregates
3. Lombok @Data
4. Jpa Many-to-Many Unidirectional
5. Spring Boot H2
6. Spring Data JDBC Example