How to get an array object response in @Query annotation in Spring

Question

I am working with AngularJS and Spring Boot technologies for my project tasks, and I am new to Spring. I use @Query annotation with nativeQuery=true in my Spring repository and I want to get an array of objects response.

Example of Response:

[{id: 1, name: "John Michael", age: 19, company_position: "Programmer"}]

This is an example of my Query:

SELECT q.id, q.name, q.age, w.company_position FROM employee q, company w WHERE q.id=w.emp_id;

How to get an array object response in @Query annotation in Spring?

Answer 1

Here are two examples. In the entity is loaded, and the data copied to the DTO. In the second example a prection query is used to minimize data load and copying. I have change the original query to select the company name, since it does not make since to store employ position in the company table. Lombok annotations are used to create getter/setter and constructors.

Employee class

@Entity
@Getter
@Setter
public class Employee {

    @Id
    private Long id;

    @Column
    private String name;

    @Column
    private int age;

    @ManyToOne
    private Company company;
}

Company class

@Entity
@Getter
@Setter
public class Company {
    @Id
    private Long id;

    @Column
    private String name;

    @OneToMany(cascade = CascadeType.ALL)
    private List<Employee> employees;
}

A simple POJO class (DTO) for generating the JSON the way we want.

@Data
@AllArgsConstructor
public class EmployeeAndCompanyDTO {

    String name;
    int age;
    String companyName;
}

Spring Data repository

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

Example Rest Controller

@RestController
public class EmployeeController {

    @Autowired
    EmployeeRepository employeeRepository;

    @ResponseBody
    public List<EmployeeAndCompanyDTO> employeeAndCompanyView() {
        List<Employee> employee = employeeRepository.findAll();
        return employee.stream()
                .map(e-> new EmployeeAndCompanyDTO(e.getId(), e.getName(), e.getAge(), e.getCompany().getName()))
                .collect(Collectors.toList());
    }
}   

If you wanted to avoid loading all the data, but only load the columns you need you could use a custom projection query in the repository:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query("select new com.example.EmployeeAndCompanyDTO(e.id, e.name, e.age, e.company.name) from Employee e")
    List<EmployeeAndCompanyDTO> employeeAndCompanyView();

}

Writing the projection query is a bit more tricky (depending on your IDE support), but you load less data, and there is no need to convert from entity to DTO in you controller/service classes.