Customized MAPPER

Custom general mappers are mostly used for intermediate table association queries

share pictures

(1) Query the brand list based on the category name

brand

controller

 1 /**

2 * Query the brand list according to the product category
3 * @param categoryName
4 * @return
5 */
6 @GetMapping("/category/{categoryName}")
7 public Result findListByCategoryName(@PathVariable String categoryName){
8 List brandList = brandService.findListByCategoryName( categoryName );
9 return new Result( true,StatusCode.OK,"Query successful", brandList);
10 }

service

< pre>public List findListByCategoryName( String categoryName);

serviceimpl

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

mapper

 1 < span style="color: #0000ff;">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 {
10
11 /**
12 * Query the brand list based on the category name
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 findListByCategoryName(@Param ("categoryName") String categoryName);
20
21 }

share picture

(2) Query the specification table based on the category name

Redundancy

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

Sub query

Check spec

select * from tb_spec where template_id in (select c.template_id from tb_category c where c.name =’mobile’ )

select s.`name`,s.`options` from tb_spec s where template_id in (select c.template_id from tb_category c where c.name =’mobile phone’ )

controller

/**
* Query the specification list according to the product category name
* @param categoryName
* @return
*/
@GetMapping("/category/{categoryName}")
public Result findListByCategoryName(@PathVariable String categoryName){
List list = specService.findListByCategoryName( categoryName );
return new Result(true, StatusCode.OK,"Query successful",list);
}

servic

/* *
* Query the specification list based on the category name
* @param categoryName
* @return
*/
public List findListByCategoryName(String categoryName);

serviceimpl

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


Intermediate table
 /** * Query the list of specifications based on the category name* @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 findListByCategoryName(@Param("categoryName") String categoryName);}

 1  /**

2 * Query the brand list according to the product category
3 * @param categoryName
4 * @return
5 */
6 @GetMapping("/category/{categoryName}")
7 public Result findListByCategoryName(@PathVariable String categoryName){
8 List brandList = brandService.findListByCategoryName( categoryName );
9 return new Result( true,StatusCode.OK,"Query successful", brandList);
10 }

 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 {
10
11 /**
12 * Query the brand list based on the category name
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 findListByCategoryName(@Param ("categoryName") String categoryName);
20
21 }

Leave a Comment

Your email address will not be published.