FindById using a foreign key in Spring Boot-CodePudding

I am working on spring boot. I have two class Department and Employee which is associated with onetomany and manytoone relationship. While I'm trying to fetch data by foreign key, get all null data in postman.

Here down is my code:

Entity

public class Department {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer deptNo;
    private String deptName;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "department")
    private List<Employee> employees;

    // getter setter constructor
}

public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer empId;
    private String empName;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "deptNo")
    private Department department;

    // getter setter constructor
}

DTO

public interface EmpDeptDto {
    public Integer getEmpNo();
    public String getEmpName();
    public String getDepName();
}

Repository

public interface EmployeeRepo extends JpaRepository<Employee, Integer> {
    @Query(value = "SELECT e.empId, e.empName, d.deptName FROM Employee e JOIN e.department d WHERE d.deptNo = ?1")
    List<EmpDeptDto> getEmpByDepNo(Integer depNo);
}

Controller

@RequestMapping(value = "/emp/{depNo}", method = RequestMethod.GET)
public ResponseEntity<List<EmpDeptDto>> getAllEmployeeByDepNo(@PathVariable("depNo") Integer depNo) {
        List<EmpDeptDto> empdetails = employeeRepo.getEmpByDepNo(depNo);
        return ResponseEntity.ok(empdetails);
}

This is i got in json response

[
    {
        "empName": null,
        "depName": null,
        "empNo": null
    },
    {
        "empName": null,
        "depName": null,
        "empNo": null
    }
]

CodePudding user response：

Problem 1

First we have to make data members, getter, setter and parameterized constructor in EmpDeptDto. After that we have to change public interface EmpDeptDto into public class EmpDeptDto.

public class EmpDeptDto {
    private Integer empNo;
    private String empName;
    private String depName;

    public EmpDeptModel(Integer empNo, String empName, String depName) {
        this.empNo = empNo;
        this.empName = empName;
        this.depName = depName;
    }

    // getter and setter

}

Problem 2

Then after we have to assign new com.xxx.classname(val1, val2, val3...) DTO constructor inplace of e.empId, e.empName, d.deptName in repository query.

Previous query inside my repository that is wrong:

SELECT e.empId, e.empName, d.deptName FROM Employee e JOIN e.department d WHERE d.deptNo = ?1

Changed query that is solved my problem:

SELECT new com.company.dto.EmpDeptDto(e.empId, e.empName, d.deptName) FROM Employee e JOIN e.department d WHERE d.deptNo = ?1