JPA一对多的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 +"%'";
}