Hibernate -Manytomany子选择甚至左JON FITCH

Question

伙计们！我有两个类别的两个属性。

public class ExhibitorList {
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="exhibitor_layouts",
    joinColumns=@JoinColumn(name="exhibitor_list_id"),
    inverseJoinColumns=@JoinColumn(name="layout_id"))
private List<Layout> layouts = new ArrayList<>();
}

和

public class Layout {
@ManyToMany(mappedBy="layouts",fetch=FetchType.EAGER)
private List<ExhibitorList> exhibitor = new ArrayList<>();
}

我正在使用HPQ获取实体。

final String FIND_ALL_JOIN = "SELECT ex from ExhibitorList as ex LEFT join FETCH ex.layouts order by ex.id";
@Query(FIND_ALL_JOIN)
List<ExhibitorList> findAllJoin();

问题在于，此查询可以为任何有布局的参展商获得子选择（目前我有6个具有布局的参展商。

Hibernate: select exhibitorl0_.id as id1_6_0_, layout2_.id as id1_8_1_, exhibitorl0_.catalogue_number as catalogu2_6_0_, exhibitorl0_.exhibitor_name as exhibito3_6_0_, exhibitorl0_.exhibitor_price as exhibito4_6_0_, exhibitorl0_.oracle_number as oracle_n5_6_0_, layout2_.created as created2_8_1_, layout2_.name as name3_8_1_, layout2_.status as status4_8_1_, layouts1_.exhibitor_list_id as exhibito1_5_0__, layouts1_.layout_id as layout_i2_5_0__ from exhibitor_list exhibitorl0_ left outer join exhibitor_layouts layouts1_ on exhibitorl0_.id=layouts1_.exhibitor_list_id left outer join layout layout2_ on layouts1_.layout_id=layout2_.id order by exhibitorl0_.id
Hibernate: select exhibitor0_.layout_id as layout_i2_5_0_, exhibitor0_.exhibitor_list_id as exhibito1_5_0_, exhibitorl1_.id as id1_6_1_, exhibitorl1_.catalogue_number as catalogu2_6_1_, exhibitorl1_.exhibitor_name as exhibito3_6_1_, exhibitorl1_.exhibitor_price as exhibito4_6_1_, exhibitorl1_.oracle_number as oracle_n5_6_1_ from exhibitor_layouts exhibitor0_ inner join exhibitor_list exhibitorl1_ on exhibitor0_.exhibitor_list_id=exhibitorl1_.id where exhibitor0_.layout_id=?
Hibernate: select exhibitor0_.layout_id as layout_i2_5_0_, exhibitor0_.exhibitor_list_id as exhibito1_5_0_, exhibitorl1_.id as id1_6_1_, exhibitorl1_.catalogue_number as catalogu2_6_1_, exhibitorl1_.exhibitor_name as exhibito3_6_1_, exhibitorl1_.exhibitor_price as exhibito4_6_1_, exhibitorl1_.oracle_number as oracle_n5_6_1_ from exhibitor_layouts exhibitor0_ inner join exhibitor_list exhibitorl1_ on exhibitor0_.exhibitor_list_id=exhibitorl1_.id where exhibitor0_.layout_id=?
Hibernate: select exhibitor0_.layout_id as layout_i2_5_0_, exhibitor0_.exhibitor_list_id as exhibito1_5_0_, exhibitorl1_.id as id1_6_1_, exhibitorl1_.catalogue_number as catalogu2_6_1_, exhibitorl1_.exhibitor_name as exhibito3_6_1_, exhibitorl1_.exhibitor_price as exhibito4_6_1_, exhibitorl1_.oracle_number as oracle_n5_6_1_ from exhibitor_layouts exhibitor0_ inner join exhibitor_list exhibitorl1_ on exhibitor0_.exhibitor_list_id=exhibitorl1_.id where exhibitor0_.layout_id=?
Hibernate: select exhibitor0_.layout_id as layout_i2_5_0_, exhibitor0_.exhibitor_list_id as exhibito1_5_0_, exhibitorl1_.id as id1_6_1_, exhibitorl1_.catalogue_number as catalogu2_6_1_, exhibitorl1_.exhibitor_name as exhibito3_6_1_, exhibitorl1_.exhibitor_price as exhibito4_6_1_, exhibitorl1_.oracle_number as oracle_n5_6_1_ from exhibitor_layouts exhibitor0_ inner join exhibitor_list exhibitorl1_ on exhibitor0_.exhibitor_list_id=exhibitorl1_.id where exhibitor0_.layout_id=?
Hibernate: select exhibitor0_.layout_id as layout_i2_5_0_, exhibitor0_.exhibitor_list_id as exhibito1_5_0_, exhibitorl1_.id as id1_6_1_, exhibitorl1_.catalogue_number as catalogu2_6_1_, exhibitorl1_.exhibitor_name as exhibito3_6_1_, exhibitorl1_.exhibitor_price as exhibito4_6_1_, exhibitorl1_.oracle_number as oracle_n5_6_1_ from exhibitor_layouts exhibitor0_ inner join exhibitor_list exhibitorl1_ on exhibitor0_.exhibitor_list_id=exhibitorl1_.id where exhibitor0_.layout_id=?

如果我使用findall（）使用计划弹簧数据;例如，我得到n 1问题。

如何使所有参展商都有一个有选择的布局

Answer 1

尝试：

 select ex from ExhibitorList ex order by ex.id

您不需要加入布局。您有很多tomany关系。

使用查询的示例：

Query q = getSession().createQuery("select ex from ExhibitorList ex order by ex.id" ); 
return (List<ExhibitorList>) q.list();

Answer 2

如果要选择所有具有Layouts的Exhibitors，换句话说，尚未 Layouts的null List，则可以在where条件下使用is not null和is not empty：

final String FIND_ALL_JOIN = "SELECT ex from ExhibitorList as ex where ( ex.layouts is not null &&  ex.layouts is not empty) order by ex.id";

查看表达式 HQL参考以获取更多详细信息。