java - 从 Spring Data Jpa 查询返回自定义对象

Question

我在 jpa 存储库类中有一个自定义查询:

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);
}

查询返回请求最多的 10 个实习导师/教授；结果由教授的信息和一个整数值(计数)组成。

教授模型课:

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 模型(创建以封装查询结果):

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...
}

现在，我很难将查询返回的内容与我创建的模型绑定(bind)起来。更准确地说，我得到以下异常:

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]
...

我做错了什么吗？有没有更好的方法来完成我想做的事情？

Answer 1

您可以使用投影轻松实现此目的。这里有波纹管列:

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

从您的查询中创建一个带有 getter 的接口(interface)。你的投影会像这样:

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

您的查询将像这样:

@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);

希望这能解决您的问题。

有关更多详细信息，请访问 this thread .

谢谢:)