Access elements of Join Tables in spring through Service

Question

I am trying to learn it and I am facing some conceptual issues.

Let's imagine to have the entities of Employee, Project and Department where:

Employee - Department is a ManyToOne relationship
Employee - Project is a ManyToMany relationship

Right now I have written this:

Employee.java @Entity

public class Employee {

    @Id @GeneratedValue
    private Long id;
    private String name;

    @ManyToOne
    @JoinColumn(name="department_id")
    private String department;

    @ManyToMany
    @JoinTable(name="project_employee",
                joinColumns=@JoinColumn(name="project_id", referencedColumnName="id"),
                inverseJoinColumns=@JoinColumn(name="employee_id", referencedColumnName="id"))
    private ArrayList<Project> projects;

//constructors, getters, setters...
}

Project.java

@Entity
public class Project {

    @Id @GeneratedValue
    private Long id;
    private String client;

    @ManyToMany(mappedBy="project")
    private ArrayList<Employee> employees;

//constructors, getters, setters...
    }

Department.java

@Entity
public class Department {

    @Id @GeneratedValue
    private Long id;
    private String name;

    @OneToMany(mappedBy="department")
    private ArrayList<Employee> employees;

    //constructors, getters, setters...
        }

EmployeeService.java

@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRep;
    @Autowired
    private ProjectRepository projectRep;
    @Autowired
    private DepartmentRepository deptRep;

    public List<Project> getProjectsByEmployee(Long emplId){

    }

}

The first solution that came in my mind to implement that methods was:

• Get the specific employee object through the employeeRep and then loop over its list project retrieving ids and using them to retrieve project info with projectRep.

But in this case it seems I am not exploiting the table relation I have between Project and Employee, so what's the point on having it then?

Answer 1

But in that case it seems to me that I am not exploiting the table relation I have between Project and Employee, so what's the point on having it then?

In fact you exploit that ManyToMany table when you access to the Project relationship from an Employee entity (JPA performs a query for you at that time) but you don't exploit it efficiently. I will explain.

The first solution that came in my mind to implement that methods was:

Get the specific employee object through the employeeRep and then loop over its list project retrieving ids and using them to retrieve project info with projectRep.

That is bad idea because you don't want to perform a lot of queries while a single one rather simple could achieve the same thing.
Similarly defining a repository by entity is not mandatory :

@Autowired
private EmployeeRepository employeeRep;
@Autowired
private ProjectRepository projectRep;
@Autowired
private DepartmentRepository deptRep;

if according to your requirements, your queries rely on a pivot table (for example the employee table), just declare :

@Autowired
private EmployeeRepository employeeRep;

And perform the join from employee to project to get the list of projects for a specific employee.

In JPQL you could use fetch join on projects to get projects associated to the matching employee such as :

public List<Project> getProjectsByEmployee(Long empId){
    String jpql = "SELECT emp FROM Employee e" +
                  "INNER JOIN FETCH e.projects" +
                  "WHERE emp.id=:empId";
    TypedQuery<Employee> query = em.createQuery(jpql, Employee.class)
                                         .setParameter("empId", empId);
    Employee emp = query.getSingleResult();
    return emp.getProjects();
}