自定义查询语句 mapper实现 参数类型 @Param("paramsMap")

 功能：查询每个一级分类的最新6条商品数据

一、数据库表结构

create table `foodie-shop-dev`.category
(
    id        int auto_increment comment '主键'
        primary key,
    name      varchar(32) not null comment '分类名称',
    type      int         not null comment '分类类型',
    father_id int         not null comment '父id',
    logo      varchar(64) null comment '图标',
    slogan    varchar(64) null comment '口号',
    cat_image varchar(64) null comment '分类图',
    bg_color  varchar(32) null comment '背景颜色'
)
    comment '商品分类 ' charset = utf8mb4;

create table `foodie-shop-dev`.items
(
    id            varchar(64) not null comment '商品主键id'
        primary key,
    item_name     varchar(32) not null comment '商品名称 商品名称',
    cat_id        int         not null comment '分类外键id 分类id',
    root_cat_id   int         not null comment '一级分类外键id',
    sell_counts   int         not null comment '累计销售 累计销售',
    on_off_status int         not null comment '上下架状态 上下架状态,1:上架 2:下架',
    content       text        not null comment '商品内容 商品内容',
    created_time  datetime    not null comment '创建时间',
    updated_time  datetime    not null comment '更新时间'
)
    comment '商品表' charset = utf8mb4;

create table `foodie-shop-dev`.items_img
(
    id           varchar(64)  not null comment '图片主键'
        primary key,
    item_id      varchar(64)  not null comment '商品外键id 商品外键id',
    url          varchar(128) not null comment '图片地址 图片地址',
    sort         int          not null comment '顺序 图片顺序，从小到大',
    is_main      int          not null comment '是否主图 是否主图，1：是，0：否',
    created_time datetime     not null comment '创建时间',
    updated_time datetime     not null comment '更新时间'
)
    comment '商品图片 ' charset = utf8mb4;

   
二、自定义Sql查询语句

SELECT
  f.id as rootCatId,
    f.`name` as rootCatName,
    f.slogan as slogan,
  f.cat_image as catImage,
    f.bg_color as bgColor,
    i.id as itemId,
    i.item_name as itemName,
    ii.url as itemUrl,
    i.created_time as createdTime

FROM
    category f
    LEFT JOIN items i ON f.id = i.root_cat_id
    LEFT JOIN items_img ii ON i.id = ii.item_id 
WHERE
    f.type = 1 
    AND i.root_cat_id = 7 
    AND ii.is_main = 1 
ORDER BY
    i.created_time DESC 
    LIMIT 0,6

三、Mapper层 xml文件和接口

1、接口定义（定义sql方法名称getSixNewItemsLazy，用在xml）

package com.imooc.mapper;


import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.NewItemsVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface CategoryMapperCustom  {

    public List<CategoryVO> getSubCatList(Integer rootCatId);

    public List<NewItemsVO> getSixNewItemsLazy(@Param("paramsMap") Map<String,Object> map);
}

   2、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.imooc.mapper.CategoryMapperCustom" >
  <resultMap id="myCategoryVO" type="com.imooc.pojo.vo.CategoryVO">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
      <result column="type" property="type"/>
      <result column="fatherId" property="fatherId"/>

      <!--
         collection 标签：用于定义关联的list集合类型的封装规则
         property：对应三级分类的list属性名
         ofType：集合的类型，三级分类的vo
      -->
      <collection property="subCatList" ofType="com.imooc.pojo.vo.SubCategoryVO">
          <id column="subId" property="subId"/>
          <result column="subName" property="subName"/>
          <result column="subType" property="subType"/>
          <result column="subFatherId" property="subFatherId"/>
      </collection>

  </resultMap>

  <select id="getSubCatList"  resultMap="myCategoryVO" parameterType="int">

     SELECT
        f.id AS id,
        f.`name` AS `name`,
        f.type AS type,
        f.father_id AS fatherId,
        c.id AS subId,
        c.NAME AS subName,
        c.type AS subType,
        c.father_id AS subFatherId
    FROM
        category f
    LEFT JOIN
        category c
    ON
        f.id = c.father_id
    WHERE
        f.father_id = #{rootCatId}


  </select>

