hibernate ibatis n+1
来源:互联网 发布:正大 数据恢复 编辑:程序博客网 时间:2024/06/01 07:13
转载自:http://antlove.iteye.com/blog/1622749
当Hibernate或Ibatis在处理一对多的时候都存在n+1问题。
创建数据库ACCOUNT 表
ACCOUNT_ID12
ORDERS 表
ORDER_IDACCOUNT_ID112131425262
创建JavaBean
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;
- }
- }
一、使用Ibatis查询
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玩,个人认为这样并没有多大意义。
二 使用hibernate查询
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>
a.hibernate 1对多 n+1
测试代码
- 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条查询语句,只是查询在一开始就全部执行了。
b.hibernate 多对1 n+1
测试代码
- 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.
c.hibernate 使用iterate n+1
测试代码
- 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
重新执行则只有一条查询语句。
总结:不管是hibernate 还是ibatis 如果对象设计中有主从对象(主从对象指:一个对象中包含另一个对象,包含对象为主对象,被包含对象为从对象,从对象可为普通javabean或集合)都有可能碰到n+1问题,其中1指进行一条语句查询到n个主对象,而n指n个主对象要将从对象关联出来要进行n次查询。而解决方案不管怎样就是通过数据库join查询。而hibernate比较复杂点有很多缓存可用,所以具体的时候会有点差异。n+1并不是问题,更是一种策略。
0 0
- hibernate ibatis n+1
- ibatis N+1问题
- ibatis N+1问题
- Hitis V1.1 [Hibernate-ibatis]
- iBATIS - Hibernate
- iBatis学习(五) 避免SELECT N+1
- [ibatis]:延迟加载和N+1问题
- Hibernate n+1问题
- hibernate N+1问题
- hibernate N+1问题
- Hibernate: N+1 problem
- Hibernate N+1 问题
- hibernate 1+N问题
- hibernate n+1查询
- Hibernate n+1问题
- hibernate:1+N问题
- Hibernate N+1 问题
- Hibernate n+1问题
- javase复习-----------2
- JS中showModalDialog 详细使用
- convertToWorldSpace convertToNodeSpace
- Java实例讲解-交通灯管理系统
- 线程___同步
- hibernate ibatis n+1
- jsp HttpServletRequest
- C#:禁止在DataGridView控件中添加和删除行
- 《转自知乎》那些年入上百万的人是如何做到的?他们的平均年龄是多少?白手起家的到底多不多?
- jquery操作select(取值,设置选中)
- Java实例讲解-银行业务调度系统
- hdu 2072 单词数
- Java swing 计算器
- C#:使用Column和Rows属性添加数据