How to map a projection query to a DTO which has a @ManyToMany @JoinTable property by JPQL

Viewed 612

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.

0 Answers
Related