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
?
Login
FAQ
Search
