自定义通用mapper

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

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

brand

controller

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

service

public List<Map> findListByCategoryName( String categoryName);

serviceimpl

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

mapper

 1 import com.changgou.pojo.Brand;
 2 import org.apache.ibatis.annotations.Param;
 3 import org.apache.ibatis.annotations.Select;
 4 import tk.mybatis.mapper.common.Mapper;
 5 
 6 import java.util.List;
 7 import java.util.Map;
 8 
 9 public interface BrandMapper extends Mapper<Brand> {
10 
11     /**
12      * 根据分类名称查询品牌列表
13      * @param categoryName
14      * @return
15      */
16     @Select( "SELECT b.`name`,b.`image` " +
17             "FROM tb_brand b ,tb_category c ,tb_category_brand cb " +
18             "WHERE b.`id`=cb.`brand_id` AND c.`id`=cb.`category_id` AND c.name=#{categoryName}  " )
19     public List<Map> findListByCategoryName(@Param( "categoryName" ) String categoryName);
20 
21 }

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

冗余

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);}