1

I already went through many links like : Spring JPA selecting specific columns, but here is my model. I am using Spring Boot 2.0.0

@Entity
@Table
public class Employee{
   @Id
   private Long employeeId;
   ....
   ....
   @OneToMany(mappedBy = "departmentId", cascade = CascadeType.ALL)
   private List<Department> departments;
   ..
   ...
   ...
   ...
   ...
   ...
}

I've written project like

public class EmployeeDto{
    public Long getEmployeeId();

    public List<Department> getDepartments();
}

When I developed JPARepositoryQuery, I get whole object. In fact, I am only expecting EmployeeId and list of departmentIds thats it.

@Query("SELECT s.employeeId, s.departments FROM Employee s WHERE s.employeeId = :employeeId")
EmployeeDto findProjectionByEmployeeId(@Param("employeeId") Long employeeId);

How can we restrict to get only two fields?

PAA
  • 1
  • 46
  • 174
  • 282

2 Answers2

1

The answer is short: That's not possible.

The result of your query will be a cartesian product with id and all fields of Department.

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
0

You already know employeeId, so just get departments

@Query("SELECT empl.departments FROM Employee empl WHERE empl.employeeId = :employeeId")
List<Department> findDepartmentsByEmployeeId(@Param("employeeId") Long employeeId);
v.ladynev
  • 19,275
  • 8
  • 46
  • 67
  • I also need to check if EmployeeId exists in DB if yes then I will check if departmentId coming in response is part of employeeId – PAA Jul 30 '21 at 12:43
  • What do you want to check? Don't you trust SQL? – Simon Martinelli Jul 30 '21 at 13:17
  • @Pra_A Please log SQL query. This JPQL query is converted to getting departments by foreign key. And looks like you have incorrect mapping with `mappedBy = "departmentId"`. You need something like `mappedBy = "employee"`. – v.ladynev Jul 30 '21 at 13:31
  • How can we make sure if EmployeeId and departmentId both exists? – PAA Aug 13 '21 at 05:36
  • @Pra_A Better to make an additional query to verify `employeeId`. – v.ladynev Aug 13 '21 at 09:48
  • Thanks, can u pls guide me here - https://stackoverflow.com/questions/68770075/spring-boot-two-datasource-configuration-for-h2-db-failed-to-create-tables-and-i? – PAA Aug 13 '21 at 09:49