I have an Blog entity like:
@Entity
class Blog{
@Id
private Long id;
private String titie;
private String content;
@ManyToMany
@JoinTable(
name = "blog_tag_association",
joinColumns = @JoinColumn(name = "blog_id"),
inverseJoinColumns = @JoinColumn(name = "tag_id")
)
private Set<Tag> tags = new LinkedHashSet<>();
// ...
}
As you can see, i use a separate table to represent the relationship between Blog and Tag.
I've read this post and write my custom JPQL query to map my query result to DTO:
class BlogSummaryDTO{
private Long id;
private String title;
private Set<Tag> tags;
public BlogSummaryDTO(Long id, String title, Set<Tag> tags){
this.id = id;
this.title = title;
this.tags = tags;
}
}
And my custom JPQL like:
String query = "SELECT new com.okatu.rgan.blog.model.BlogSummaryDTO(" +
"b.id, b.title, " +
"b.tags) FROM Blog b";
It cannot work, the terminal told me that:
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_2_0_ from blog blog0_ inner join blog_tag_association tags1_ on blog0_.id' at line 1
The sql it generate was like:
Hibernate:
select
blog0_.id as col_0_0_,
blog0_.title as col_1_0_,
. as col_2_0_
from
blog blog0_
inner join
blog_tag_association tags1_
on blog0_.id=tags1_.blog_id
inner join
tag tag2_
on tags1_.tag_id=tag2_.id
where
blog0_.title like ?
or blog0_.title like ? limit ?
If i remove the tags property from the JPQL and DTO's constructor's parameter list, it works well.
So how should i write my custom JPQL to construct the DTO with @JoinTable property?
I've also observed the jpa-generated sql when do the BlogRepository::findById, it use a separate native sql to retrieve the Tag.
Hibernate:
select
blog0_.id as id1_0_0_,
blog0_.content as content2_0_0_,
blog0_.created_time as created_3_0_0_,
blog0_.modified_time as modified4_0_0_,
blog0_.summary as summary5_0_0_,
blog0_.title as title6_0_0_,
blog0_.author_id as author_i9_0_0_,
user1_.id as id1_5_1_,
user1_.created_time as created_2_5_1_,
user1_.username as username7_5_1_,
from
blog blog0_
left outer join
user user1_
on blog0_.author_id=user1_.id
where
blog0_.id=?
Hibernate:
select
tags0_.blog_id as blog_id1_1_0_,
tags0_.tag_id as tag_id2_1_0_,
tag1_.id as id1_4_1_,
tag1_.description as descript2_4_1_,
tag1_.title as title3_4_1_
from
blog_tag_association tags0_
inner join
tag tag1_
on tags0_.tag_id=tag1_.id
where
tags0_.blog_id=?
But i don't know what's the JPQL it generated.
SQLquery can return plain result set only.JPQLquery result can be mapped to entities. As fordtoI'd suggest you to get data you need fromTagside asList<Object[]>and fillBlogSummaryDTOfrom code. By the way you should useSet<TagDto>as field ofBlogSummaryDTO