sql-mybatis 查询树结构与递归查询 （一对一与一对多collection和association）

sql-mybatis 查询树结构

• 使用collection 和 association 会遇到mybatis中的懒加载的问题
• 下面的这个是转载的，写的挺不错。转载: https://my.oschina.net/yejunxi/blog/1836329
• 现在是自己的总结的。

表关系中,一对一,一对多的关系查询, 和查询树结构中, 往往要设计到collection和association的使用.今天做了一个查询订单的查询: 查询订单, 并查询订单项, 和每一条的订单项中查出该订单项对应的商店对象和商品对象; 这就典型的一对多的关系要用collection, 多的一方为订单项, 订单项中又和商店和商品的对象是一对一的关系要用 association; 下面是mapper.xml

踩坑

当一个resultMap标签中，既含有association 又含有 collection 时候，两者的顺序是不能变的。必须如下的顺序

<resultMap id="editionAreaResultMap" type="com.basetnt.bss.dto.EditionAreaDTO" extends="com.basetnt.bss.dao.ForumForumMapper.BaseResultMap">
        <association column="fid" property="forumForumField" select="com.basetnt.bss.dao.ForumForumFieldMapper.selectByPrimaryKey"></association >
        <collection column="fid" property="forumForumList" ofType="com.basetnt.bss.entity.ForumForum" select="selectPlateByFid"></collection>
</resultMap>

---

 <resultMap id="omsOrderBaseResultMap" type="com.macro.mall.dto.OmsOrderResultMap" extends="com.macro.mall.mapper.OmsOrderMapper.BaseResultMap">
        <collection column="id" property="itemList" ofType="com.macro.mall.dto.OmsOrderItemResultMap" select="getOrderItemList"></collection>
 </resultMap>
 
 <resultMap id="OmsOrderItemResultMap" type="com.macro.mall.dto.OmsOrderItemResultMap" extends="com.macro.mall.mapper.OmsOrderItemMapper.BaseResultMap">
      <result column="attributevalue_id" property="productAttributeValueId" jdbcType="BIGINT" />
      <result column="product_id" property="productId" jdbcType="BIGINT" />
      <result column="product_attribute_id" property="productAttributeId" jdbcType="BIGINT" />
      <result column="value" property="value" jdbcType="VARCHAR" />
      <result column="shop_id" property="shopId" jdbcType="BIGINT" />
      <result column="prd_att_price" property="prdAttPrice" jdbcType="DECIMAL" />
      <result column="discount" property="discount" jdbcType="DECIMAL" />
      <result column="stock" property="stock" jdbcType="INTEGER" />
      <result column="sell_count" property="sellCount" jdbcType="INTEGER" />
      <association  column="shop_id" property="shop" select="com.macro.mall.mapper.CmsShopMapper.selectByPrimaryKey"></association >
      <association column="product_id" property="product" select="com.macro.mall.mapper.PmsProductMapper.selectByPrimaryKey"></association>
  </resultMap>

  <select id="getOrderItemList" resultMap="OmsOrderItemResultMap">
	SELECT t3.id as attributevalue_id, t2.*, t3.*
       FROM
           oms_order_item t2,
           pms_product_attribute_value t3
       WHERE
             t2.order_id =#{orderId,jdbcType=BIGINT}
       AND t2.specifications_id = t3.id
  </select>

  <select id="anotherOrder" resultMap="omsOrderBaseResultMap">
     SELECT * FROM oms_order WHERE id = #{orderId,jdbcType=BIGINT} AND delete_status = 0
  </select>
  

下面是实体

@Data
public class OmsOrderItemResultMap extends OmsOrderItem {
    private Long productAttributeValueId;
    private Long productAttributeId;
    private String value;
    private Long shopId;
    private BigDecimal discount;//折扣价
    private Integer stock;//库存
    private BigDecimal prdAttPrice;
    private Integer sellCount;
    private CmsShop shop;
    private PmsProduct product;
}

@Data
public class OmsOrderResultMap extends OmsOrder {
    private List<OmsOrderItemResultMap> itemList;
}
//接口
 OmsOrderResultMap anotherOrder(Long orderId);
//service
OmsOrderResultMap result = omsOrderDao.anotherOrder(orderId);

• 总结

• 首先要理清要查询的结构是一对多的关系, 还是一对一的关系;如果是一对一则用association ,如果是一对多关系,要用collection

• 一对多关系中,查询一的一方,在一的一方中含有多的一方的集合collection属性, 在多的一方中含有一的一方的外键.在实体中表现为,在order属性中含有 List itemList 集合.在xml 表现为 标签; 标签中含有几个属性:column 表示一的一方中要传入多的一方的外键的那个列名; property 表示在一的一方的实体中含有多的一方的集合的属性名称; ofType 表示集合属性中的类型; select 表示返回集合属性的select id;

• 一对一关系 可以查询的是一对一的关系表, 也可以查询多对一中一的一方. 标签; 标签中含有几个属性: column 表示要传入的列名称; property 表示属性实体中名称; select 表示返回该属性的select查询;

查询权限树,采用递归的算法

 <resultMap id="storeAccessBaseTree" type="com.basetnt.shop.form.StoreAccessTree">
    <id column="access_id" jdbcType="INTEGER" property="accessId" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="url" jdbcType="VARCHAR" property="url" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <result column="sort" jdbcType="TINYINT" property="sort" />
    <collection column="access_id" property="children" ofType="com.basetnt.shop.form.StoreAccessTree" select="getStoreAccessNextTree"></collection>
  </resultMap>
  <resultMap id="storeAccessNextTree" type="com.basetnt.shop.form.StoreAccessTree">
    <id column="access_id" jdbcType="INTEGER" property="accessId" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="url" jdbcType="VARCHAR" property="url" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <result column="sort" jdbcType="TINYINT" property="sort" />
    <collection column="access_id" property="children" ofType="com.basetnt.shop.form.StoreAccessTree" select="getStoreAccessNextTree"></collection>
  </resultMap>
  
  <select id="getStoreAccessNextTree" resultMap="storeAccessNextTree">
		SELECT access_id,`name`,url,parent_id,sort FROM btshop_store_access WHERE parent_id = #{parentId}
  </select>
  
  <select id="getStoreAccessBaseTree" resultMap="storeAccessBaseTree">
		SELECT access_id,`name`,url,parent_id,sort FROM btshop_store_access WHERE parent_id = 0
  </select>

  <!--根据角色Id查询该角色的权限-->
  <select id="getStoreAccessBaseTreeByRoleId" resultMap="storeAccessBaseTree">
	SELECT access_id,`name`,url,parent_id,sort FROM btshop_store_access WHERE parent_id = 0 and access_id in(
      SELECT access_id from btshop_store_role_access where role_id = #{id,jdbcType=INTEGER}
    )
  </select>