<resultMap id="myNewItemsVO" type="com.imooc.pojo.vo.NewItemsVO">
    <id column="rootCatId" property="rootCatId"/>
    <result column="rootCatName" property="rootCatName"/>
    <result column="slogan" property="slogan"/>
    <result column="cat_image" property="cat_image"/>
    <result column="bgColor" property="bgColor"/>
  <collection property="simpleItemList" ofType="com.imooc.pojo.vo.SimpleItemVO">
      <id column="itemId" property="itemId"/>
      <result column="itemName" property="itemName"/>
      <result column="itemUrl" property="itemUrl"/>
  </collection>

</resultMap>
    <select id="getSixNewItemsLazy" resultMap="myNewItemsVO" parameterType="Map">
             SELECT
            f.id as rootCatId,
            f.`name` as rootCatName,
            f.slogan as slogan,
            f.cat_image as catImage,
            f.bg_color as bgColor,
            i.id as itemId,
            i.item_name as itemName,
            ii.url as itemUrl,
            i.created_time as createdTime
        FROM
            category f
            LEFT JOIN items i ON f.id = i.root_cat_id
            LEFT JOIN items_img ii ON i.id = ii.item_id
        WHERE
            f.type = 1
            AND i.root_cat_id = #{paramsMap.rootCatId}
            AND ii.is_main = 1
        ORDER BY
            i.created_time DESC
            LIMIT 0,6

    </select>

</mapper>

  3.VO对象定义（目录：com/imooc/pojo/vo/NewItemsVO.java和com/imooc/pojo/vo/SimpleItemVO.java）

      

package com.imooc.pojo.vo;

import java.util.List;

/**
 * 最新商品VO
 */
public class NewItemsVO {

    private  Integer rootCatId;
    private  String  rootCatName;
    private  String  slogan;
    private  String  catImage;
    private  String  bgColor;
    private List<SimpleItemVO> simpleItemList;


    public Integer getRootCatId() {
        return rootCatId;
    }

    public void setRootCatId(Integer rootCatId) {
        this.rootCatId = rootCatId;
    }

    public String getRootCatName() {
        return rootCatName;
    }

    public void setRootCatName(String rootCatName) {
        this.rootCatName = rootCatName;
    }

    public String getSlogan() {
        return slogan;
    }

    public void setSlogan(String slogan) {
        this.slogan = slogan;
    }

    public String getCatImage() {
        return catImage;
    }

    public void setCatImage(String catImage) {
        this.catImage = catImage;
    }

    public String getBgColor() {
        return bgColor;
    }

    public void setBgColor(String bgColor) {
        this.bgColor = bgColor;
    }

    public List<SimpleItemVO> getSimpleItemList() {
        return simpleItemList;
    }

    public void setSimpleItemList(List<SimpleItemVO> simpleItemList) {
        this.simpleItemList = simpleItemList;
    }
}

package com.imooc.pojo.vo;

/**
 * 6个最新商品的简单数据类型
 */
public class SimpleItemVO {
    private  String itemId;
    private  String  itemName;
    private  String  itemUrl;

    public String getItemId() {
        return itemId;
    }

    public void setItemId(String itemId) {
        this.itemId = itemId;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public String getItemUrl() {
        return itemUrl;
    }

    public void setItemUrl(String itemUrl) {
        this.itemUrl = itemUrl;
    }
}

四、service层实现

    1、接口定义（目录：com/imooc/service/CategoryService.java）

package com.imooc.service;

import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.NewItemsVO;

import java.util.List;
import java.util.Map;

public interface CategoryService {
    /**
     *查询所有一级分类
     * @return
     */
    public List<Category> queryAllRootLeverCat();

    /**根据一级分类查询
     *
     * @param rootCatId
     * @return
     */
    public List<CategoryVO> getSubCatList(Integer rootCatId);

