SQL-MyBatis级联查询

列表中item也存在列表的情况下使用，比如查询多个订单，每个订单又有多个商品。要实现这种可以先查询订单列表，然后遍历每个订单根据订单号再查询订单商品，第二种方法就是级联查询，即通过SQL实现。

如需求：查询订单列表，每个订单有订单基本信息、商家名称、头像、多个商品

1.先实现根据订单号查询商品列表：
商品实体：

public class OrderListGoods {

	private String goodsId;
	private String goodsName;
	private String goodsDesc;
	private String goodsImageUrl;
	private int goodsCoins;
	private int goodsPrice;
	....
	//set get方法省略
}

Mapper.java

public interface OrderGoodsMapper {
    List<OrderListGoods> selectGoodsListByOrderNo(@Param("orderNum")String orderNum);
}

Mapper.xml

<select id="selectGoodsListByOrderNo" resultType="com.streamlet.pojo.resultdto.OrderListGoods">
	SELECT
	goods_id as goodsId,
	goods_name as goodsName,
	goods_desc as goodsDesc,
	pics as goodsImagePics,
	original_coins as coins,
	original_price as prices
	FROM order_goods
	WHERE order_no=#{orderNum}
  </select>

2.编写查询订单列表
订单实体：

public class OrderListBean {

	private String orderNo;
	private String salerId;
	private Integer orderPrice;
	private Integer orderCoins;
	private Integer orderStatus;
	private String salerName;
	private String salerAvatar;
	private Date createTime;
	private List<OrderListGoods> goodsList;
    ...
    //set get方法省略
	
}

Mapper.java

public interface OrderMapper {
   List<OrderListBean> searchOrderList(String buyId);
}

Mapper.xml

 <select id="searchOrderList" resultMap="UserOrderGoodsMap" parameterType="java.lang.String" >
   select
    _order.order_no as orderNo,
    _order.saler_id as salerId,
    usr.name as salerName,
    usr.headimg as salerAvatar,
    _order.order_price as orderPrice,
    _order.order_coins as orderCoins,
    _order.order_status as orderStatus,
    _order.create_time as createTime
    from `order` _order
    INNER JOIN baseuser usr ON _order.saler_id=usr.user_id   
    where _order.buyer_id=#{buyId}
  </select>

<resultMap id="UserOrderGoodsMap" type="com.streamlet.pojo.OrderListBean">
		<result property="orderNo" column="orderNo" />
		<result property="salerId" column="salerId" />
		<result property="salerName" column="salerName" />
		<result property="salerAvatar" column="salerAvatar" />
		<result property="orderPrice" column="orderPrice" />
		<result property="orderCoins" column="orderCoins" />
		<result property="orderStatus" column="orderStatus" />
		<result property="createTime" column="createTime" />
		<association property="goodsList"
		   select="com.streamlet.mapper.OrderGoodsMapper.selectGoodsListByOrderNo" column="orderNo">
		</association>
	</resultMap>

查询结果如下：