列表中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>
查询结果如下: