Java – Return custom object from Spring Data Jpa query

javaspringspring-bootspring-dataspring-data-jpa

I've a custom query inside a jpa repository class:

package it.univaq.we.internshipTutor.repository;
import ...

public interface ProfessorRepository extends JpaRepository<Professor, Long> {

    List<Professor> findAll();

    ...

    @Query(value =  "SELECT professor.id, professor.department_id, " +
                    "professor.first_name, professor.last_name, " +
                    "professor.email, COUNT(professor_id) as count " +
                    "FROM professor LEFT JOIN student_internship ON professor.id = professor_id " +
                    "GROUP BY professor_id ORDER BY count DESC LIMIT ?1", nativeQuery = true)
    List<ProfessorInternshipCount> mostRequestedProfessors(int limit);
}

The query returns the 10 most requested internship tutors/professors; the result is composed by the information of the Professor and an integer value (the count).

Professor model class:

package it.univaq.we.internshipTutor.model;

import ...

@Entity
@Table(name = "professor")
public class Professor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Transient
    private UUID uuid;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id", nullable = false)
    @NotNull(message = "this field is mandatory")
    private Department department;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "professor")
    private List<StudentInternship> studentInternships;

    @Column(name = "first_name", nullable = false, length = 255)
    @NotEmpty
    private String firstName;

    @Column(name = "last_name", nullable = false, length = 255)
    @NotEmpty
    private String lastName;

    @Column(name = "email", nullable = false, length = 255)
    @Email
    @NotEmpty
    private String email;

    ...getters and setters...
}

ProfessorInternshipCount model (created to incapsulate the result of the query):

package it.univaq.we.internshipTutor.model;

public class ProfessorInternshipCount {
    private Professor professor;
    private Integer count;

    public ProfessorInternshipCount(Professor professor, int count) {
        this.professor = professor;
        this.count = count;
    }

    ...getters and setters...
}

Now, I've difficulties in binding what the query returns with the model I've created.
More precisely I get the following exception:

org.springframework.core.convert.ConverterNotFoundException: 
    No converter found capable of converting from type 
    [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] 
    to type 
    [it.univaq.we.internshipTutor.model.ProfessorInternshipCount]
...

Am I doing something wrong? Is there a better way of doing what I'am trying to do?

Best Answer

You can easily achive this using projection. Here you have the bellow columns :

private String firstName;
private String lastName;
private Long id;

Create an Interface with getter from your query. Your projection will like this:

public interface ITestProjection {
    Long getId();
    Integer getCount();
    String getFirstName();
    String getLastName();
}

Your Query will like this :

@Query(value = "SELECT professor.id, professor.department_id, " +
                    "professor.first_name, professor.last_name, " +
                    "professor.email, COUNT(professor_id) as count " +
                    "FROM professor LEFT JOIN student_internship ON professor.id = professor_id " +
                    "GROUP BY professor_id ORDER BY count DESC LIMIT =?1", nativeQuery = true)
    ArrayList<ITestProjection> findDataWithCount(Integer limit);

Hope this will solve your problem.

For more details visit this thread.

Thanks :)