TP6.2 - Employee-Department Relations

Objectives

By the end of this lab, you will be able to:

1. ✅ Create entities with @ManyToOne and @OneToMany relationships
2. ✅ Manage bidirectional relationships
3. ✅ Use cascade operations
4. ✅ Avoid the N+1 problem with JOIN FETCH

Estimated duration: 2h

---

Prerequisites

• ✅ Chapter 2 of Module 6 completed
• ✅ TP6.1 completed
• ✅ Maven 3.9+ and Java 17+

---

Preparation: New Project

Spring Initializr

Configuration:

• Artifact: employee-department
• Dependencies: Spring Web, Spring Data JPA, H2 Database

---

Exercise 1: Create the Entities

Step 1.1: Department Entity

File: model/Department.java

package com.example.relations.model;

import jakarta.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "departments")
public class Department {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String location;
    
    // ONE department has MANY employees
    @OneToMany(mappedBy = "department", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Employee> employees = new ArrayList<>();
    
    // Constructors
    public Department() {}
    
    public Department(String name, String location) {
        this.name = name;
        this.location = location;
    }
    
    // Helper methods to synchronize the relationship
    public void addEmployee(Employee employee) {
        employees.add(employee);
        employee.setDepartment(this);
    }
    
    public void removeEmployee(Employee employee) {
        employees.remove(employee);
        employee.setDepartment(null);
    }
    
    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getLocation() { return location; }
    public void setLocation(String location) { this.location = location; }
    
    public List<Employee> getEmployees() { return employees; }
    public void setEmployees(List<Employee> employees) { this.employees = employees; }
}

---

Step 1.2: Employee Entity

File: model/Employee.java

package com.example.relations.model;

import jakarta.persistence.*;

@Entity
@Table(name = "employees")
public class Employee {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String email;
    private Double salary;
    
    // MANY employees belong to ONE department
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;
    
    // Constructors
    public Employee() {}
    
    public Employee(String name, String email, Double salary) {
        this.name = name;
        this.email = email;
        this.salary = salary;
    }
    
    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    
    public Double getSalary() { return salary; }
    public void setSalary(Double salary) { this.salary = salary; }
    
    public Department getDepartment() { return department; }
    public void setDepartment(Department department) { this.department = department; }
}

---

Exercise 2: Create the Repositories

Step 2.1: DepartmentRepository

File: repository/DepartmentRepository.java

package com.example.relations.repository;

import com.example.relations.model.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
    
    // Simple method
    List<Department> findByLocation(String location);
    
    // With JOIN FETCH to avoid N+1
    @Query("SELECT d FROM Department d JOIN FETCH d.employees")
    List<Department> findAllWithEmployees();
}

---

Step 2.2: EmployeeRepository

File: repository/EmployeeRepository.java

package com.example.relations.repository;

import com.example.relations.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    // Traverse the relationship
    List<Employee> findByDepartmentName(String deptName);
    
    // With salary
    List<Employee> findBySalaryGreaterThan(Double minSalary);
    
    // Department AND salary
    List<Employee> findByDepartmentNameAndSalaryGreaterThan(
        String deptName, 
        Double minSalary
    );
    
    // With JOIN FETCH
    @Query("SELECT e FROM Employee e JOIN FETCH e.department")
    List<Employee> findAllWithDepartment();
    
    // Custom query
    @Query("SELECT e FROM Employee e WHERE e.department.location = :location")
    List<Employee> findByDepartmentLocation(@Param("location") String location);
}

---

Exercise 3: Initialize the Data

File: config/DataLoader.java

package com.example.relations.config;

import com.example.relations.model.Department;
import com.example.relations.model.Employee;
import com.example.relations.repository.DepartmentRepository;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataLoader {
    
    @Bean
    CommandLineRunner initDatabase(DepartmentRepository deptRepo) {
        return args -> {
            System.out.println("=== Initialization ===");
            
            // IT Department
            Department it = new Department("IT", "Montreal");
            it.addEmployee(new Employee("Alice", "alice@example.com", 75000.0));
            it.addEmployee(new Employee("Bob", "bob@example.com", 65000.0));
            it.addEmployee(new Employee("Charlie", "charlie@example.com", 80000.0));
            deptRepo.save(it);
            
            // HR Department
            Department hr = new Department("HR", "Quebec");
            hr.addEmployee(new Employee("David", "david@example.com", 55000.0));
            hr.addEmployee(new Employee("Eve", "eve@example.com", 60000.0));
            deptRepo.save(hr);
            
            // Sales Department
            Department sales = new Department("Sales", "Montreal");
            sales.addEmployee(new Employee("Frank", "frank@example.com", 50000.0));
            sales.addEmployee(new Employee("Grace", "grace@example.com", 52000.0));
            deptRepo.save(sales);
            
            System.out.println("=== " + deptRepo.count() + " departments created ===");
        };
    }
}

