n+1问题

• http://hi.baidu.com/victorlin23/blog/item/8b7a3382670cd9b16d811963.html

Find()方法下FetchType.LAZY、FetchType.EAGER、@Fetch(FetchMode.JOIN)的区别2010-08-27 16:13结论：1.使用
例如：@ManyToOne(fetch = FetchType.LAZY)将会产生N+1的问题。
      2.使用：@ManyToOne(fetch = FetchType.EAGER)将会使用join查询，推断hibernate对eaer进行了优化并不会产生N+1的问题。 
      3.使用：@Fetch(FetchMode.JOIN)将和第2项描述的执行效果和步骤是一样的，都是使用join 可以得出官方文档：Hibernate_Annotations.pdf第62页描述的Table 2.3. Lazy and fetch options equivalent是正确的。完全等价。hibernate重载了EJG的Fetch注解                                      
一、@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "SERVICE_ID", referencedColumnName = "ID")
public SystemServiceInfo getSysService() {
   return sysService;
}
使用Lazy查询，由于需要立即访问触发了数据库查询。这个时候出现了N+1的问题：
    select
        count(*) as y0_ 
    from
        at_t_client_and_service this_ 
    inner join
        AT_T_CLIENT_INFO clientinfo1_ 
            on this_.CLIENT_ID=clientinfo1_.id 
    where
        (
            1=1
        ) 
        and (
            clientinfo1_.id = ?
        )
Hibernate: 
    select
        * 
    from
        ( select
            this_.id as id56_1_,
            this_.URL as URL56_1_,
            this_.CLIENT_ID as CLIENT15_56_1_,
            this_.CREATE_DATE as CREATE3_56_1_,
            this_.SERVICE_ID as SERVICE16_56_1_,
            this_.NAME_SPACE_URL as NAME4_56_1_,
            this_.LOCAL_PART_SERVICE as LOCAL5_56_1_,
            this_.LOCAL_PART_PORT as LOCAL6_56_1_,
            this_.PERIOD_BEGIN_DATE as PERIOD7_56_1_,
            this_.PERIOD_END_DATE as PERIOD8_56_1_,
            this_.FTP_FLAG as FTP9_56_1_,
            this_.FTP_RELATIVE_FLAG as FTP10_56_1_,
            this_.FTP_PATH as FTP11_56_1_,
            this_.FTP_PORT as FTP12_56_1_,
            this_.FTP_USER as FTP13_56_1_,
            this_.FTP_PASSWORD as FTP14_56_1_,
            clientinfo1_.id as id63_0_,
            clientinfo1_.CLIENT_ID as CLIENT2_63_0_,
            clientinfo1_.CLIENT_IP as CLIENT3_63_0_,
            clientinfo1_.CLIENT_NAME as CLIENT4_63_0_,
            clientinfo1_.REGEST_TIME as REGEST5_63_0_,
            clientinfo1_.REMARK as REMARK63_0_ 
        from
            at_t_client_and_service this_ 
        inner join
            AT_T_CLIENT_INFO clientinfo1_ 
                on this_.CLIENT_ID=clientinfo1_.id 
        where
            (
                1=1
            ) 
            and (
                clientinfo1_.id = ?
            ) ) 
    where
        rownum <= ?
Hibernate: 
    select
        systemserv0_.id as id28_0_,
        systemserv0_.COMM as COMM28_0_,
        systemserv0_.SERVICE_NAME as SERVICE3_28_0_,
        systemserv0_.CREATE_DATE as CREATE4_28_0_ 
    from
        AT_T_SERVICE systemserv0_ 
    where
        systemserv0_.id=?
Hibernate: 
    select
        systemserv0_.id as id28_0_,
        systemserv0_.COMM as COMM28_0_,
        systemserv0_.SERVICE_NAME as SERVICE3_28_0_,
        systemserv0_.CREATE_DATE as CREATE4_28_0_ 
    from
        AT_T_SERVICE systemserv0_ 
    where
        systemserv0_.id=?


二、@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "SERVICE_ID", referencedColumnName = "ID")
public SystemServiceInfo getSysService() {
   return sysService;
}
并未出现N+1的问题
Hibernate: 
    select
        count(*) as y0_ 
    from
        at_t_client_and_service this_ 
    inner join
        AT_T_CLIENT_INFO clientinfo1_ 
            on this_.CLIENT_ID=clientinfo1_.id 
    where
        (
            1=1
        ) 
        and (
            clientinfo1_.id = ?
        )
