-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Sun Dec 24, 2017 10:02 pm 
Newbie

Joined: Sun Dec 24, 2017 8:15 pm
Posts: 3
This is a duplicate of this question on StackOverflow:

I have the following entities:

Code:
    @Entity
    public class CityExpert {
        @Id
        private long id;
   
        @OneToOne
        private User user;
   
        @OneToMany(mappedBy = "cityExpert")
        private List<CityExpertDocument> documents;
   
        // Lots of other fields...
    }
   
    @Entity
    public class CityExpertDocument {
   
        @Id
        private long id;
   
        @ManyToOne
        private CityExpert cityExpert;
   
        // Lots of other fields...
    }
   
    @Entity
    public class User {
        @Id
        private long id;
   
        private String name;
   
        private String email;
   
        // Lots of other fields...
    }


I have the following HQL query, in which I select a subset of
Code:
CityExpert
s:

Code:
    "select " +
            "e " +
    "from " +
            "CityExpert e " +
    "where " +
            "( (lower(e.user.name) like concat('%', lower(?1), '%') or e.user.name is null) or ?1 = '' ) " +
            "and " +
            "( (lower(e.user.phone) like concat('%', lower(?2), '%') or e.user.phone is null) or ?2 = '' ) "


However, since there are too many fields in `CityExpert`, I don't want to select all fields. Hence, I have changed the query as follows:

Code:
    "select " +
            "e.user.name, " +
            "e.user.email, " +
            "e.documents " +
    "from " +
            "CityExpert e " +
    "where " +
            "( (lower(e.user.name) like concat('%', lower(?1), '%') or e.user.name is null) or ?1 = '' ) " +
            "and " +
            "( (lower(e.user.phone) like concat('%', lower(?2), '%') or e.user.phone is null) or ?2 = '' ) "


However, apparently we cannot select a one-to-many field in an entity like that, because I am getting a
Code:
MySQLSyntaxErrorException
with the preceding query (refer to this question). Hence, I have changed the query to following:

Code:
    "select " +
            "e.user.name, " +
            "e.user.email, " +
            "d " +
    "from " +
            "CityExpert e " +
            "left join " +
            "e.documents d" +
    "where " +
            "( (lower(e.user.name) like concat('%', lower(?1), '%') or e.user.name is null) or ?1 = '' ) " +
            "and " +
            "( (lower(e.user.phone) like concat('%', lower(?2), '%') or e.user.phone is null) or ?2 = '' ) "


However, this time the result becomes a
Code:
List<Object[]>
, instead of
Code:
List<CityExpert>
.

I have created the following DTO:

Code:
    public class CityExpertDTO {
   
        private String name;
        private String email;
        private List<CityExpertDocument> documents;
   
    }


However, I don't know how I should map the result returned by Hibernate to
Code:
List<CityExpertDTO>
. I mean, I can do this manually but surely there must be an automated solution provided by Hibernate.

I am using Spring Data JPA and using the HQL as follows:

Code:
    public interface CityExpertRepository extends JpaRepository<CityExpert, Long> {
   
        @Query(
                "select " +
                        "e " +
                "from " +
                        "CityExpert e " +
                "where " +
                        "( (lower(e.user.name) like concat('%', lower(?1), '%') or e.user.name is null) or ?1 = '' ) " +
                        "and " +
                        "( (lower(e.user.phone) like concat('%', lower(?2), '%') or e.user.phone is null) or ?2 = '' ) "
        )
        Set<CityExpert> findUsingNameAndPhoneNumber(String name,
                                                    String phoneNumber);
   
    }


How can I map the result to
Code:
CityExpertDTO
?


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Mon Dec 25, 2017 1:13 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
The @OneToMany association can only be mapped to an entity, not a DTO. However, you can have multiple entities mapped on the same table.

For DTO projections, read this article.

For mapping subentities, read this one.


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Mon Dec 25, 2017 4:06 pm 
Newbie

Joined: Sun Dec 24, 2017 8:15 pm
Posts: 3
@vlad So, isn't there a possibility to return a list of objects, where each object containing:

* String name;
* String email;
* List<CityExpertDocument> documents;

It doesn't have to be a list of DTOs if mapping to DTO is not possible. It can be just list of CityExperts where only a subset of the fields of each CityExpert is populated.

I have read the links you have provided but couldn't understand how I should implement this.


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Mon Dec 25, 2017 4:47 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Yes, you can. But via a query, not a mapping. Hibernate ResultTransformer is great for this kind of task.


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Mon Dec 25, 2017 7:12 pm 
Newbie

Joined: Sun Dec 24, 2017 8:15 pm
Posts: 3
@vlad Thanks Vlad! One last thing: I am using the @Query annotation on a Spring Data JPA repository. Your blog post explains using ResultTransformer with createQuery.

Is there a clean way to use ResultTransformer with @Query annotation of Spring Data JPA, or should I change my approach?


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Mon Dec 25, 2017 8:28 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Probably you should change your approach because Spring Data JPA does not provide support for Hibernate specific features


Top
 Profile  
 
 Post subject: Re: How to map @OneToMany field into List<DTO> from HQL result?
PostPosted: Thu Dec 28, 2017 12:42 pm 
Beginner
Beginner

Joined: Sat May 21, 2011 7:40 am
Posts: 23
You might want to have a look at Blaze-Persistence Entity Views which are specifcially made for such projections and have a great Spring Data integration.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.