FetchMode和FetchType

原文：http://www.jianshu.com/p/23bd82a7b96e

Entity:
Entity City and Hotel, One-to-Many 双向连接.
@Entitypublic class City {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long id;    @Column(nullable = false)    private String name;    @Column(nullable = false)    private String country;    @OneToMany(mappedBy="city")    private Set<Hotel> hotles;    ...}
@Entitypublic class Hotel {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long id;    @ManyToOne(optional = false)    @NaturalId    private City city;    @Column(nullable = false)    @NaturalId    private String name;    @Column(nullable = false)    private String address;    @OneToMany(fetch = FetchType.LAZY, mappedBy = "hotel")    private Set<Review> reviews;    ...}

Data:
insert into city(country, name) values ('Australia', 'Brisbane')insert into city(country, name) values ('Canada', 'Montreal')insert into city(country, name) values ('Australia', 'Melbourne')insert into city(country, name) values ('Israel', 'Tel Aviv')insert into hotel(city_id, name, address) values (1, 'Hotel_A', 'Street_A')insert into hotel(city_id, name, address) values (1, 'Hotel_B', 'Street_A')insert into hotel(city_id, name, address) values (1, 'Hotel_C', 'Street_C')insert into hotel(city_id, name, address) values (2, 'Hotel_D', 'Street_D')insert into hotel(city_id, name, address) values (2, 'Hotel_E', 'Street_E')insert into hotel(city_id, name, address) values (3, 'Hotel_F', 'Street_F')insert into hotel(city_id, name, address) values (3, 'Hotel_G', 'Street_G')insert into hotel(city_id, name, address) values (3, 'Hotel_H', 'Street_H')insert into hotel(city_id, name, address) values (3, 'Hotel_I', 'Street_I')insert into hotel(city_id, name, address) values (3, 'Hotel_J', 'Street_J')insert into hotel(city_id, name, address) values (4, 'Hotel_K', 'Street_K')

Code causes N+1:
criteria.list()发送一条sql获取所有City, 循环执行city.getHotles().size()时发送N条sql获取Hotel
    Session session = sessionFactory.openSession();    Transaction transaction = session.beginTransaction();    Criteria criteria = session.createCriteria(City.class);    //one sql    List<City> cityList = criteria.list();    for(City city : cityList){        //N sql        System.out.println(city.getHotles().size());    }    transaction.commit();    session.close();
output:
2017-02-25 12:00:45.630 DEBUG 5460 --- [nio-8080-exec-5] org.hibernate.SQL                        : select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_Hibernate: select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_2017-02-25 12:00:45.632 DEBUG 5460 --- [nio-8080-exec-5] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?32017-02-25 12:00:45.634 DEBUG 5460 --- [nio-8080-exec-5] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?22017-02-25 12:00:45.636 DEBUG 5460 --- [nio-8080-exec-5] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?52017-02-25 12:00:45.638 DEBUG 5460 --- [nio-8080-exec-5] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?1

FetchType.LAZY and @Fetch(FetchMode.SELECT):
City中加上fetch=FetchType.LAZY 或  @Fetch(FetchMode.SELECT), 输出结果与上面相同，说明one-to-many默认设置是fetch=FetchType.LAZY 和  @Fetch(FetchMode.SELECT)
下面四种配置等效，都是N+1条sql的懒加载：
@OneToMany(mappedBy="city")private Set<Hotel> hotles;
@OneToMany(mappedBy="city")@Fetch(FetchMode.SELECT)private Set<Hotel> hotles;
@OneToMany(mappedBy="city", fetch=FetchType.LAZY)private Set<Hotel> hotles;
@OneToMany(mappedBy="city", fetch=FetchType.LAZY)@Fetch(FetchMode.SELECT)private Set<Hotel> hotles;

FetchType.Eager and @Fetch(FetchMode.SELECT):
City中加上fetch=FetchType.Eager 和  @Fetch(FetchMode.SELECT)
@OneToMany(mappedBy="city", fetch=FetchType.Eager)@Fetch(FetchMode.SELECT)private Set<Hotel> hotles;
output:
2017-02-25 14:13:45.455 DEBUG 5800 --- [nio-8080-exec-1] org.hibernate.SQL                        : select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_Hibernate: select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_2017-02-25 14:13:45.473 DEBUG 5800 --- [nio-8080-exec-1] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?2017-02-25 14:13:45.482 DEBUG 5800 --- [nio-8080-exec-1] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?2017-02-25 14:13:45.485 DEBUG 5800 --- [nio-8080-exec-1] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?2017-02-25 14:13:45.486 DEBUG 5800 --- [nio-8080-exec-1] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?Hibernate: select hotles0_.city_id as city_id4_0_0_, hotles0_.id as id1_1_0_, hotles0_.id as id1_1_1_, hotles0_.address as address2_1_1_, hotles0_.city_id as city_id4_1_1_, hotles0_.name as name3_1_1_ from Hotel hotles0_ where hotles0_.city_id=?3251
同样是N+1条sql，不过和上面情况不同的是，N条sql会在criteria.list()时执行

