-->
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.  [ 2 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: 1
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: 1628
Location: Romania
In order to map a @OneToMany association to a DTO projection, you have to use the Hibernate ResultTransformer.

This article explains in great details on how to achieve this goal.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 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.