1.查询的数据对象
@Data
public class JdOrder implements Serializable{
private Long orderId;
private Long finishTime;
private Integer orderEmt;
private Long orderTime;
private Long parentId;
private String payMonth;
private Integer plus;
private Long popId;
private Long unionId;
private String ext1;
private Integer validCode;
private String hasMore;
//预估佣金
private Double estimateFee;
//商户名称
private String merchantName;
//顾客号码
private String customerId;
**//返回数据要包含的list**
private List<JdSkuList> jdSkuLists;
}
2.返回数据中被包含的list对象
@Data
public class JdSkuList extends JdSkuListKey implements Serializable{
private Double actualCosPrice;
private Double actualFee;
private Double commissionRate;
private Double estimateCosPrice;
private Double estimateFee;
private Double finalRate;
private Long cid1;
private Long frozenSkuNum;
private String pid;
private Long positionId;
private Double price;
private Long cid2;
private Long siteId;
private String skuName;
private Long skuNum;
private Long skuReturnNum;
private Double subSideRate;
private Double subsidyRate;
private Long cid3;
private String unionAlias;
private String unionTag;
private Integer unionTrafficGroup;
private Integer validCode;
private String subUnionId;
private Integer traceType;
private Integer payMonth;
private Long popId;
private String ext1;
private String merchantName;
}
3.resultMap的写法
<resultMap id="BaseResultMap" type="com.luer.JD.bean.JdOrder">
<id column="order_id" property="orderId" jdbcType="BIGINT"/>
<result column="finish_time" property="finishTime" jdbcType="BIGINT"/>
<result column="order_emt" property="orderEmt" jdbcType="INTEGER"/>
<result column="order_time" property="orderTime" jdbcType="BIGINT"/>
<result column="parent_id" property="parentId" jdbcType="BIGINT"/>
<result column="pay_month" property="payMonth" jdbcType="VARCHAR"/>
<result column="plus" property="plus" jdbcType="INTEGER"/>
<result column="pop_id" property="popId" jdbcType="BIGINT"/>
<result column="union_id" property="unionId" jdbcType="BIGINT"/>
<result column="ext1" property="ext1" jdbcType="VARCHAR"/>
<result column="valid_code" property="validCode" jdbcType="INTEGER"/>
<result column="has_more" property="hasMore" jdbcType="VARCHAR"/>
//property="jdSkuLists" ,要与第一步对象中定义的list属性名称一样
<collection property="jdSkuLists" resultMap="JdSkuListResultMap"/>
</resultMap>
<resultMap id="JdSkuListResultMap" type="com.luer.JD.bean.JdSkuList">
<id column="order_id" property="orderId" jdbcType="BIGINT"/>
<id column="sku_id" property="skuId" jdbcType="BIGINT"/>
<result column="actual_cos_price" property="actualCosPrice" jdbcType="DOUBLE"/>
<result column="actual_fee" property="actualFee" jdbcType="DOUBLE"/>
<result column="commission_rate" property="commissionRate" jdbcType="DOUBLE"/>
<result column="estimate_cos_price" property="estimateCosPrice" jdbcType="DOUBLE"/>
<result column="estimate_fee" property="estimateFee" jdbcType="DOUBLE"/>
<result column="final_rate" property="finalRate" jdbcType="DOUBLE"/>
<result column="cid1" property="cid1" jdbcType="BIGINT"/>
<result column="frozen_sku_num" property="frozenSkuNum" jdbcType="BIGINT"/>
<result column="pid" property="pid" jdbcType="VARCHAR"/>
<result column="position_id" property="positionId" jdbcType="BIGINT"/>
<result column="price" property="price" jdbcType="DOUBLE"/>
<result column="cid2" property="cid2" jdbcType="BIGINT"/>
<result column="site_id" property="siteId" jdbcType="BIGINT"/>
<result column="sku_name" property="skuName" jdbcType="VARCHAR"/>
<result column="sku_num" property="skuNum" jdbcType="BIGINT"/>
<result column="sku_return_num" property="skuReturnNum" jdbcType="BIGINT"/>
<result column="sub_side_rate" property="subSideRate" jdbcType="DOUBLE"/>
<result column="subsidy_rate" property="subsidyRate" jdbcType="DOUBLE"/>
<result column="cid3" property="cid3" jdbcType="BIGINT"/>
<result column="union_alias" property="unionAlias" jdbcType="VARCHAR"/>
<result column="union_tag" property="unionTag" jdbcType="VARCHAR"/>
<result column="union_traffic_group" property="unionTrafficGroup" jdbcType="INTEGER"/>
<result column="valid_code" property="validCode" jdbcType="INTEGER"/>
<result column="sub_union_id" property="subUnionId" jdbcType="VARCHAR"/>
<result column="trace_type" property="traceType" jdbcType="INTEGER"/>
<result column="pay_month" property="payMonth" jdbcType="INTEGER"/>
<result column="pop_id" property="popId" jdbcType="BIGINT"/>
<result column="ext1" property="ext1" jdbcType="VARCHAR"/>
</resultMap>
4.定义一个JdSkuList_Column_List,方便多个查询sql中引入不必重复写
<sql id="JdSkuList_Column_List" >
jsl.order_id, jsl.sku_id, jsl.actual_cos_price, jsl.actual_fee, jsl.commission_rate, jsl.estimate_cos_price,
jsl.estimate_fee, jsl.final_rate, jsl.cid1, jsl.frozen_sku_num, jsl.pid, jsl.position_id, jsl.price, jsl.cid2, jsl.site_id,
jsl.sku_name, jsl.sku_num, jsl.sku_return_num, jsl.sub_side_rate, jsl.subsidy_rate, jsl.cid3, jsl.union_alias,
jsl.union_tag, jsl.union_traffic_group, jsl.valid_code, jsl.sub_union_id, jsl.trace_type, jsl.pay_month,
jsl.pop_id, jsl.ext1
</sql>
5.SQL的写法,查询所有的JdOrder ,并包含订单详情JdSkuList的对象集合,引入前面已经定义好的JdSkuList_Column_List
<select id="selectOrder" resultMap="BaseResultMap">
SELECT j.*,<include refid="JdSkuList_Column_List"/> FROM jd_order j
left join jd_sku_list jsl on jsl.order_id=j.order_id
order by j.order_time desc
</select>
6.SQL也可以全部写出,个人喜欢引入的方法,看着清爽,节约资源
<select id="selectOrder" resultMap="BaseResultMap">
SELECT j.*,
jsl.order_id, jsl.sku_id, jsl.actual_cos_price, jsl.actual_fee, jsl.commission_rate, jsl.estimate_cos_price,
jsl.estimate_fee, jsl.final_rate, jsl.cid1, jsl.frozen_sku_num, jsl.pid, jsl.position_id, jsl.price, jsl.cid2, jsl.site_id,
jsl.sku_name, jsl.sku_num, jsl.sku_return_num, jsl.sub_side_rate, jsl.subsidy_rate, jsl.cid3, jsl.union_alias,
jsl.union_tag, jsl.union_traffic_group, jsl.valid_code, jsl.sub_union_id, jsl.trace_type, jsl.pay_month,
jsl.pop_id, jsl.ext1
FROM jd_order j
left join jd_sku_list jsl on jsl.order_id=j.order_id
order by j.order_time desc
</select>
7.通过postman测试发送请求,查询出两条结果,第一条包含jdSkuLists中含有三条数据,第二条包含一条数据,测试成功
{
"code": 200,
"msg": "success",
"data": [
{
"orderId": 86475668988,
"finishTime": 1556167285000,
"orderEmt": 2,
"orderTime": 1555661964000,
"parentId": 0,
"payMonth": "0",
"plus": 0,
"popId": 10084045,
"unionId": 1001466274,
"ext1": "",
"validCode": 17,
"hasMore": "false",
"estimateFee": null,
"merchantName": null,
"customerId": null,
"jdSkuLists": [
{
"orderId": 86475668988,
"skuId": 44132717554,
"actualCosPrice": 0,
"actualFee": 0,
"commissionRate": 5,
"estimateCosPrice": 0,
"estimateFee": 0,
"finalRate": 90,
"cid1": 6144,
"frozenSkuNum": 0,
"pid": "",
"positionId": 0,
"price": 0,
"cid2": 12042,
"siteId": 55000,
"skuName": "珠宝盒 赠品",
"skuNum": 1,
"skuReturnNum": 0,
"subSideRate": 90,
"subsidyRate": 0,
"cid3": 12055,
"unionAlias": "",
"unionTag": "00000000",
"unionTrafficGroup": 4,
"validCode": 17,
"subUnionId": "luertest",
"traceType": 2,
"payMonth": 0,
"popId": 10084045,
"ext1": "",
"merchantName": null
},
{
"orderId": 86475668988,
"skuId": 40043797202,
"actualCosPrice": 29.9,
"actualFee": 5.38,
"commissionRate": 20,
"estimateCosPrice": 29.9,
"estimateFee": 5.38,
"finalRate": 90,
"cid1": 6144,
"frozenSkuNum": 0,
"pid": "",
"positionId": 0,
"price": 129.9,
"cid2": 12042,
"siteId": 55000,
"skuName": "千楼珠宝 淡水珍珠耳钉 紫色 镜面光 高品质 8-9mm馒头珍珠耳环 S925银 一对装 ",
"skuNum": 1,
"skuReturnNum": 0,
"subSideRate": 90,
"subsidyRate": 0,
"cid3": 12055,
"unionAlias": "",
"unionTag": "00000000",
"unionTrafficGroup": 4,
"validCode": 17,
"subUnionId": "luertest",
"traceType": 2,
"payMonth": 0,
"popId": 10084045,
"ext1": "",
"merchantName": null
},
{
"orderId": 86475668988,
"skuId": 44132198245,
"actualCosPrice": 0,
"actualFee": 0,
"commissionRate": 5,
"estimateCosPrice": 0,
"estimateFee": 0,
"finalRate": 90,
"cid1": 6144,
"frozenSkuNum": 0,
"pid": "",
"positionId": 0,
"price": 0,
"cid2": 12042,
"siteId": 55000,
"skuName": "硅胶耳堵 配件 1对 限量赠送 赠完为止",
"skuNum": 1,
"skuReturnNum": 0,
"subSideRate": 90,
"subsidyRate": 0,
"cid3": 12054,
"unionAlias": "",
"unionTag": "00000000",
"unionTrafficGroup": 4,
"validCode": 17,
"subUnionId": "luertest",
"traceType": 2,
"payMonth": 0,
"popId": 10084045,
"ext1": "",
"merchantName": null
}
]
},
{
"orderId": 85562762227,
"finishTime": 1555215459000,
"orderEmt": 2,
"orderTime": 1554715488000,
"parentId": 0,
"payMonth": "0",
"plus": 0,
"popId": 690686,
"unionId": 1001466274,
"ext1": "",
"validCode": 17,
"hasMore": "false",
"estimateFee": null,
"merchantName": null,
"customerId": null,
"jdSkuLists": [
{
"orderId": 85562762227,
"skuId": 43659908490,
"actualCosPrice": 24.8,
"actualFee": 1.12,
"commissionRate": 5,
"estimateCosPrice": 24.8,
"estimateFee": 1.12,
"finalRate": 90,
"cid1": 1320,
"frozenSkuNum": 0,
"pid": "",
"positionId": 0,
"price": 29.8,
"cid2": 5019,
"siteId": 0,
"skuName": "芗园黑糖红枣桂圆茶 便携独立包装台湾姨妈红枣桂圆茶姜汤姜母茶120g",
"skuNum": 1,
"skuReturnNum": 0,
"subSideRate": 90,
"subsidyRate": 0,
"cid3": 5023,
"unionAlias": "",
"unionTag": "00000000",
"unionTrafficGroup": 5,
"validCode": 17,
"subUnionId": "luertest",
"traceType": 2,
"payMonth": 0,
"popId": 690686,
"ext1": "",
"merchantName": null
}
]
}
],
"time": 1558491931288
}