mybatis sql一对多查询返回对象或list中包含子list

最新推荐文章于 2023-07-22 09:04:48 发布

原创最新推荐文章于 2023-07-22 09:04:48 发布 · 7.7k 阅读

21 ·

本内容遵循CC 4.0 BY-SA版权协议

mybatis 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了使用MyBatis进行多表关联查询的方法，包括实体类设计、XML文件中resultMap的配置、SQL语句的编写以及通过PostMan验证查询结果的过程。

数据库对应关系如下图：两个表的关联关系是 store_code 和 sku_code 同时相等

方式1：

要求查询结果(图一)

"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>

5.用postMan调用接口，查看返回结果如上面图一。

方式二

<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMapJobVoUsers" type="com.ampc.system.biz.model.vo.JobVo">
    <id column="id" property="id" />
    <result column="job_code" property="jobCode" />
    <result column="name" property="name" />
    <result column="enabled" property="enabled" />
    <result column="sort" property="sort" />
    <result column="dept_id" property="deptId" />
    <result column="role_id" property="roleId" />
    <result column="create_time" property="createTime" />
    <result column="create_by" property="createBy" />
    <result column="update_time" property="updateTime" />
    <result column="update_by" property="updateBy" />
    <result column="department" property="department" />
    <result column="job_responsibility" property="jobResponsibility" />
    <result column="is_delete" property="isDelete" />
    <collection property="users" ofType="com.ampc.system.biz.model.vo.UserSmallVo">
        <result column="userId" property="userId"/>
        <result column="username" property="username"/>
        <result column="realName" property="realName"/>
        <result column="deptId" property="deptId"/>
        <result column="deptName" property="deptName"/>
    </collection>
</resultMap>