Mapping @Query with multiple column selection to an Java object in a @Repository - is it possible out of the box?

Question

Is it possible to map the results of a Hibernate @Query like this (in a @Repository interface extending @JpaRepository):

@Query("select u.id, u.email, u.status from user u")
public SimpleUserDTO getAllUsersSimpleData();

directly to a Java object like this:

public class SimpleUserDTO {
    private Long id;
    private String email;
    private String status;
}

What I know, is that doing something like this:

Query query = session.createQuery("select u.id, u.email, u.status from user u");

the result can be extracted to a List<Object[]>:

List<Object[]> users = (List<Object[]>) query.list();

But is it possible to map it directly to a List<SimpleUserDTO> without writing additional method that will map the values to SimpleUserDTO?

Have you tried Hibernate Projections? Or the Interface-Based Projections of Spring? — MPhil
– MPhil, Commented Apr 1, 2020 at 7:14

Simon Martinelli · Accepted Answer · 2020-04-01 07:18:31Z

3

You have two options.

Option 1: Constructor Expression:

@Query("select new <insert_package_here>.SimpleUserDTO(u.id, u.email, u.status) from user u")
public List<SimpleUserDTO> getAllUsersSimpleData();

Option 2: Use Interface projection

Turn your DTO into an interface and you can use it without @Query annotation

public List<SimpleUserDTO> getAllUsersSimpleData();

Please find out more about projections and Spring Data JPA in the documentation:

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

answered Apr 1, 2020 at 7:18

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

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

3 Comments

hc0re Over a year ago

I have simplified the code, but in reality, the query looks like this: select u.id, u.email, u.status, a.postalCode from user u join address a on (u.id = a.user_id) - so it includes another table. Will this also work?

Simon Martinelli Over a year ago

Why don't you try?

hc0re Over a year ago

Trying now the solution using projection ;)

Collectives™ on Stack Overflow

Mapping @Query with multiple column selection to an Java object in a @Repository - is it possible out of the box?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related