Mybatis多表联查范例

一时失言乱红尘 2024-03-24 14:57 56阅读 0赞

<?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.hspedu.seckill.mapper.GoodsMapper">
        
        <resultMap id="BaseResultMap" type="com.hspedu.seckill.pojo.Goods">
            <id column="id" property="id" />
            <result column="goods_name" property="goodsName" />
            <result column="goods_title" property="goodsTitle" />
            <result column="goods_img" property="goodsImg" />
            <result column="goods_detail" property="goodsDetail" />
            <result column="goods_price" property="goodsPrice" />
            <result column="goods_stock" property="goodsStock" />
        </resultMap>
        <select id="findGoodsVo" resultType="com.hspedu.seckill.vo.GoodsVo">
            SELECT
                g.id,
                g.goods_name,
                g.goods_title,
                g.goods_img,
                g.goods_detail,
                g.goods_price,
                g.goods_stock,
                sg.seckill_price,
                sg.stock_count,
                sg.start_date,
                sg.end_date
            FROM t_goods g
            LEFT JOIN t_seckill_goods sg
            ON g.id = sg.goods_id
        </select>
        <select id="findGoodsVoByGoodsId" resultType="com.hspedu.seckill.vo.GoodsVo">
            SELECT
                g.id,
                g.goods_name,
                g.goods_title,
                g.goods_img,
                g.goods_detail,
                g.goods_price,
                g.goods_stock,
                sg.seckill_price,
                sg.stock_count,
                sg.start_date,
                sg.end_date
            FROM t_goods g
            LEFT JOIN t_seckill_goods sg
            ON g.id = sg.goods_id
            WHERE g.id=#{goodsId}
        </select>
    
    </mapper>

其实现的接口是

public interface GoodsMapper extends BaseMapper<Goods> {
    
        //获取商品列表-秒杀
        List<GoodsVo> findGoodsVo();
    
        //获取指定商品详情-根据id
        GoodsVo findGoodsVoByGoodsId(Long goodsId);
    }

其返回的VO对象是

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class GoodsVo extends Goods {
    
        private BigDecimal seckillPrice;
    
        private Integer stockCount;
    
        private Date startDate;
    
        private Date endDate;
    
        //如果后面有需求，也可以做修改
    }