hibernate 一对多 取多方数据重复问题，FetchMode.JOIN、FetchMode.SELECT、FetchMode.SUBSELECT区别

问题描述：稿件附件表数据时出现多条重复数据。

介绍：

表：稿件实体Manuscripts （数据库表MANUSCRIPTS），稿件附件实体ManuscriptsAtt（表MANUSCRIPTS_ATT），稿件审核实体：ManuscriptsQuotes

表关系：稿件与稿件附件 一对多；稿件与稿件审核一对多；

代码：

稿件实体 Manuscripts ：

 

@Entity
@Table(name = "MANUSCRIPTS")

public class Manuscripts implements Serializable {

//主键
@Id
@Column(length = 38,name = "ID")
private String id;

......

@OneToMany(targetEntity = ManuscriptsAtt.class,fetch=FetchType.EAGER)
@JoinColumn(name="MANUSCRIPTS_ID")
@Fetch(FetchMode.SUBSELECT)
private Set<ManuscriptsAtt> manuscriptsAtts;

@OneToMany(targetEntity = ManuscriptsQuotes.class,fetch=FetchType.EAGER)
@JoinColumn(name="MANUSCRIPTS_ID")
@Fetch(FetchMode.SUBSELECT)
private Set<ManuscriptsQuotes> manuscriptsQuotes;

......

}

稿件附件实体： 

 

@Entity
@Table(name = "MANUSCRIPTS_ATT")

public class ManuscriptsAtt implements Serializable {

/**
* 
*/
private static final long serialVersionUID = 1L;

//主键
@Id
@Column(length = 38, name = "ID")
private String id;

.....
//父类
@ManyToOne
@JoinColumn(name = "MANUSCRIPTS_ID")
private Manuscripts manuscripts;

.....}

稿件审核实体：

@Entity
@Table(name = "MANUSCRIPTS_QUOTES")
public class ManuscriptsQuotes implements Serializable {
    
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    //主键
    @Id
    @Column(length = 38,name = "ID")
    private String id;

    //父类
     @ManyToOne
     @JoinColumn(name = "MANUSCRIPTS_ID")
    private Manuscripts manuscripts;
...
}

 

稿件实体 Manuscripts 中oneToMany 下Fetch设置不同结果不同

@Fetch(FetchMode.JOIN) 会使用left join查询 只产生一条sql语句
@Fetch(FetchMode.SELECT) 会产生N+1条sql语句
@Fetch(FetchMode.SUBSELECT) 产生两条sql语句 第二条语句使用id in (…..)查询出所有关联的数据

下面介绍列出附件数据sql和会出现结果

@Fetch(FetchMode.JOIN) 后sql语句和结果(会使用left join查询 只产生一条sql语句)

Hibernate: 
    select
        this_.id as id1_13_1_,
        this_.file_address as file_add6_13_1_,
        this_.file_name as file_nam7_13_1_,
        this_.manuscripts_id as manuscr11_13_1_,
        this_.type as type9_13_1_, 
        manusc1_.id as id1_11_0_,
        manusc1_.content as content4_11_0_,
        manusc1_.create_time as create_t5_11_0_,
        manusc1_.title as title21_11_0_,
        manusc1_.type as type22_11_0_ 
        manuscript4_.manuscripts_id as manuscr11_11_5_,
        manuscript4_.id as id1_13_5_,
        manuscript4_.id as id1_13_1_,
        manuscript4_.file_address as file_add6_13_1_,
        manuscript4_.file_name as file_nam7_13_1_,
        manuscript4_.manuscripts_id as manuscr11_13_1_,
        manuscript4_.type as type9_13_1_,
        manuscript5_.manuscripts_id as manuscri7_11_6_,
        manuscript5_.id as id1_14_6_,
        manuscript5_.id as id1_14_2_,
        manuscript5_.department as departme2_14_2_,
        manuscript5_.department_name as departme3_14_2_,
        manuscript5_.editor_person as editor_p4_14_2_,
        manuscript5_.editor_person_name as editor_p5_14_2_,
        manuscript5_.manuscripts_id as manuscri7_14_2_,
        manuscript5_.quote_time as quote_ti6_14_2_ 
    from
        manuscripts_att this_ 
    inner join
        manuscripts manusc1_ 
            on this_.manuscripts_id=manusc1_.id 
    left outer join
        manuscripts_att manuscript4_ 
            on manusc1_.id=manuscript4_.manuscripts_id 
    left outer join
        manuscripts_quotes manuscript5_ 
            on manusc1_.id=manuscript5_.manuscripts_id 
    where
        this_.type=? 
    order by
        this_.createtime desc

 FetchMode.JOIN  ：会使用left join查询 只产生一条sql语句 ，结果数据重复

