Home Spring Boot Spring Data JDBC - Many-to-Many Example

Spring Data JDBC – Many-to-Many Example

August 17, 2020

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.

Join Table in many to many associations in JPA and Hibernate

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 :

6. References

Spring Boot

Previous articleSpring Data JDBC – One-to-Many Example

Next articleSpring Boot – Loading Initial Data