---

Exercise 4: Create the Controllers

Controller Departments

File: controller/DepartmentController.java

package com.example.relations.controller;

import com.example.relations.model.Department;
import com.example.relations.repository.DepartmentRepository;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/departments")
public class DepartmentController {
    
    private final DepartmentRepository deptRepo;
    
    public DepartmentController(DepartmentRepository deptRepo) {
        this.deptRepo = deptRepo;
    }
    
    @GetMapping
    public List<Department> getAll() {
        return deptRepo.findAll();
    }
    
    @GetMapping("/{id}")
    public Department getById(@PathVariable Long id) {
        return deptRepo.findById(id).orElseThrow();
    }
    
    @GetMapping("/with-employees")
    public List<Department> getAllWithEmployees() {
        return deptRepo.findAllWithEmployees();
    }
    
    @GetMapping("/location/{location}")
    public List<Department> byLocation(@PathVariable String location) {
        return deptRepo.findByLocation(location);
    }
}

---

Controller Employees

File: controller/EmployeeController.java

package com.example.relations.controller;

import com.example.relations.model.Employee;
import com.example.relations.repository.EmployeeRepository;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/employees")
public class EmployeeController {
    
    private final EmployeeRepository empRepo;
    
    public EmployeeController(EmployeeRepository empRepo) {
        this.empRepo = empRepo;
    }
    
    @GetMapping
    public List<Employee> getAll() {
        return empRepo.findAll();
    }
    
    @GetMapping("/department/{deptName}")
    public List<Employee> byDepartment(@PathVariable String deptName) {
        return empRepo.findByDepartmentName(deptName);
    }
    
    @GetMapping("/salary-above/{minSalary}")
    public List<Employee> highSalary(@PathVariable Double minSalary) {
        return empRepo.findBySalaryGreaterThan(minSalary);
    }
    
    @GetMapping("/with-department")
    public List<Employee> withDepartment() {
        return empRepo.findAllWithDepartment();
    }
}

---

Exercise 5: Test

Test 1: Retrieve all departments

GET http://localhost:8080/api/departments

⚠️ Check the SQL console:

SELECT * FROM departments;
-- No JOIN! Employees are not loaded (LAZY)

---

Test 2: Retrieve a department with its employees

GET http://localhost:8080/api/departments/1

SQL Console:

SELECT * FROM departments WHERE id = 1;
SELECT * FROM employees WHERE department_id = 1;  -- 2nd query!

This is normal with LAZY!

---

Test 3: Avoid N+1 with JOIN FETCH

GET http://localhost:8080/api/departments/with-employees

SQL Console:

-- 1 SINGLE query with JOIN!
SELECT d.*, e.* 
FROM departments d 
LEFT JOIN employees e ON d.id = e.department_id;

✅ Much more efficient!

---

Test 4: Employees of a department

GET http://localhost:8080/api/employees/department/IT

Result: Alice, Bob, Charlie

---

Test 5: Employees with salary > 60000

GET http://localhost:8080/api/employees/salary-above/60000

Result: Alice (75k), Charlie (80k), Eve (60k)

---

Challenge: Complete CRUD with Relationships

Create these endpoints:

1. POST /api/departments - Create a department
2. POST /api/employees - Create an employee (with department_id)
3. PUT /api/employees/{id}/department/{deptId} - Change an employee's department
4. DELETE /api/departments/{id} - Delete a department (and its employees in cascade)
5. GET /api/departments/{id}/employees - Employees of a specific department

---

Next Step

Proceed to TP3 for the full assessment!

➡️ TP6.3: Exercise Airport

⬅️ Previous TP: Conventions

⬅️ Back to Module 6