Hibernate select sub object into interface

Question

To collect only the data i need i use following query for object Declaration:

        @Query(value = "select d.id as id "
            + " , d.statusChanged as statusChanged "
            + " from Declaration d ", 
            countQuery="select count(id) from Declaration")
    Page<DeclarationIDTO> findAllDeclarationListIDTO(Pageable pageable);

DeclarationIDTO has a getId() and a getStatusChanged().

This works.

Now i want to add the id of the freelancer like this:

    @Query(value = "select d.id as id "
            + " , d.statusChanged as statusChanged "
            + " , f.id as 'freelancer.id' "
            + " from Declaration d join d.freelancer f", 
            countQuery="select count(id) from Declaration")
    Page<DeclarationIDTO> findAllDeclarationListIDTO(Pageable pageable);

The DeclarationIDTO has a getFreelancer() which has a getId() but i get an error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting IDENT, found ''freelancer.id'' near line 1, column 66 [select d.id as id  , d.statusChanged as statusChanged  , f.id as 'freelancer.id'  from nl.tibi.sbys.domain.Declaration d join d.freelancer f]

Any idea how to work with object chain?

Possible work arounds:

1) flatten interface:

@Query(value = "select d.id as id "
        + " , d.statusChanged as statusChanged "
        + " , f.id as 'freelancer_id' "
        + " from Declaration d join d.freelancer f", 
        countQuery="select count(id) from Declaration")
Page<DeclarationIDTO> findAllDeclarationListIDTO(Pageable pageable);

DeclarationIDTO will have getId(), getStatusChanged() and getFreelancer_id()

Downside is the interface will have to copy all needed freelancer fields and i have to use a mapper to map the freelancer_id to a DTO object with a freelancer.id

2) work with constructor instead of interface:

@Query(value = "select new DeclarationDTO(d.id "
        + " , d.statusChanged "
        + " , f.id) "
        + " from Declaration d join d.freelancer f", 
        countQuery="select count(id) from Declaration")
Page<DeclarationDTO> findAllDeclarationListIDTO(Pageable pageable);

Downside is i will need many constructors for different pages or need to select null values cluttering my query

3) Multiple queries per object, this is a performance hit and a lot of work.

4) Select full sub objects:

@Query(value = "select d.id as id "
    + " , d.statusChanged as statusChanged "
    + " , f as freelancer "
    + " from Declaration d join d.freelancer f", 
    countQuery="select count(id) from Declaration")

Page findAllDeclarationListIDTO(Pageable pageable);

DeclarationIDTO has a getId() getStatusChanged() and getFreelancer()

This works there are still to many data collected which is a downside on performance.

If there is a simple solution for getting f.id it would solve all downsides.

There seems an answer here:

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

When time i will look into it.

Maciej Kowalski · Accepted Answer · 2019-02-15 10:22:24Z

1

1) Your DeclarationIDTO must have constructor with id and statusChange params, in that order

2) Try adding the new operator in the query with fully qualified name:

"select new my.package.DeclarationIDTO(d.id as id, d.statusChanged as statusChanged "
    + " , f.id as 'freelancer.id' "
    + " from Declaration d join d.freelancer f",

answered Feb 15, 2019 at 10:22

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

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

7 Comments

tibi Over a year ago

thanks but the first example is working without any constructor. if I would go the constructor route i need a lot of constructors for every situation, so i rather avoid this. the thing is i can select into the DeclarationIDTO fine, but not its sub object FreelancerIDTO

Maciej Kowalski Over a year ago

just create a freelancerId in the dto

tibi Over a year ago

if this was the only field that would be possible but i will need many more fields and that would be duplication fields. I hope there is a way but maybe not to not only use interface for direct fields but also for chained fields. But it could be that is not supported by hibernate/jpa

Maciej Kowalski Over a year ago

are you using spring data jpa?

Maciej Kowalski Over a year ago

have you declared DeclarationIDTO and FreelanceDTO as interfaces?

|

Collectives™ on Stack Overflow

Hibernate select sub object into interface

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related