    /**
     * 查询一级分类的前6个商品
     * @param rootCatId
     * @return
     */
    public List<NewItemsVO> getSixNewItemsLazy(Integer rootCatId);

}

   2、接口实现（目录：com.imooc.service.impl.CategoryServiceImpl）

     

package com.imooc.service.impl;

import com.imooc.mapper.CarouselMapper;
import com.imooc.mapper.CategoryMapper;
import com.imooc.mapper.CategoryMapperCustom;
import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.NewItemsVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class CategoryServiceImpl implements CategoryService {

    @Autowired
    private CategoryMapper categoryMapper;
    @Autowired
    private CategoryMapperCustom categoryMapperCustom;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<CategoryVO> getSubCatList(Integer rootCatId) {
        return categoryMapperCustom.getSubCatList(rootCatId);
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<Category> queryAllRootLeverCat() {
        Example example = new Example(Carousel.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("type",1);
        List<Category> resList= categoryMapper.selectByExample(example);
        return resList;
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<NewItemsVO> getSixNewItemsLazy(Integer rootCatId) {
        Map<String,Object> map=new HashMap<>();
        map.put("rootCatId",rootCatId);
        return categoryMapperCustom.getSixNewItemsLazy(map);
    }
}

五、API层实现

     方法：sixNewItems

     目录：com.imooc.controller.IndexController

package com.imooc.controller;

import com.imooc.enums.YesOrNo;
import com.imooc.pojo.Carousel;
import com.imooc.pojo.Category;
import com.imooc.pojo.vo.CategoryVO;
import com.imooc.pojo.vo.NewItemsVO;
import com.imooc.service.CarouselService;
import com.imooc.service.CategoryService;
import com.imooc.utils.IMOOCJSONResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import springfox.documentation.annotations.ApiIgnore;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.List;

@Api(value = "首页",tags = "首页展示的相关接口")
@RestController
@RequestMapping("index")
public class IndexController {

    @Autowired
     private CarouselService carouselService;
    @Autowired
    private CategoryService categoryService;

    @ApiOperation(value="获取首页轮播图列表",notes = "获取首页轮播图列表",httpMethod = "GET")
    @GetMapping("/carouse")
    public IMOOCJSONResult carouse() {
        List<Carousel> list= carouselService.queryAll(YesOrNo.YES.type);
        return IMOOCJSONResult.ok(list);
    }

    @ApiOperation(value="获取商品分类(一级分类)",notes = "获取商品分类(一级分类)",httpMethod = "GET")
    @GetMapping("/cats")
    public IMOOCJSONResult cats() {
        List<Category> list= categoryService.queryAllRootLeverCat();
        return IMOOCJSONResult.ok(list);
    }

    @ApiOperation(value="获取商品子分类",notes = "获取商品子分类",httpMethod = "GET")
    @GetMapping("/subCat/{rootCatId}")
    public IMOOCJSONResult subCat(
            @ApiParam(name="rootCatId",value = "一级分类id",required = true)
            @PathVariable Integer rootCatId) {
        if(rootCatId ==null){
            return IMOOCJSONResult.errorMsg("分类不存在");
        }
        List<CategoryVO> list= categoryService.getSubCatList(rootCatId);
        return IMOOCJSONResult.ok(list);
    }

    @ApiOperation(value="查询每个一级分类的最新6条商品数据",notes = "查询每个一级分类的最新6条商品数据",httpMethod = "GET")
    @GetMapping("/sixNewItems/{rootCatId}")
    public IMOOCJSONResult sixNewItems(
            @ApiParam(name="rootCatId",value = "一级分类id",required = true)
            @PathVariable Integer rootCatId) {
        if(rootCatId ==null){
            return IMOOCJSONResult.errorMsg("分类不存在");
        }
        List<NewItemsVO> list= categoryService.getSixNewItemsLazy(rootCatId);
        return IMOOCJSONResult.ok(list);
    }
}