问题描述:稿件附件表数据时出现多条重复数据。
介绍:
表:稿件实体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 (…..)查询出所有关联的数据 结果正确,效率相对高