自定义通用mapper

自定义通用mapper多用于中间表关联查询

（一）根据分类名称查询品牌列表

brand

controller

   /**
     * 根据商品分类查询品牌列表
     * @param categoryName
     * @return
     */
    @GetMapping("/category/{categoryName}")
    public Result findListByCategoryName(@PathVariable String categoryName){
        List<Map> brandList = brandService.findListByCategoryName( categoryName );
        return new Result( true,StatusCode.OK,"查询成功", brandList);
    }

service

public List<Map> findListByCategoryName( String categoryName);

serviceimpl

@Override
public List<Map> findListByCategoryName(String categoryName) {
    return brandMapper.findListByCategoryName( categoryName );
}

mapper

import com.changgou.pojo.Brand;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

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

public interface BrandMapper extends Mapper<Brand> {

    /**
     * 根据分类名称查询品牌列表
     * @param categoryName
     * @return
     */
    @Select( "SELECT b.`name`,b.`image` " +
            "FROM tb_brand b ,tb_category c ,tb_category_brand cb " +
            "WHERE b.`id`=cb.`brand_id` AND c.`id`=cb.`category_id` AND c.name=#{categoryName}  " )
    public List<Map> findListByCategoryName(@Param( "categoryName" ) String categoryName);

}

（二）根据分类名称查询规格表

冗余

select * from tb_spec s, tb_category c where s.`template_id` = c.`template_id`

子查询

查spec

select * from tb_spec where template_id in (select c.template_id from tb_category c where c.name ='手机' )

select s.`name`,s.`options` from tb_spec s where template_id in (select c.template_id from tb_category c where c.name ='手机' )

controller

/**
 * 根据商品分类名称查询规格列表
 * @param categoryName
 * @return
 */
@GetMapping("/category/{categoryName}")
public Result findListByCategoryName(@PathVariable String categoryName){
    List<Map> list = specService.findListByCategoryName( categoryName );
    return new Result(true, StatusCode.OK,"查询成功",list);
}

servic

/**
 * 根据分类名称查询规格列表
 * @param categoryName
 * @return
 */
public List<Map> findListByCategoryName(String categoryName);

serviceimpl

@Override
public List<Map> findListByCategoryName(String categoryName) {
    List<Map> list = specMapper.findListByCategoryName( categoryName );
    for(Map map:list){
        String[] options = ((String) map.get( "options" )).split( "," );
        map.put( "options", options);
    }
    return  list;
}


中间表

    /**
     *  根据分类名称查询规格列表
     * @param categoryName
     * @return
     */
    @Select( "SELECT `name`,`options` FROM tb_spec WHERE template_id IN ( SELECT template_id FROM tb_category WHERE NAME=#{categoryName} )" )
    public List<Map> findListByCategoryName(@Param("categoryName") String categoryName);

}