Spring Data JDBC – One to Many Example

A basic quick Spring Data JDBC example on how to map One to Many database tables relation in entities.

1. Database Tables

Let’s have a look into following tables, We can say that the relation between BRANCH and STUDENT is One-to-Many. Each College Engineering Branch has multiple number of Students.

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 `STUDENT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FIRST_NAME` varchar(45) DEFAULT NULL,
  `LAST_NAME` varchar(45) DEFAULT NULL,
  `CONTACT_NO` varchar(45) DEFAULT NULL,
  `BRANCH_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `BRANCH_ID_FK` 
    FOREIGN KEY (`BRANCH_ID`) 
    REFERENCES `BRANCH` (`BRANCH_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. Entities Mapping

3.1. Student entity :

@Data // lombok
public class Student {

	@Id
	private Long id;

	private String contactNo;
	private String firstName;
	private String lastName;

}

3.2. Branch Entity :

To map One-to-Many relation in Entity we have to use @MappedCollection on Collection like List or Set or Map.

@Data
public class Branch {

	@Id
	private int branchId;

	private String branchName;
	private String branchShortName;
	private String description;

	@MappedCollection(keyColumn = "BRANCH_ID", idColumn = "BRANCH_ID")
	private Set<Student> students;

}

4. Test the mapping

4.1. Create a JDBC Repository for Branch entity to perform basic database operations.

@Repository
public interface BranchOneToManyTestRepository extends CrudRepository<Branch, Long>{

}

4.2. Test Case

@SpringBootTest
public class OneToManyMappingTest extends BaseTest {

	@Autowired
	private BranchOneToManyTestRepository testRepository;

	@Test
	@DisplayName("One-to-Many Mapping Test")
	@Sql(scripts = "/one-to-many-mapping.sql")
	void embeddedMappingTest() {

		Branch branch = new Branch();
		branch.setBranchShortName("CSE");
		branch.setBranchName("Computer Science and Engineering");
		branch.setDescription("CSE department offers courses under ambitious curriculam in computer science and computer engineering..");
		Set<Student> students = new HashSet<>();
		students.add(getStudent1());
		students.add(getStudent2());
		branch.setStudents(students);

		Branch createdBranch = testRepository.save(branch);
		System.err.println(createdBranch);
		Assert.assertTrue(createdBranch != null);
	}

	private static Student getStudent1() {
		Student student = new Student();
		student.setFirstName("Rosy");
		student.setLastName("Larsen");
		student.setContactNo("+1-408-575-1317");

		return student;
	}

	private static Student getStudent2() {
		Student student = new Student();
		student.setFirstName("Rosy");
		student.setLastName("Larsen");
		student.setContactNo("+1-408-575-1219");

		return student;
	}
}

5. Conclusion

In this tutorial we have covered how to map One-to-Many association in Spring Data JDBC.

6. References

1. Spring Documentation
2. Lombok @Data
3. Hibernate One-to-Many Unidirectional
4. Spring Boot Data JDBC Example
5. Spring Boot H2 in-memory Database