mybatisSql查询 返回对象或者list数据中包含一个对象的list集合

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 }