spring data jpa - class based projections with custom query

Question

I need to have a spring data repository method for a custom query and would like to use class based projection.

Looking at this https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

@Entity
public class Person {
  @Id
  private Long id;
  private String firstName;
  private String lastName;
  private int age;
}

@Value // lombok annotation to create constructor, equals and hash-code
public class PersonDTO {
  private String firstName;
  private String lastName;
}

public interface PersonRepository extends Repository<Person, Long> {

List<PersonProjection> findDistinct();

@Query("select distinct firstName, lastName from Person")
List<PersonProjection> findDistinctQuery();

@Query(value = "select distinct first_name, last_name from person", nativeQuery = true)
List<PersonProjection> findDistinctNativeQuery();

}

• findDistinct works well
• findDistinctQuery and findDistinctNativeQuery throw

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.x.PersonDTO]

Is there any option to make it work with classes (not interfaces)?

Answer 1

please write your method in person repository like below

@Query(value = "select distinct new xxx.xxx.dto.PersonDTO(p.first_name, p.last_name) from person p")
List<PersonProjection> findDistinctNativeQuery();

"xxx.xxx.dto.PersonDTO" this should point to your fully qualified class name.

for your reference please look into this https://www.bytestree.com/spring/spring-data-jpa-projections-5-ways-return-custom-object/

Answer 2

This works too:

public interface PersonRepository extends CrudRepository<Person, Long> {

    @Query(nativeQuery = true,
        value = "SELECT DISTINCT fist_name AS firstName, last_name AS lastName FROM person ")
    List<PersonNameOnly> findDistinctNames();

    interface PersonNameOnly {
        String getFirstName;
        String getLastName;
    }
}