hibernate N+1 问题的总结

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的问题，这个还有带探索哈！