abatis学习笔记(1)----避免N+1 Select

(一)什么是N+1Select问题

通常的N+1查询(1:1和1:M)

<resultMap id="select-product-result" class="product">
<result property="id" column="PRD_ID"/>
<result property="description" column="PRD_DESCRIPTION"/>
<result property="category" column="PRD_CAT_ID" select="selectCategory"/>
</resultMap>
select id="selectCategory" parameterClass="int" resultMap="select-category-result">
select * from CATEGORY where CAT_ID = #value#
</select>
<statements>
<select id="selectProduct" parameterClass="int" resultMap="select-product-result">
select * from PRODUCT where PRD_ID = #value#
</select>

所谓N+1问题即在查询N次父记录的同时查询N次子记录

 

 

 

(二)解决N+1Select问题 

第一种：构建多表连接sql语句

第二种：iBATIS.NET DataMapper 1.1中，添加了groupBy特性用来解决N+1问题                       

<resultMap class="com.tj_zhongzhixin.entity.Store" id="storeMap" groupBy="sto_id">
       <result property="sto_id" column="sto_id"/>
       <result property="sto_name" column="sto_name"/>
       <result property="sto_contactphone" column="sto_contactphone"/>
       <result property="sto_time" column="sto_time" javaType="string" />
       <result property="listQuote" resultMap="getListQuote" />
    </resultMap>
    <resultMap class="com.tj_zhongzhixin.entity.Quote" id="getListQuote">
     <result property="quoId" column="quo_id" />
     <result property="quoPrice" column="quo_price" />
    </resultMap>
               
      <statement id="selectListStore" resultClass="com.tj_zhongzhixin.entity.Store" resultMap="storeMap" parameterClass="com.tj_zhongzhixin.common.util.PageResult" >
      select q.quo_id,q.quo_price,s.sto_id,s.sto_name,s.sto_contactphone,s.sto_time from store as s,quote as q where s.sto_id=q.quo_storeId limit #pageSize# offset #throwRows#
      </statement>

groupby属性解决了查询N次父记录的同时查询N次子记录

他只执行一次查询（父查询和子查询同时执行）

http://hi.baidu.com/zhifeichuan/