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

swyrik · Accepted Answer · 2020-11-12 07:43:13Z

13

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/

edited Nov 12, 2020 at 7:43

answered Feb 10, 2020 at 10:36

swyrik

3344 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tharaka Devinda Over a year ago

This worked for me. I didn't even use native query.

Toni Over a year ago

This doesn't work with native queries.

swyrik Over a year ago

Yeah true @birca

ilinykhma · Accepted Answer · 2019-09-15 07:59:14Z

I'm not sure there is a Spring Data solution for native query, but you can use JPA ConstructorResult:

@Entity
@NamedNativeQuery(
        name="Person.findDistinctNativeQuery",
        query="select distinct first_name as firstName, last_name as lastName from person",
        resultSetMapping="PersonMapping"
)
@SqlResultSetMapping(name="PersonMapping",
        classes={
                @ConstructorResult(targetClass=PersonDTO.class, columns={
                        @ColumnResult(name="firstName", type=String.class),
                        @ColumnResult(name="lastName", type=String.class)
                })
        })
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(name = "Person.findDistinctNativeQuery", nativeQuery = true)
    List<PersonDTO> findDistinctNativeQuery();

}

Or you can return Object[] from findDistinctNativeQuery() and then manually create PersonDTO.

With HQL you can use class based projections as well, the only condition PersonDTO must have the all argument constructor which parameter names must match properties of the root entity class:

public interface PersonRepository extends Repository<Person, Long> {

    ...

    List<PersonDTO> findDistinct();

}

If PersonDTO properties doesn't match the base entity properties, than HQL dynamic instantiation may be used. Again, this doesn't work with native query:

public interface PersonRepository extends Repository<Person, Long> {

    ...

    @Query("select new com.x.PersonDTO(firstName, lastName) from Person")
    List<PersonDTO> findDistinct();

}

Jay Ehsaniara · Accepted Answer · 2020-09-28 01:53:01Z

5

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

answered Sep 28, 2020 at 1:53

Jay Ehsaniara

1,5591 gold badge17 silver badges26 bronze badges

Comments

Konstantin Triger · Accepted Answer · 2019-09-15 05:38:59Z

1

This can be done with FluentJPA:

default List<PersonDTO> findDistinctQuery() {
    FluentQuery query = FluentJPA.SQL((Person p) -> {
        SELECT(DISTINCT(p.getFirstName(), p.getLastName()));
        FROM(p);
    });
    return query.createQuery(getEntityManager(), PersonDTO.class).getResultList();
}

Note, that you must annotate PersonDTO with @Data and not @Value. More details about JPA Repositories integration.

answered Sep 15, 2019 at 5:38

Konstantin Triger

1,79915 silver badges13 bronze badges

Comments

Vikas · Accepted Answer · 2019-09-13 15:11:05Z

0

You need to add constructor with fields name same as root entity. So your DTO would be,

public class PersonDTO {
  private String firstName;
  private String lastName;
  // constructor , getters setters, equals(…) and hashCode() implementations 
}

You can simplify DTO by using Lombok’s @Value annotation,

 @Value
 class PersonDTO {
    String firstname, lastname;
 }

Check more details here

answered Sep 13, 2019 at 15:11

Vikas

7,2214 gold badges30 silver badges46 bronze badges

6 Comments

Boris Over a year ago

@Code_Mode, this is required for findDistinct to work. But custom queries still don't work.

Boris Over a year ago

Because this doesn't answer the question. But OK, I removed down-vote.

Boris Over a year ago

Updated the description to make it more clear that DTO satisfies all requirements for class-based projection.

Boris Over a year ago

@Code_Mode, I already removed my down-vote, please also do the same.

Vikas Over a year ago

@Boris not mine

|

Collectives™ on Stack Overflow

spring data jpa - class based projections with custom query

5 Answers 5

3 Comments

Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related