MYBATIS SQL一对多查询 返回对象或LIST中包含子LIST
标签： mybatis
数据库对应关系如下图 ： 两个表的关联关系 是 store_code 和 sku_code 同时相等
要求查询结果(图一)
"list": [
{
"storeCode": "0001",
"skuCode": "0200000306",
"proStatus": 0,
"curNumber": 3,
"batchList": [
{
"id": "b55ad08528bd42049a0ba6effa3e6e1e",
"storeCode": "0001",
"skuCode": "0200000306",
"providerCode": "000002",
"batchNumber": "1906170004",
"price": 5000,
"curNumber": 2
},
{
"id": "f8c5fdc911c841ae8ef4278a504b9a80",
"storeCode": "0001",
"skuCode": "0200000306",
"providerCode": "000017",
"batchNumber": "1907160001",
"price": 2000,
"curNumber": 1
}
]
},
{
"storeCode": "0001",
"skuCode": "52020103",
"proStatus": 0,
"curNumber": 2,
"batchList": [
{
"id": "caca5271355c4139834dc1069a8290ff",
"storeCode": "0001",
"skuCode": "52020103",
"providerCode": "000017",
"batchNumber": "1907160001",
"price": 0,
"curNumber": 2
}
]
}
]
实现方式：
1.创建两张表对应的实体类：
public class SkuStock {
@FieldNote("店代码")
private String storeCode;
@FieldNote("商品SKU编码")
private String skuCode;
@FieldNote("促销状态:0普通商品1促销商品")
private Integer proStatus;
@FieldNote("商品现存")
private java.math.BigDecimal curNumber;
@FieldNote("需要返回的批次列表") // 返回数据要包含的list
private List<SkuStockBatch> batchList;
}
public class SkuStockBatch {
@FieldNote("店代码")
private String storeCode;
@FieldNote("商品SKU编码")
private String skuCode;
@FieldNote("供应商编码")
private String providerCode;
@FieldNote("批号")
private String batchNumber;
@FieldNote("零售价")
private java.math.BigDecimal price;
@FieldNote("商品现存")
private java.math.BigDecimal curNumber;
}
2. xml文件resultMap的写法
<resultMap type="com.a.b.model.sku.SkuStock" id="ResultMap"> <id column="id" property="id" jdbcType="VARCHAR"/> <id column="store_code" property="storeCode" jdbcType="VARCHAR"/> <id column="sku_code" property="skuCode" jdbcType="VARCHAR"/> <id column="pro_status" property="proStatus" jdbcType="INTEGER"/> <id column="no_number" property="noNumber" jdbcType="DECIMAL"/> //property="batchList" ,要与第一步对象中定义的list属性名称一样 <collection property="batchList" resultMap="BatchListResultMap"/> </resultMap> <resultMap type="com.a.b.model.sku.SkuStockBatch" id="BatchListResultMap"> <id column="ssb_id" property="id" jdbcType="VARCHAR"/> <id column="store_code" property="storeCode" jdbcType="VARCHAR"/> <id column="provider_code" property="providerCode" jdbcType="VARCHAR"/> <id column="sku_code" property="skuCode" jdbcType="VARCHAR"/> <id column="batch_number" property="batchNumber" jdbcType="VARCHAR"/> <id column="price" property="price" jdbcType="DECIMAL"/> <!--由于SkuStockBatch表的cur_number和id字段与主表SkuStock重复 所以去了别名区分解析映射结果--> <id column="ssb_cur_number" property="curNumber" jdbcType="DECIMAL"/> </resultMap>
3.定义一个BathList_Column_List，方便多个查询sql中引入不必重复写
<sql id="BathList_Column_List"> ssb.id as ssb_id, ssb.store_code, ssb.provider_code, ssb.sku_code,ssb.batch_number, ssb.price, ssb.cur_number as ssb_cur_number </sql>
4.SQL的写法，查询所有的Batch ,并包含订单详情batchList的对象集合，引入前面已经定义好的BathList_Column_List
<select id="findAll" resultMap="ResultMap"> SELECT ss.*,<include refid="BathList_Column_List"/> FROM sku_stock ss left join sku_stock_batch ssb on ssb.store_code=ss.store_code and ssb.sku_code = ss.sku_code order by ss.create_time desc </select>
原文地址 https://i.cnblogs.com/posts/edit