Return custom object from Spring Data Jpa query

Question

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?

Solution 1

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 :)

Solution 2

For example, let's that we have:

• User - entity object with many fields.

• UserBean - just object where our data will be converted.

Bad practice:

 @Repository
    public class ReportingRepository {

        @PersistenceContext
        private EntityManager em;

        public List<UserBean> findQuery() {
            Query query = em.createNativeQuery("select  ...  from Table  INNER JOIN ...");
            List<UserBean> items = (List<UserBean>) query.getResultList();
            return items;
        }

    }

but it will return values in arrays, so it will be more beautiful if we write the following code, that is better practice:

query.unwrap(SQLQuery.class)
           .addScalar("instance name", StringType.INSTANCE)
           .addScalar("second instance name", IntegerType.INSTANCE)
           .setResultTransformer(Transformers.aliasToBean(UserBean.class));

   List<UserBean> items = query.getResultList();

So finally everything is converted to UserBean class. Note that in addScalar method you should pass instance variable name (in your question you have count variable )

Solution 3

You can do this by either following ways.

1. Using ModelMapper to convert entity to target bean type
2. Use Spring Projection to make this happen

Go through the article to get more detail.

http://javasampleapproach.com/spring-framework/spring-data/query-alter-domain-model-spring-jpa-projection-springboot-mysql-database

Comments

[Md. Sajedul Karim]

The ResultTransformer comes with a legacy definition which is not following the Functional Interface syntax. Hence, we cannot use a lambda in this example. Hibernate 6.0 aims to overcome this issue, so that’s why the Hibernate ORM 5.2 ResultTransformer is deprecated. Do you know what is the alternative of ResultTransformer.

[grep]

At the moment stable version of Hibernate is still 5.3. For hibernate 6.0 hibernate team promised that there would be @FunctionalInterface. but nothing has changed. we are waiting :)

[valent0ne]

Can confirm that in my configuration I got several deprecated warnings.

[grep]

I use Spring boot 1.X and I don't have deprecated version of method of hibernate. it is deprecated in newer versions of Hibernate but still there is no alternative. At the moment Hibernate 5.3 is still stable version. We will wait new updates from hibernate, they promised to implement @FunctionalInterface. So just you the deprecated method until there will be any alternatives.

[Satish Patro]

getFirstName() is not worked for me when name was first_name, but worked, when it is firstName. Why?