Customized MAPPER - Custom, mapper, universal

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 Cancel reply