Hibernate: 
    select
        * 
    from
        ( select
            this_.id as id61_2_,
            this_.URL as URL61_2_,
            this_.CLIENT_ID as CLIENT15_61_2_,
            this_.CREATE_DATE as CREATE3_61_2_,
            this_.SERVICE_ID as SERVICE16_61_2_,
            this_.NAME_SPACE_URL as NAME4_61_2_,
            this_.LOCAL_PART_SERVICE as LOCAL5_61_2_,
            this_.LOCAL_PART_PORT as LOCAL6_61_2_,
            this_.PERIOD_BEGIN_DATE as PERIOD7_61_2_,
            this_.PERIOD_END_DATE as PERIOD8_61_2_,
            this_.FTP_FLAG as FTP9_61_2_,
            this_.FTP_RELATIVE_FLAG as FTP10_61_2_,
            this_.FTP_PATH as FTP11_61_2_,
            this_.FTP_PORT as FTP12_61_2_,
            this_.FTP_USER as FTP13_61_2_,
            this_.FTP_PASSWORD as FTP14_61_2_,
            clientinfo1_.id as id63_0_,
            clientinfo1_.CLIENT_ID as CLIENT2_63_0_,
            clientinfo1_.CLIENT_IP as CLIENT3_63_0_,
            clientinfo1_.CLIENT_NAME as CLIENT4_63_0_,
            clientinfo1_.REGEST_TIME as REGEST5_63_0_,
            clientinfo1_.REMARK as REMARK63_0_,
            systemserv4_.id as id29_1_,
            systemserv4_.COMM as COMM29_1_,
            systemserv4_.SERVICE_NAME as SERVICE3_29_1_,
            systemserv4_.CREATE_DATE as CREATE4_29_1_ 
        from
            at_t_client_and_service this_ 
        inner join
            AT_T_CLIENT_INFO clientinfo1_ 
                on this_.CLIENT_ID=clientinfo1_.id 
        left outer join
            AT_T_SERVICE systemserv4_ 
                on this_.SERVICE_ID=systemserv4_.id 
        where
            (
                1=1
            ) 
            and (
                clientinfo1_.id = ?
            ) ) 
    where
        rownum <= ?


三、 @ManyToOne
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "CLIENT_ID", referencedColumnName = "ID")
public ClientInfo getClientInfo() {
   return clientInfo;
}

未出现N+1的问题。而且和第二种配置的效果完全一样，证实了hibernate做eager时进行了优化，使用了ourtjoin，并不会产生N+1问题。
Hibernate: 
    select
        count(*) as y0_ 
    from
        at_t_client_and_service this_ 
    inner join
        AT_T_CLIENT_INFO clientinfo1_ 
            on this_.CLIENT_ID=clientinfo1_.id 
    where
        (
            1=1
        ) 
        and (
            clientinfo1_.id = ?
        )
Hibernate: 
    select
        * 
    from
        ( select
            this_.id as id35_2_,
            this_.URL as URL35_2_,
            this_.CLIENT_ID as CLIENT15_35_2_,
            this_.CREATE_DATE as CREATE3_35_2_,
            this_.SERVICE_ID as SERVICE16_35_2_,
            this_.NAME_SPACE_URL as NAME4_35_2_,
            this_.LOCAL_PART_SERVICE as LOCAL5_35_2_,
            this_.LOCAL_PART_PORT as LOCAL6_35_2_,
            this_.PERIOD_BEGIN_DATE as PERIOD7_35_2_,
            this_.PERIOD_END_DATE as PERIOD8_35_2_,
            this_.FTP_FLAG as FTP9_35_2_,
            this_.FTP_RELATIVE_FLAG as FTP10_35_2_,
            this_.FTP_PATH as FTP11_35_2_,
            this_.FTP_PORT as FTP12_35_2_,
            this_.FTP_USER as FTP13_35_2_,
            this_.FTP_PASSWORD as FTP14_35_2_,
            clientinfo1_.id as id47_0_,
            clientinfo1_.CLIENT_ID as CLIENT2_47_0_,
            clientinfo1_.CLIENT_IP as CLIENT3_47_0_,
            clientinfo1_.CLIENT_NAME as CLIENT4_47_0_,
            clientinfo1_.REGEST_TIME as REGEST5_47_0_,
            clientinfo1_.REMARK as REMARK47_0_,
            systemserv4_.id as id58_1_,
            systemserv4_.COMM as COMM58_1_,
            systemserv4_.SERVICE_NAME as SERVICE3_58_1_,
            systemserv4_.CREATE_DATE as CREATE4_58_1_ 
        from
            at_t_client_and_service this_ 
        inner join
            AT_T_CLIENT_INFO clientinfo1_ 
                on this_.CLIENT_ID=clientinfo1_.id 
        left outer join
            AT_T_SERVICE systemserv4_ 
                on this_.SERVICE_ID=systemserv4_.id 
        where
            (
                1=1
            ) 
            and (
                clientinfo1_.id = ?
            ) ) 
    where
        rownum <= ?

参考官方文档：Hibernate_Annotations.pdf   P62。The Hibernate annotations overrides the EJB3 fetching options