MYBATIS-PLUS关联查询，一对一、一对多

原文地址：https://www.freesion.com/article/77301045065/

mybatis-plus没有提供关联查询的crud接口，所以本质上还是使用mybatis方法。

一对一

商品表中的classify_id关联商品分类表中的id
返回商品列表数据时数据内包含商品分类信息

service

 /**
     * 获取商品列表
     *
     * @param form
     */
    @Override
    public Result goodsList(GoodsDto form) {
        Page page = new Page();
        page.setCurrent(form.getPage());
        page.setSize(form.getSize());
        IPage<Map> rows = goodsMapper.goodsList(page, form);
        if (rows != null) {
            return ResultGenerate.genSuccessResult(rows);
        } else {
            return ResultGenerate.genErroResult("商品信息添加失败，请重试！");
        }
    }

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

mapper

package com.ozomall.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ozomall.entity.GoodsDto;
import org.apache.ibatis.annotations.Mapper;

import java.util.Map;

@Mapper
public interface GoodsMapper extends BaseMapper<GoodsDto> {
    IPage<Map> goodsList(Page page,GoodsDto form);
}

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

mapper.xml

需要注意俩个表中如果有重名的字段需要加columnPrefix属性起个别名
sql语句也要使用as起个一样别名

    <resultMap id="goodsMap" type="com.ozomall.vo.GoodsVo">
        <id property="id" column="id"></id>
        <result property="goodsName" column="goods_name"></result>
        <result property="goodsPrice" column="goods_price"></result>
        <result property="commentCount" column="comment_count"></result>
        <result property="sales" column="sales"></result>
        <result property="classifyId" column="classify_id"></result>
        <result property="createTime" column="create_time"></result>
        <result property="status" column="status"></result>
        <result property="step" column="step"></result>
        <result property="cover" column="cover"></result>
        <association property="classify" javaType="com.ozomall.entity.AdminClassifyDto" columnPrefix="classify_">
            <id property="id" column="id"></id>
            <result property="parentId" column="parent_id"/>
            <result property="classifyLevel" column="classify_level"/>
            <result property="name" column="name"/>
            <result property="createTime" column="create_time"/>
        </association>
    </resultMap>
    <select id="goodsList" parameterType="com.ozomall.entity.GoodsDto" resultMap="goodsMap">
        select goods.*,admin_classify.*,admin_classify.id as classify_id from goods left join admin_classify on
        goods.classify_id=admin_classify.id
        where goods.del=0
        <if test="form.goodsName != null">
            and goods.goods_name=#{form.goodsName}
        </if>
        <if test="form.classifyId != null">
            and goods.classify_id=#{form.classifyId}
        </if>
    </select>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30

返回结果

一对多

商品属性表中的id对应商品属性值表中的goods_attr_id
查询商品属性时包含此商品属性下的所有值

service

 /**
     * 获取商品属性
     *
     * @param form
     */
    @Override
    public Result getGoodsAttr(GoodsAttrDto form) {
        List<GoodsAttrDto> rows = goodsAttrMapper.goodsAttr(null, form);
        if (rows != null) {
            return ResultGenerate.genSuccessResult(rows);
        } else {
            return ResultGenerate.genErroResult("失败！");
        }
    }

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

mapper

package com.ozomall.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ozomall.entity.GoodsAttrDto;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface GoodsAttrMapper extends BaseMapper<GoodsAttrDto> {
    List<GoodsAttrDto> goodsAttr(Page page, GoodsAttrDto form);
}

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

mapper.xml

注意别名

    <resultMap id="goodsAttrMap" type="com.ozomall.entity.GoodsAttrDto">
        <id property="id" column="id"></id>
        <result property="goodsId" column="goods_id"></result>
        <result property="name" column="name"></result>
        <collection property="children" ofType="com.ozomall.entity.GoodsAttrValDto" columnPrefix="children_">
            <id property="id" column="id"></id>
            <result property="goodsAttrId" column="goods_attr_id"></result>
            <result property="value" column="value"></result>
        </collection>
    </resultMap>
    <select id="goodsAttr" parameterType="com.ozomall.entity.GoodsAttrDto" resultMap="goodsAttrMap">
        select
        goods_attr.*,
        goods_attr_val.id as children_id,
        goods_attr_val.goods_attr_id as children_goods_attr_id,
        goods_attr_val.value as children_value
         from goods_attr left join goods_attr_val on goods_attr.id=goods_attr_val.goods_attr_id
        where goods_attr.goods_id=#{form.goodsId}
    </select>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19

返回结果