博客

Mybatis之collection标签嵌套查询(select)跨xml的demo

业务需求：手机商城分页获取商品的全部规格信息和规格的全部选项值。

手机规格类名称：GoodsSpecification

package com.giao.store.entity;

import java.util.List;
import lombok.Data;
/**
 * author：GZC
 * createTime：2020/11/6 20:20
 * description：商品规格类
 **/
 @Data
public class GoodsSpecification{
    private String id;                    //商品规格主键自增id
    private String goodsId;               //商品唯一id
    private String name;                  //规格名称
    private String status;                //状态0有1无
    private String createTime;			  //创建时间
    //商品规选项格集合
    private List<GoodsSpecificationOption> goodsSpecificationOptionList; 
}

规格选项值类名称：GoodsSpecificationOption

package com.giao.store.entity;

import lombok.Data;
/**
 * author：GZC
 * createTime：2020/11/6 20:23
 * description：商品规格选项类
 **/
 @Data
public class GoodsSpecificationOption{
    private String id;                    //商品规格选项主键自增id
    private String specId;                //商品规格唯一id
    private String value;                 //选项值
    private String createTime;			  //创建时间
}

Mapper接口名称：

规格接口：AdminGoodsSpecificationMapper

package com.giao.store.mapper.admin;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.giao.store.entity.GoodsSpecification;
import org.apache.ibatis.annotations.Mapper;

/**
 * author：GZC
 * createTime：2020/11/6 21:21
 * description：商品规格接口
 **/
@Mapper
public interface AdminGoodsSpecificationMapper {
   
   IPage<GoodsSpecification> getGoodsSpecificationAndOptionListDB(Page<GoodsSpecification> page);
}

规格选项接口：AdminGoodsSpecificationOptionMapper

package com.giao.store.mapper.admin;

import com.giao.store.entity.GoodsSpecificationOption;
import org.apache.ibatis.annotations.Mapper;
import java.util.ArrayList;

/**
 * author：GZC
 * createTime：2020/11/6 21:26
 * description：商品规格选项接口
 **/
@Mapper
public interface AdminGoodsSpecificationOptionMapper{

    ArrayList<GoodsSpecificationOption> getGoodsSpecificationOptionListByIdDB(String specId);
}

mapper.xml文件:

规格接口映射xml文件：AdminGoodsSpecificationMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.giao.store.mapper.admin.AdminGoodsSpecificationMapper">

    <resultMap id="getSpecAndOptionList" type="com.giao.store.entity.GoodsSpecification">
        <collection property="goodsSpecificationOptionList"
                    column="id"
                    javaType="ArrayList"
                    ofType="com.giao.store.entity.GoodsSpecificationOption"
                    select="com.giao.store.mapper.admin.AdminGoodsSpecificationOptionMapper.getGoodsSpecificationOptionListByIdDB"/>
    </resultMap>

    <select id="getGoodsSpecificationAndOptionListDB"
            resultMap="getSpecAndOptionList">
         SELECT id,goods_id,name,status,create_time
         FROM g_goods_specification
    </select>
    
</mapper>

规格选项接口映射xml文件：AdminGoodsSpecificationOptionMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.giao.store.mapper.admin.AdminGoodsSpecificationOptionMapper">
    <select id="getGoodsSpecificationOptionListByIdDB"
            resultType="com.giao.store.entity.GoodsSpecificationOption">
        SELECT id,spec_id,value,create_time
        FROM g_goods_specification_option
        WHERE  spec_id=#{id}
    </select>

</mapper>

springbootTest测试类:

@RunWith(SpringRunner.class)
@SpringBootTest
public class GiaoStoreApplicationTests {

    @Autowired(required = false)
    private AdminGoodsSpecificationMapper adminGoodsSpecificationMapper;  
    
	@Test
    public void getGoodsSpecificationTest() {
        Page<GoodsSpecification> page = new Page<>();
        page.setCurrent(1);
        page.setSize(10);
        IPage<GoodsSpecification> gsList =
                        adminGoodsSpecificationMapper.getGoodsSpecificationAndOptionListDB(page);
        for (GoodsSpecification gs : gsList.getRecords()) {
            System.out.print(gs.getName()+": ");
            List<GoodsSpecificationOption> gsoList = 
            			gs.getGoodsSpecificationOptionList();
            for (GoodsSpecificationOption gso : gsoList) {
                System.out.print(gso.getValue()+",");
            }
            System.out.println("");
        }
    }

控制台打印结果：

总结：

collection标签中的select属性填写对应接口的包路径+方法名即可跨xml

.

就此完成！！！