@Fetch(FetchMode.SELECT) 后sql语句和结果(会产生N+1条sql语句)

Hibernate: 
    select
       this_.id as id1_13_1_,
        this_.file_address as file_add6_13_1_,
        this_.file_name as file_nam7_13_1_,
        this_.manuscripts_id as manuscr11_13_1_,
        this_.type as type9_13_1_,   
        manusc1_.id as id1_11_0_,
        manusc1_.content as content4_11_0_,
        manusc1_.create_time as create_t5_11_0_,
        manusc1_.title as title21_11_0_,
        manusc1_.type as type22_11_0_ 
    from
        manuscripts_att this_ 
    inner join
        manuscripts manusc1_ 
            on this_.manuscripts_id=manusc1_.id 
    where
        this_.type=? 
    order by
        this_.createtime desc
Hibernate: 
    select
        manuscript0_.manuscripts_id as manuscri7_11_0_,
        manuscript0_.id as id1_14_0_,
        manuscript0_.id as id1_14_1_,
        manuscript0_.department as departme2_14_1_,
        manuscript0_.department_name as departme3_14_1_,
        manuscript0_.editor_person as editor_p4_14_1_,
        manuscript0_.editor_person_name as editor_p5_14_1_,
        manuscript0_.manuscripts_id as manuscri7_14_1_,
        manuscript0_.quote_time as quote_ti6_14_1_ 
    from
        manuscripts_quotes manuscript0_ 
    where
        manuscript0_.manuscripts_id=?
Hibernate: 
    select
        manuscript0_.manuscripts_id as manuscr11_11_0_,
        manuscript0_.id as id1_13_0_,
        manuscript0_.id as id1_13_1_,
        manuscript0_.createtime as createti2_13_1_,
        manuscript0_.create_person as create_p3_13_1_,
        manuscript0_.create_person_name as create_p4_13_1_,
        manuscript0_.download_count as download5_13_1_,
        manuscript0_.file_address as file_add6_13_1_,
        manuscript0_.file_name as file_nam7_13_1_,
        manuscript0_.file_suff as file_suf8_13_1_,
        manuscript0_.manuscripts_id as manuscr11_13_1_,
        manuscript0_.type as type9_13_1_,
        manuscript0_.view_count as view_co10_13_1_ 
    from
        manuscripts_att manuscript0_ 
    where
        manuscript0_.manuscripts_id=?
.......................

@Fetch(FetchMode.SELECT) 会产生N+1条sql语句 ,结果正确，但是效率低

 

 

@Fetch(FetchMode.SUBSELECT) 产生两条sql语句 第二条语句使用id in (…..)查询出所有关联的数据

Hibernate: 
    select
        this_.id as id1_13_1_,
        this_.file_address as file_add6_13_1_,
        this_.file_name as file_nam7_13_1_,
        this_.manuscripts_id as manuscr11_13_1_,
        this_.type as type9_13_1_,   
        manusc1_.id as id1_11_0_,
        manusc1_.content as content4_11_0_,
        manusc1_.create_time as create_t5_11_0_,
        manusc1_.title as title21_11_0_,
        manusc1_.type as type22_11_0_ 
    from
        manuscripts_att this_ 
    inner join
        manuscripts manusc1_ 
            on this_.manuscripts_id=manusc1_.id 
    where
        this_.type=? 
    order by
        this_.createtime desc
Hibernate: 
    select
        manuscript0_.manuscripts_id as manuscri7_11_1_,
        manuscript0_.id as id1_14_1_,
        manuscript0_.id as id1_14_0_,
        manuscript0_.department as departme2_14_0_,
        manuscript0_.department_name as departme3_14_0_,
        manuscript0_.editor_person as editor_p4_14_0_,
        manuscript0_.editor_person_name as editor_p5_14_0_,
        manuscript0_.manuscripts_id as manuscri7_14_0_,
        manuscript0_.quote_time as quote_ti6_14_0_ 
    from
        manuscripts_quotes manuscript0_ 
    where
        manuscript0_.manuscripts_id in (
            select
                manusc1_.id 
            from
                manuscripts_att this_ 
            inner join
                manuscripts manusc1_ 
                    on this_.manuscripts_id=manusc1_.id 
            where
                this_.type=? 
        )

FetchMode.SUBSELECT： 产生两条sql语句 第二条语句使用id in (…..)查询出所有关联的数据 结果正确，效率相对高