当Hibernate或Ibatis在处理一对多的时候都存在n+1问题。
[size=medium]创建数据库[/size]
ACCOUNT 表
[table]
|ACCOUNT_ID|
|1|
|2|
[/table]
ORDERS 表
[table]
|ORDER_ID|ACCOUNT_ID|
|1|1|
|2|1|
|3|1|
|4|2|
|5|2|
|6|2|
[/table]
[size=medium]创建JavaBean[/size]
Account.java
Orders.java
[size=large]一、使用Ibatis查询[/size]
SqlMapConfig.xml配置
n1.ibatis.xml 配置
groupby.ibatis.xml 配置
测试代码
执行以上代码,log4j打印如下:
查看以上log,发现执行了3条查询语句.
将SqlMapConfig.xml中的setting 节点中的lazyLoadingEnabled属性设为true
再次执行之前queryN1测试代码,log4j打印如下
查看log,依然有3条查询语句,只是查询的时机不一样了。当设置SqlMapConfig.xml中的setting 节点中的lazyLoadingEnabled属性设为true时(启动ibatis懒加载),ibatis会生成代理对对象,当集合元素真正使用时才执行查询语句(和hibernate类似)。
目前来看:至少在ibatis中启用或不启用懒加载,都不可以解决n+1问题.而在ibatis中使用groupby是一个解决方案.
测试代码如下:
执行以上测试代码,log打印如下
查看以上log,发现只有1条查询语句,解决了n+1问题。
目前看来,n+1问题算是解决了,可是配置文件搞得很复杂。
ibatis作为sql mapper并不是orm,非把ibatis当hibernate玩,个人认为这样并没有多大意义。
[size=large]二 使用hibernate查询[/size]
hibernate.cfg.xml配置
Account.hibernate.xml 配置
Orders.hibernate.xml 配置
[size=medium]a.hibernate 1对多 n+1[/size]
测试代码
执行以上测试代码,打印log如下:
查看以上log,发现3条查询语句。n+1 问题重现了。因为hibernate对集合查询默认是懒加载的,所以从以上打印log发现,只用在要使用集合时,hibernate才去执行查询。
将Account.hibernate.xml中set节点的lazy属性设置为false,如下
再次执行query1()测试代码,log打印如下:
查看以上log,发现依然是3条查询语句,只是查询在一开始就全部执行了。
[size=medium]b.hibernate 多对1 n+1 [/size]
测试代码
执行以上测试代码,log如下:
查看以上log,发现3条查询语句。
倘若将Account.hibernate.xml中set节点的lazy属性设置为false,如下
再次执行query2(),log打印如下:
查看以上log,发现5条查询语句。第一查询语句用来查询Orders信息,而Orders对象中的Account实例变量默认是懒加载的,所以当使用account变量时,hibernate才进行查询,由于Account对象中的ordersSet集合不启用懒加载。所以每次查询Account时会附带查询一次Orders.
[size=medium]c.hibernate 使用iterate n+1[/size]
测试代码
执行以上测试代码,log打印语句如下:
查看以上log,发现有5条查询语句。第一条查询Account对象的accountId列表。当迭代使用Account时,hibernate会通过查询到的accountId在内存中找,如果找到则直接使用,如果为未找到则做一次查询。而把iterate查询归纳为n+1查询感觉不妥当,写在这里是想说明iterate没用好会产生n+1次查询或者更多。
如果将query3()中的迭代对象改为Orders,就会有很刺激的事,如果再将Account中的ordersSet懒加载去除(lazy=false),就更有意思了。
在hibernate中要解决n+1可以通过left join fetch。
将query1() 的hql该为from Account as account left join fetch account.ordersSet
重新执行则只有一条查询语句。
[size=large]总结:不管是hibernate 还是ibatis 如果对象设计中有主从对象(主从对象指:一个对象中包含另一个对象,包含对象为主对象,被包含对象为从对象,从对象可为普通javabean或集合)都有可能碰到n+1问题,其中1指进行一条语句查询到n个主对象,而n指n个主对象要将从对象关联出来要进行n次查询。而解决方案不管怎样就是通过数据库join查询。而hibernate比较复杂点有很多缓存可用,所以具体的时候会有点差异。n+1并不是问题,更是一种策略。[/size]
[size=medium]创建数据库[/size]
ACCOUNT 表
[table]
|ACCOUNT_ID|
|1|
|2|
[/table]
ORDERS 表
[table]
|ORDER_ID|ACCOUNT_ID|
|1|1|
|2|1|
|3|1|
|4|2|
|5|2|
|6|2|
[/table]
[size=medium]创建JavaBean[/size]
Account.java
public class Account {
private Integer accountId;
private Set<Orders> ordersSet = new HashSet<Orders>();
// 省略 getter setter
public String toString() {
return "accountId:" + accountId;
}
}
Orders.java
public class Orders {
private Integer orderId;
private Account account;
// 省略getter setter
public String toString() {
return "orderId:"+orderId;
}
}
[size=large]一、使用Ibatis查询[/size]
SqlMapConfig.xml配置
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="false"
lazyLoadingEnabled="false" maxRequests="32" maxSessions="10"
maxTransactions="5" useStatementNamespaces="true" />
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver"
value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="JDBC.ConnectionURL"
value="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=TEST" />
<property name="JDBC.Username" value="xiao" />
<property name="JDBC.Password" value="xiao" />
</dataSource>
</transactionManager>
<sqlMap resource="ibatis/n1.ibatis.xml" />
<sqlMap resource="ibatis/groupby.ibatis.xml" />
</sqlMapConfig>
n1.ibatis.xml 配置
<sqlMap namespace="N1">
<typeAlias alias="Account" type="bean.Account"/>
<resultMap id="ResultAccountInfoMap" class="bean.Account">
<result property="accountId" column="ACCOUNT_ID" />
<result property="ordersSet" select="N1.getOrderInfoList" column="ACCOUNT_ID"/>
</resultMap>
<resultMap id="ResultOrderInfoMap" class="bean.Orders">
<result property="orderId" column="ORDER_ID" />
<result property="account.accountId" column="ACCOUNt_Id" />
</resultMap>
<select id="getAccountInfoList" resultMap="ResultAccountInfoMap">
select account_Id from ACCOUNT
</select>
<select id="getOrderInfoList" resultMap="ResultOrderInfoMap">
select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = #value#
</select>
</sqlMap>
groupby.ibatis.xml 配置
<sqlMap namespace="GroupBy">
<resultMap id="ResultAccountInfoMap" class="bean.Account" groupBy="accountId">
<result property="accountId" column="account_Id" />
<result property="ordersSet" resultMap="GroupBy.ResultOrderInfoMap"/>
</resultMap>
<resultMap id="ResultOrderInfoMap" class="bean.Orders" groupBy="orderId">
<result property="orderId" column="ORDER_ID" />
<result property="account.accountId" column="ACCOUNT_ID" />
</resultMap>
<select id="getAccountInfoList" resultMap="ResultAccountInfoMap">
select
ACCOUNT.ACCOUNT_ID AS ACCOUNT_ID
,ORDERS.ORDER_ID AS ORDER_ID
from ACCOUNT
join ORDERS on ACCOUNT.account_Id=ORDERS.ACCOUNT_ID
group by ACCOUNT.ACCOUNT_ID,ORDERS.ORDER_ID
</select>
</sqlMap>
测试代码
public static void queryN1() throws SQLException, IOException{
Reader reader=Resources.getResourceAsReader("ibatis/SqlMapConfig.xml");
SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
List<Account> accountList=sqlMapClient.queryForList("N1.getAccountInfoList");
for(Account account:accountList){
System.out.println("############"+account+"############");
for(Orders orders:account.getOrdersSet()){
System.out.println("############"+orders+"############");
}
}
}
执行以上代码,log4j打印如下:
[size=xx-small]2012-08-07 10:19:52,000-[HL] DEBUG main java.sql.Connection - {conn-100000} Connection
2012-08-07 10:19:52,000-[HL] DEBUG main java.sql.Connection - {conn-100000} Preparing Statement: select account_Id from ACCOUNT
2012-08-07 10:19:52,031-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Executing Statement: select account_Id from ACCOUNT
2012-08-07 10:19:52,031-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Parameters: []
2012-08-07 10:19:52,031-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Types: []
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.Connection - {conn-100000} Preparing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Executing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Parameters: [1]
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Types: [java.lang.Integer]
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Executing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Parameters: [2]
2012-08-07 10:19:52,078-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100003} Types: [java.lang.Integer][/size][size=xx-small][/size]
############accountId:1############
############orderId:3############
############orderId:2############
############orderId:1############
############accountId:2############
############orderId:5############
############orderId:6############
############orderId:4############
查看以上log,发现执行了3条查询语句.
将SqlMapConfig.xml中的setting 节点中的lazyLoadingEnabled属性设为true
<settings cacheModelsEnabled="true" enhancementEnabled="false"
lazyLoadingEnabled="true" maxRequests="32" maxSessions="10"
maxTransactions="5" useStatementNamespaces="true" />
再次执行之前queryN1测试代码,log4j打印如下
2012-08-07 10:26:42,468-[HL] DEBUG main java.sql.Connection - {conn-100000} Connection
2012-08-07 10:26:42,468-[HL] DEBUG main java.sql.Connection - {conn-100000} Preparing Statement: select account_Id from ACCOUNT
2012-08-07 10:26:42,515-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Executing Statement: select account_Id from ACCOUNT
2012-08-07 10:26:42,515-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Parameters: []
2012-08-07 10:26:42,515-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Types: []
############accountId:1############
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.Connection - {conn-100003} Connection
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.Connection - {conn-100003} Preparing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100004} Executing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100004} Parameters: [1]
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100004} Types: [java.lang.Integer]
############orderId:3############
############orderId:1############
############orderId:2############
############accountId:2############
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.Connection - {conn-100006} Connection
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.Connection - {conn-100006} Preparing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100007} Executing Statement: select ORDER_ID,ACCOUNT_ID from ORDERS where ACCOUNT_ID = ?
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100007} Parameters: [2]
2012-08-07 10:26:42,562-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100007} Types: [java.lang.Integer]
############orderId:5############
############orderId:4############
############orderId:6############
查看log,依然有3条查询语句,只是查询的时机不一样了。当设置SqlMapConfig.xml中的setting 节点中的lazyLoadingEnabled属性设为true时(启动ibatis懒加载),ibatis会生成代理对对象,当集合元素真正使用时才执行查询语句(和hibernate类似)。
目前来看:至少在ibatis中启用或不启用懒加载,都不可以解决n+1问题.而在ibatis中使用groupby是一个解决方案.
测试代码如下:
public static void queryGroupBy() throws SQLException, IOException{
Reader reader=Resources.getResourceAsReader("ibatis/SqlMapConfig.xml");
SqlMapClient sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
List<Account> accountList=sqlMapClient.queryForList("GroupBy.getAccountInfoList");
for(Account account:accountList){
System.out.println("############"+account+"############");
for(Orders orders:account.getOrdersSet()){
System.out.println("############"+orders+"############");
}
}
}
执行以上测试代码,log打印如下
2012-08-07 10:36:12,328-[HL] DEBUG main java.sql.Connection - {conn-100000} Connection
2012-08-07 10:36:12,328-[HL] DEBUG main java.sql.Connection - {conn-100000} Preparing Statement: select ACCOUNT.ACCOUNT_ID AS ACCOUNT_ID ,ORDERS.ORDER_ID AS ORDER_ID from ACCOUNT join ORDERS on ACCOUNT.account_Id=ORDERS.ACCOUNT_ID group by ACCOUNT.ACCOUNT_ID,ORDERS.ORDER_ID
2012-08-07 10:36:12,359-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Executing Statement: select ACCOUNT.ACCOUNT_ID AS ACCOUNT_ID ,ORDERS.ORDER_ID AS ORDER_ID from ACCOUNT join ORDERS on ACCOUNT.account_Id=ORDERS.ACCOUNT_ID group by ACCOUNT.ACCOUNT_ID,ORDERS.ORDER_ID
2012-08-07 10:36:12,359-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Parameters: []
2012-08-07 10:36:12,359-[HL] DEBUG main java.sql.PreparedStatement - {pstm-100001} Types: []
############accountId:1############
############orderId:3############
############orderId:2############
############orderId:1############
############accountId:2############
############orderId:4############
############orderId:6############
############orderId:5############
查看以上log,发现只有1条查询语句,解决了n+1问题。
目前看来,n+1问题算是解决了,可是配置文件搞得很复杂。
ibatis作为sql mapper并不是orm,非把ibatis当hibernate玩,个人认为这样并没有多大意义。
[size=large]二 使用hibernate查询[/size]
hibernate.cfg.xml配置
<hibernate-configuration>
<session-factory>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="connection.url">jdbc:sqlserver://127.0.0.1:1433;DatabaseName=TEST</property>
<property name="connection.username">xiao</property>
<property name="connection.password">xiao</property>
<property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="show_sql">true</property>
<mapping resource="hibernate/Account.hibernate.xml"/>
<mapping resource="hibernate/Orders.hibernate.xml"/>
</session-factory>
</hibernate-configuration>
Account.hibernate.xml 配置
<hibernate-mapping>
<class name="bean.Account" table="ACCOUNT" >
<id name="accountId" column="ACCOUNT_ID" />
<set name="ordersSet" >
<key column="ACCOUNT_ID"/>
<one-to-many class="bean.Orders"/>
</set>
</class>
</hibernate-mapping>
Orders.hibernate.xml 配置
<hibernate-mapping>
<class name="bean.Orders" table="ORDERS">
<id name="orderId" column="ORDER_ID"/>
<many-to-one name="account" class="bean.Account" column="ACCOUNT_ID"/>
</class>
</hibernate-mapping>
[size=medium]a.hibernate 1对多 n+1[/size]
测试代码
public static void query1(){
Configuration config = new Configuration().configure("hibernate/hibernate.cfg.xml");
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
System.out.println("==========start query==========");
Query query=session.createQuery("from Account as account");
List<Account> accountList=query.list();
for(Account account:accountList){
System.out.println("############"+account+"############");
for(Orders orders:account.getOrdersSet()){
System.out.println("############"+orders+"############");
}
}
session.close();
}
执行以上测试代码,打印log如下:
==========start query==========
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_ from ACCOUNT account0_
############accountId:1############
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############orderId:2############
############orderId:1############
############orderId:3############
############accountId:2############
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############orderId:6############
############orderId:5############
############orderId:4############
查看以上log,发现3条查询语句。n+1 问题重现了。因为hibernate对集合查询默认是懒加载的,所以从以上打印log发现,只用在要使用集合时,hibernate才去执行查询。
将Account.hibernate.xml中set节点的lazy属性设置为false,如下
<set name="ordersSet" lazy="false">
<key column="ACCOUNT_ID"/>
<one-to-many class="bean.Orders"/>
</set>
再次执行query1()测试代码,log打印如下:
==========start query==========
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_ from ACCOUNT account0_
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############accountId:1############
############orderId:1############
############orderId:3############
############orderId:2############
############accountId:2############
############orderId:5############
############orderId:4############
############orderId:6############
查看以上log,发现依然是3条查询语句,只是查询在一开始就全部执行了。
[size=medium]b.hibernate 多对1 n+1 [/size]
测试代码
public static void query2(){
Configuration config = new Configuration().configure("hibernate/hibernate.cfg.xml");
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
System.out.println("==========start query==========");
Query query=session.createQuery("from Orders");
List<Orders> ordersSet=query.list();
for(Orders orders:ordersSet){
System.out.println("############"+orders+"############");
System.out.println("############"+orders.getAccount()+"############");
}
session.close();
}
执行以上测试代码,log如下:
==========start query==========
Hibernate: select orders0_.ORDER_ID as ORDER1_1_, orders0_.ACCOUNT_ID as ACCOUNT2_1_ from ORDERS orders0_
############orderId:1############
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
############accountId:1############
############orderId:2############
############accountId:1############
############orderId:3############
############accountId:1############
############orderId:4############
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
############accountId:2############
############orderId:5############
############accountId:2############
############orderId:6############
############accountId:2############
查看以上log,发现3条查询语句。
倘若将Account.hibernate.xml中set节点的lazy属性设置为false,如下
<set name="ordersSet" lazy="false">
<key column="ACCOUNT_ID"/>
<one-to-many class="bean.Orders"/>
</set>
再次执行query2(),log打印如下:
==========start query==========
Hibernate: select orders0_.ORDER_ID as ORDER1_1_, orders0_.ACCOUNT_ID as ACCOUNT2_1_ from ORDERS orders0_
############orderId:1############
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############accountId:1############
############orderId:2############
############accountId:1############
############orderId:3############
############accountId:1############
############orderId:4############
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############accountId:2############
############orderId:5############
############accountId:2############
############orderId:6############
############accountId:2############
查看以上log,发现5条查询语句。第一查询语句用来查询Orders信息,而Orders对象中的Account实例变量默认是懒加载的,所以当使用account变量时,hibernate才进行查询,由于Account对象中的ordersSet集合不启用懒加载。所以每次查询Account时会附带查询一次Orders.
[size=medium]c.hibernate 使用iterate n+1[/size]
测试代码
public static void query3(){
Configuration config = new Configuration().configure("hibernate/hibernate.cfg.xml");
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
System.out.println("==========start query==========");
Query query=session.createQuery("from Account");
Iterator<Account> iterator=query.iterate();
while(iterator.hasNext()){
Account account=iterator.next();
System.out.println("############"+account+"############");
System.out.println("############"+account.getOrdersSet()+"############");
}
session.close();
}
执行以上测试代码,log打印语句如下:
==========start query==========
Hibernate: select account0_.ACCOUNT_ID as col_0_0_ from ACCOUNT account0_
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
############accountId:1############
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############[orderId:3, orderId:2, orderId:1]############
Hibernate: select account0_.ACCOUNT_ID as ACCOUNT1_0_0_ from ACCOUNT account0_ where account0_.ACCOUNT_ID=?
############accountId:2############
Hibernate: select ordersset0_.ACCOUNT_ID as ACCOUNT2_1_, ordersset0_.ORDER_ID as ORDER1_1_, ordersset0_.ORDER_ID as ORDER1_1_0_, ordersset0_.ACCOUNT_ID as ACCOUNT2_1_0_ from ORDERS ordersset0_ where ordersset0_.ACCOUNT_ID=?
############[orderId:6, orderId:5, orderId:4]############
查看以上log,发现有5条查询语句。第一条查询Account对象的accountId列表。当迭代使用Account时,hibernate会通过查询到的accountId在内存中找,如果找到则直接使用,如果为未找到则做一次查询。而把iterate查询归纳为n+1查询感觉不妥当,写在这里是想说明iterate没用好会产生n+1次查询或者更多。
如果将query3()中的迭代对象改为Orders,就会有很刺激的事,如果再将Account中的ordersSet懒加载去除(lazy=false),就更有意思了。
在hibernate中要解决n+1可以通过left join fetch。
将query1() 的hql该为from Account as account left join fetch account.ordersSet
重新执行则只有一条查询语句。
[size=large]总结:不管是hibernate 还是ibatis 如果对象设计中有主从对象(主从对象指:一个对象中包含另一个对象,包含对象为主对象,被包含对象为从对象,从对象可为普通javabean或集合)都有可能碰到n+1问题,其中1指进行一条语句查询到n个主对象,而n指n个主对象要将从对象关联出来要进行n次查询。而解决方案不管怎样就是通过数据库join查询。而hibernate比较复杂点有很多缓存可用,所以具体的时候会有点差异。n+1并不是问题,更是一种策略。[/size]