mysql查询到jpql的多连接springboot+jpa

5rgfhyps  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(304)

我有一个mysql原生查询,如何将相同的mysql转换成jpql,就像我在项目中使用jpa和springboot一样。
以下是mysql查询,运行良好:

select title0_.title_id as title_id1_15_, title0_.title_categorystudio_id as title_ca3_15_, title0_.title_name as title_na2_15_ 
from title title0_ 
    inner join title_language languages1_ on title0_.title_id=languages1_.titlelanguage_titleid 
    inner join language language2_ on languages1_.titlelanguage_languageid=language2_.language_id 
    inner join title_genre genres3_ on title0_.title_id=genres3_.titlegenre_titleid 
    inner join genrescategory genrescate4_ on genres3_.titlegenre_genreid=genrescate4_.genrescategory_id 
    left join (movie movies5_  inner join studioname studio8_ on movies5_.movie_studio_id=studio8_.studioname_id) on title0_.title_id=movies5_.movie_titleid 
    left join (tvshow tvshows6_ inner join studioname studio9_ on tvshows6_.tv_studio_id=studio9_.studioname_id) on title0_.title_id=tvshows6_.tv_titleid 
    left join (kid kids7_ inner join studioname studio10_ on kids7_.kid_studio_id=studio10_.studioname_id) on title0_.title_id=kids7_.kid_titleid 
    inner join categorystudio categoryof11_ on title0_.title_categorystudio_id=categoryof11_.categorystudio_id 
where 1=1
and (language2_.language_name in ('english')) 
 and (genrescate4_.genrescategory_name in ('action')) 
 and (categoryof11_.categorystudio_title in ('tv')) 
 and (studio8_.studioname_name in ('sony') or studio9_.studioname_name in ('sony') or studio10_.studioname_name in ('sony'))

我使用的是一个不起作用的jpql查询,

@Query("select t from Title t " +
            "join t.languages l " +
            "join t.genres g " +
            "left join t.movies m join m.studio ms " +
            "left join t.tvShows tv join tv.studio tvs " +
            "left join t.kids k join k.studio ks " +
            "join t.categorystudio c  where l.languageName IN (:languages) AND g.name IN (:genres) AND c.categorystudio_title IN (:categories)  AND (ms.name IN (:studios) OR tvs.name in (:studios) OR ks.name IN (:studios))")

在日志中,jpql查询显示为:

select title0_.title_id as title_id1_15_, title0_.title_categorystudio_id as title_ca3_15_, title0_.title_name as title_na2_15_ from title title0_ 
    inner join title_language languages1_ on title0_.title_id=languages1_.titlelanguage_titleid 
    inner join language language2_ on languages1_.titlelanguage_languageid=language2_.language_id 
    inner join title_genre genres3_ on title0_.title_id=genres3_.titlegenre_titleid 
    inner join genrescategory genrescate4_ on genres3_.titlegenre_genreid=genrescate4_.genrescategory_id 
    left outer join movie movies5_ on title0_.title_id=movies5_.movie_titleid inner join studioname studio6_ on movies5_.movie_studio_id=studio6_.studioname_id 
    left outer join tvshow tvshows7_ on title0_.title_id=tvshows7_.tv_titleid inner join studioname studio8_ on tvshows7_.tv_studio_id=studio8_.studioname_id
    left outer join kid kids9_ on title0_.title_id=kids9_.kid_titleid inner join studioname studio10_ on kids9_.kid_studio_id=studio10_.studioname_id 
    inner join categorystudio categoryof11_ on title0_.title_categorystudio_id=categoryof11_.categorystudio_id 
where (language2_.language_name in (?)) and (genrescate4_.genrescategory_name in (?)) and (categoryof11_.categorystudio_title in (?)) and (studio6_.studioname_name in (?) or studio8_.studioname_name in (?) or studio10_.studioname_name in (?))

提前谢谢。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题