hibernate N+1 问题的总结
在hibernate中的一对多或者多对一中查询一的一方或者多的一方都会出现N+1的问题。比如Customer和Linkman为一对多的问题。查询Customer的时候也会吧Customer对应的Linkman也查出来,对查询Linkman也一样。
iterator 查询时,一定先去缓存中找(1条sql查集合,只查出ID),在没命中时,会再按ID到库中逐一查找, 产生1+n条SQL
@Test
public void testListCustomer() {
SessionFactory sessionFactory = null;
Session session = null;
try {
//得到sessionFactory
sessionFactory = HibernateUtils.getSessionFactory();
//得到session
session = sessionFactory.openSession();
/*<set name="setLinkMan" cascade="save-update,delete" lazy="false">
<!-- 一对多建表,有外键
hibernate机制:双向维护外键,在一和多那一方都配置外键
column属性值:外键名称
-->
<key column="clid"></key>
<!-- 客户所有的联系人,class里面写联系人实体类全路径 -->
<one-to-many class="cn.itcast.entity.LinkMan" />
</set>*/
//这样的配置会有N+1问题。lazy="false"设置不使用懒加载。 hibernate3以后的版本默认为lazy="true"
Query query = session.createQuery("from Customer");
List<Customer> customerList = (List<Customer>) query.list();
System.out.println(customerList);
}catch(Exception e) {
e.printStackTrace();
}finally {
session.close();
//sessionFactory不需要关闭
sessionFactory.close();
}
}
打印出的sql语句:
Hibernate:
select
customer0_.cid as cid1_0_,
customer0_.custName as custName2_0_,
customer0_.custLevel as custLeve3_0_,
customer0_.custSource as custSour4_0_,
customer0_.custPhone as custPhon5_0_,
customer0_.custMobile as custMobi6_0_
from
t_customer customer0_
Hibernate:
select
setlinkman0_.clid as clid5_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_name as lkm_name2_1_1_,
setlinkman0_.lkm_gender as lkm_gend3_1_1_,
setlinkman0_.lkm_phone as lkm_phon4_1_1_,
setlinkman0_.clid as clid5_1_1_
from
t_linkman setlinkman0_
where
setlinkman0_.clid=?
Hibernate:
select
setlinkman0_.clid as clid5_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_name as lkm_name2_1_1_,
setlinkman0_.lkm_gender as lkm_gend3_1_1_,
setlinkman0_.lkm_phone as lkm_phon4_1_1_,
setlinkman0_.clid as clid5_1_1_
from
t_linkman setlinkman0_
where
setlinkman0_.clid=?
Hibernate:
select
setlinkman0_.clid as clid5_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_name as lkm_name2_1_1_,
setlinkman0_.lkm_gender as lkm_gend3_1_1_,
setlinkman0_.lkm_phone as lkm_phon4_1_1_,
setlinkman0_.clid as clid5_1_1_
from
t_linkman setlinkman0_
where
setlinkman0_.clid=?
Hibernate:
select
setlinkman0_.clid as clid5_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_name as lkm_name2_1_1_,
setlinkman0_.lkm_gender as lkm_gend3_1_1_,
setlinkman0_.lkm_phone as lkm_phon4_1_1_,
setlinkman0_.clid as clid5_1_1_
from
t_linkman setlinkman0_
where
setlinkman0_.clid=?
Hibernate:
select
setlinkman0_.clid as clid5_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_name as lkm_name2_1_1_,
setlinkman0_.lkm_gender as lkm_gend3_1_1_,
setlinkman0_.lkm_phone as lkm_phon4_1_1_,
setlinkman0_.clid as clid5_1_1_
from
t_linkman setlinkman0_
where
setlinkman0_.clid=?
这里只需要设置lazy=”true”,在hibernate3以后的版本
默认lazy=”true”,所以不需要再配置。
还可以设置:
<set name="setLinkMan" cascade="save-update,delete" lazy="false" batch-size="10">
batch-size=”10”来确定一次性抓取的条数
sql语句:
Hibernate:
select
customer0_.cid as cid1_0_,
customer0_.custName as custName2_0_,
customer0_.custLevel as custLeve3_0_,
customer0_.custSource as custSour4_0_,
customer0_.custPhone as custPhon5_0_,
customer0_.custMobile as custMobi6_0_
from
t_customer customer0_
Hibernate:
select
setlinkman0_.clid as clid5_1_1_,
setlinkman0_.lkm_id as lkm_id1_1_1_,
setlinkman0_.lkm_id as lkm_id1_1_0_,
setlinkman0_.lkm_name as lkm_name2_1_0_,
setlinkman0_.lkm_gender as lkm_gend3_1_0_,
setlinkman0_.lkm_phone as lkm_phon4_1_0_,
setlinkman0_.clid as clid5_1_0_
from
t_linkman setlinkman0_
where
setlinkman0_.clid in (
?, ?, ?, ?, ?
)
在多的一方。
@Test
public void testLinkMan() {
SessionFactory sessionFactory = null;
Session session = null;
try {
//得到sessionFactory
sessionFactory = HibernateUtils.getSessionFactory();
//得到session
session = sessionFactory.openSession();
/*Query query = session.createQuery("from Customer");
List<Customer> customerList = (List<Customer>) query.list();*/
Query query = session.createQuery("from LinkMan l left join fetch l.customer");
List<LinkMan> linkManList = (List<LinkMan>) query.list();
/*for(Customer customer : customerList){
Set<LinkMan> linkManList = customer.getSetLinkMan();
System.out.println(linkManList);
}*/
/*SQLQuery query = session.createSQLQuery("select * from Customer").addEntity(Customer.class);
List<Customer> customerList = query.list();
System.out.println(customerList);*/
}catch(Exception e) {
e.printStackTrace();
}finally {
session.close();
//sessionFactory不需要关闭
sessionFactory.close();
}
}
打印出来的sql语句:
Hibernate:
select
linkman0_.lkm_id as lkm_id1_1_0_,
customer1_.cid as cid1_0_1_,
linkman0_.lkm_name as lkm_name2_1_0_,
linkman0_.lkm_gender as lkm_gend3_1_0_,
linkman0_.lkm_phone as lkm_phon4_1_0_,
linkman0_.clid as clid5_1_0_,
customer1_.custName as custName2_0_1_,
customer1_.custLevel as custLeve3_0_1_,
customer1_.custSource as custSour4_0_1_,
customer1_.custPhone as custPhon5_0_1_,
customer1_.custMobile as custMobi6_0_1_
from
t_linkman linkman0_
left outer join
t_customer customer1_
on linkman0_.clid=customer1_.cid
我在set上面lazy=”true” 在上面lazy=”proxy”但是还是会出现N+1的问题,这个还有带探索哈!