JPA一对多的SQL语法的写法

最新推荐文章于 2023-04-28 10:58:02 发布

最新推荐文章于 2023-04-28 10:58:02 发布 · 536 阅读

文章标签：

#SQL #JPA

JPA 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了在处理一个Scene对多个SceneVideo的关系时如何正确地构建SQL查询语句。通过对比错误与正确的写法，阐述了在使用内连接进行查询时如何正确指定条件。

1.前提Scene.java：一个Scene对多个SceneVideo
@OneToMany(mappedBy = "scene", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private Set<SceneVideo> sceneVideoCollection = new HashSet<SceneVideo>();

2.SQL语句不正确的写法：
String strQuery = "select distinct s from Scene s ";
if (quality != null && !"".equals(quality)){
   strWhere = " where s.sceneVideoCollection.videoUrl like '%"+ quality +"%' or s.sceneVideoCollection.playlistFile like '%"+ quality +"%'";
}
错误的原因是：一个Scene对多个SceneVideo,而这里用sceneVideoCollection.videoUrl肯定是不对啦，这里是对多个sceneVideoCollection的，那它的videoUrl就对不少号啦！！！

3.正确的SQL写法：
String strQuery = "select distinct s from Scene s inner join s.sceneVideoCollection sv";
if (quality != null && !"".equals(quality)){
   strWhere = " where sv.videoUrl like '%"+ quality +"%' or sv.playlistFile like '%"+ quality +"%'";
}