FetchMode.JOIN:
City中加上@Fetch(FetchMode.JOIN), 那么Hibernate将强行设置为fetch=FetchType.EAGER, 用户设置fetch=FetchType.LAZY将不会生效.
下面四种设置等效：
@OneToMany(mappedBy="city", fetch=FetchType.EAGER)@Fetch(FetchMode.JOIN)private Set<Hotel> hotles;
@OneToMany(mappedBy="city", fetch=FetchType.LAZY)@Fetch(FetchMode.JOIN)private Set<Hotel> hotles;
@OneToMany(mappedBy="city")@Fetch(FetchMode.JOIN)private Set<Hotel> hotles;
@OneToMany(mappedBy="city", fetch=FetchType.EAGER)private Set<Hotel> hotles;
output:
2017-02-25 13:17:07.583 DEBUG 2964 --- [nio-8080-exec-1] org.hibernate.SQL                        : select this_.id as id1_0_1_, this_.country as country2_0_1_, this_.name as name3_0_1_, hotles2_.city_id as city_id4_0_3_, hotles2_.id as id1_1_3_, hotles2_.id as id1_1_0_, hotles2_.address as address2_1_0_, hotles2_.city_id as city_id4_1_0_, hotles2_.name as name3_1_0_ from City this_ left outer join Hotel hotles2_ on this_.id=hotles2_.city_idHibernate: select this_.id as id1_0_1_, this_.country as country2_0_1_, this_.name as name3_0_1_, hotles2_.city_id as city_id4_0_3_, hotles2_.id as id1_1_3_, hotles2_.id as id1_1_0_, hotles2_.address as address2_1_0_, hotles2_.city_id as city_id4_1_0_, hotles2_.name as name3_1_0_ from City this_ left outer join Hotel hotles2_ on this_.id=hotles2_.city_id33322555551
从输出可看出，在执行criteria.list()时通过一条sql 获取了所有的City和Hotel。
使用@Fetch(FetchMode.JOIN)需要注意的是：它在Join查询时是Full Join, 所以会有重复City出现

FetchMode.SUBSELECT:
City中加上@Fetch(FetchMode.SUBSELECT), 那么Hibernate将强行设置为fetch=FetchType.EAGER, 用户设置fetch=FetchType.LAZY将不会生效.
下面三种设置等效：
@OneToMany(mappedBy="city", fetch=FetchType.EAGER)@Fetch(FetchMode.SUBSELECT)private Set<Hotel> hotles;
@OneToMany(mappedBy="city", fetch=FetchType.LAZY)@Fetch(FetchMode.SUBSELECT)private Set<Hotel> hotles;
@OneToMany(mappedBy="city")@Fetch(FetchMode.SUBSELECT)private Set<Hotel> hotles;
output:
2017-02-25 13:45:06.089 DEBUG 4004 --- [nio-8080-exec-1] org.hibernate.SQL                        : select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_Hibernate: select this_.id as id1_0_0_, this_.country as country2_0_0_, this_.name as name3_0_0_ from City this_2017-02-25 13:45:06.114 DEBUG 4004 --- [nio-8080-exec-1] org.hibernate.SQL                        : select hotles0_.city_id as city_id4_0_1_, hotles0_.id as id1_1_1_, hotles0_.id as id1_1_0_, hotles0_.address as address2_1_0_, hotles0_.city_id as city_id4_1_0_, hotles0_.name as name3_1_0_ from Hotel hotles0_ where hotles0_.city_id in (select this_.id from City this_)Hibernate: select hotles0_.city_id as city_id4_0_1_, hotles0_.id as id1_1_1_, hotles0_.id as id1_1_0_, hotles0_.address as address2_1_0_, hotles0_.city_id as city_id4_1_0_, hotles0_.name as name3_1_0_ from Hotel hotles0_ where hotles0_.city_id in (select this_.id from City this_)3251
从输出可看出，在执行criteria.list()时通过两条sql分别获取City和Hotel。

Summary:
FetchMode\FetchTypeLazyEagerNullSelectABAJoinCCCSubselectDDDNullACA
十二种排列组合，但最后只会有四种情况，如上标注的A,B,C,D：
A. FetchMode.SELECT与FetchType.LAZY，都不设置，都设置，设置任一, 都将会是情况A。情况A会出现N+1条sql，其中N条是懒加载。
B. FetchMode.SELECT与FetchType.EAGER同时设置时出现B情况，同样是N+1条sql，和A情况不同的是N条sql会立即执行。
C. 只要设置了FetchMode.JOIN，不管FetchType设置成什么（FetchType.EAGER,FetchType.LAZY,不设置FetchType），都将会是C情况。同时，只设置FetchType.EAGER而不设置FetchMode也将会是C情况。C情况下只产生一条立即执行的Full-Join的sql, parent数据可能会重复。
D. 只要设置了FetchMode.SUBSELECT，不管FetchType设置成什么（FetchType.EAGER,FetchType.LAZY,不设置FetchType），都将会是D情况。D情况下产生两条立即执行的sql, 分别读取parent表和child表。


作者：昵称全尼马被注册了
链接：http://www.jianshu.com/p/23bd82a7b96e
來源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。