Review above

Last Section We have implemented the functions of jd-like round-robin advertising and commodity classification, and explained the different injection methods. In this section, we will continue to realize our e-commerce business, the display of commodity information.

requirement analysis

First, before we start coding this section, let's analyze where the goods will be displayed. Open the jd home page, and you can see the following when you drop down the mouse:

You can see that under the large type of query some of the goods are displayed on the first page (can be the latest, can also be recommended by the website, etc.), and then click on any of the categories, you can see the following:

When we enter e-commerce websites in general, one of the most common functions is search. Search for Piano The results are as follows:

You can click on any item to get to the details page. This is the information display of a single item.
To sum up, we can know that to achieve the commodity display of an e-commerce platform, the most basic includes:

• First page recommendation/latest goods on shelf
• Category Query for Commodities
• Keyword Search Commodity
• Commodity details display
• ...

Next, we can start developing commodity-related businesses.

First Page Merchandise List|IndexProductList

Development combing

Let's start by implementing the list of recommended items on the first page to see what information you need to show and how to show it.

• Product_id
• Show pictures (image_url)
• product_name
• Product_price
• Description
• Category name
• Classification Primary Key (category_id)
• Other...

Encoding implementation

Query by first level classification

Following the development order, from the bottom up, if the underlying mapper can't solve it, write the SQL mapper first, because we need to recursively implement data queries in the same table based on parent_id, of course we use table linking.Therefore, common mapper cannot meet our needs and needs to customize the mapper implementation.

Custom Mapper implementation

and Last Section As with the subcategorization of the first-level categorization query, add a custom implementation interface com.liferunner.custom.ProductCustomMapper to the project mscx-shop-mapper and synchronously create the XML file mapper/custom/ProductCustomMapper.xml in the resourcesmapper\custom path, since we configured in the previous section that the current folder can be scanned by containers, so IThe new mappers they add will be scanned and loaded at startup with the following code:

/**
 * ProductCustomMapper for : Custom Merchandise Mapper
 */
public interface ProductCustomMapper {

    /***
     * Query commodities by first-level classification
     *
     * @param paramMap Pass First Classification (map passes multiple parameters)
     * @return java.util.List<com.liferunner.dto.IndexProductDTO>
     */
    List<IndexProductDTO> getIndexProductDtoList(@Param("paramMap") Map<String, Integer> paramMap);
}

<?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.liferunner.custom.ProductCustomMapper">
    <resultMap id="IndexProductDTO" type="com.liferunner.dto.IndexProductDTO">
        <id column="rootCategoryId" property="rootCategoryId"/>
        <result column="rootCategoryName" property="rootCategoryName"/>
        <result column="slogan" property="slogan"/>
        <result column="categoryImage" property="categoryImage"/>
        <result column="bgColor" property="bgColor"/>
        <collection property="productItemList" ofType="com.liferunner.dto.IndexProductItemDTO">
            <id column="productId" property="productId"/>
            <result column="productName" property="productName"/>
            <result column="productMainImageUrl" property="productMainImageUrl"/>
            <result column="productCreateTime" property="productCreateTime"/>
        </collection>
    </resultMap>
    <select id="getIndexProductDtoList" resultMap="IndexProductDTO" parameterType="Map">
        SELECT
        c.id as rootCategoryId,
        c.name as rootCategoryName,
        c.slogan as slogan,
        c.category_image as categoryImage,
        c.bg_color as bgColor,
        p.id as productId,
        p.product_name as productName,
        pi.url as productMainImageUrl,
        p.created_time as productCreateTime
        FROM category c
        LEFT JOIN products p
        ON c.id = p.root_category_id
        LEFT JOIN products_img pi
        ON p.id = pi.product_id
        WHERE c.type = 1
        AND p.root_category_id = #{paramMap.rootCategoryId}
        AND pi.is_main = 1
        LIMIT 0,10;
    </select>
</mapper>

Service implementation

Create the com.liferunner.service.IProductService interface and its implementation class com.liferunner.service.impl.ProductServiceImpl in the service project by adding the following query methods:

public interface IProductService {

    /**
     * Get the list of goods recommended on the first page based on the first-level classification id
     *
     * @param rootCategoryId First-level classification id
     * @return Commodity list
     */
    List<IndexProductDTO> getIndexProductDtoList(Integer rootCategoryId);
    ...
}

---
    
@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ProductServiceImpl implements IProductService {

    // RequiredArgsConstructor constructor injection
    private final ProductCustomMapper productCustomMapper;

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public List<IndexProductDTO> getIndexProductDtoList(Integer rootCategoryId) {
        log.info("====== ProductServiceImpl#getIndexProductDtoList(rootCategoryId) : {}=======", rootCategoryId);
        Map<String, Integer> map = new HashMap<>();
        map.put("rootCategoryId", rootCategoryId);
        val indexProductDtoList = this.productCustomMapper.getIndexProductDtoList(map);
        if (CollectionUtils.isEmpty(indexProductDtoList)) {
            log.warn("ProductServiceImpl#getIndexProductDtoList did not query any commodity information ";
        }
        log.info("Query results:{}", indexProductDtoList);
        return indexProductDtoList;
    }
}

Controller implementation

Next, exposed query interfaces are implemented in com.liferunner.api.controller.IndexController:

@RestController
@RequestMapping("/index")
@Api(value = "Home page information controller", tags = "Home Page Information Interface API")
@Slf4j
public class IndexController {
    ...
    @Autowired
    private IProductService productService;

    @GetMapping("/rootCategorys")
    @ApiOperation(value = "Query Level 1 Classification", notes = "Query Level 1 Classification")
    public JsonResponse findAllRootCategorys() {
        log.info("============Query Level 1 Classification==============");
        val categoryResponseDTOS = this.categoryService.getAllRootCategorys();
        if (CollectionUtils.isEmpty(categoryResponseDTOS)) {
            log.info("============No categories were queried==============");
            return JsonResponse.ok(Collections.EMPTY_LIST);
        }
        log.info("============Level 1 Classified Query result: {}==============", categoryResponseDTOS);
        return JsonResponse.ok(categoryResponseDTOS);
    }
    ...
}

Test API

After writing, we need to verify our code by testing it or by using the RestService plug-in. Of course, you can also test it by Postman with the following results:

Commodity List|ProductList

As we saw in the list of goods in Jingdong at the beginning of the article, let's first analyze what element information is needed in the list page.

Development combing

The display of the list of goods is divided into two categories according to our previous analysis:

• Show all the items in the current category after choosing the category
• After entering the search keywords, show all the related items that you have searched

The list of goods data displayed in these two categories are basically consistent except for the data source. Can we use a unified interface to isolate them based on parameters?There is no problem in theory, data can be returned by passing parameters judgment completely, but when we achieve some predictable functional requirements, we must reserve a way for our own development, which is often referred to as extensibility. Based on this, we will separately implement their interfaces for later expansion.
Next, we will analyze the elements we need to show on the list page. First, because we need to distinguish between the above two situations, we need to deal with them separately when designing our API.
1. The list of classified goods is displayed, and the parameters that need to be passed in are:

• Classification id
• Sorting (several of our common sorting (sales, prices, etc.) in the business list)
• Paging is relevant (because we can't get everything out of the database)
  • PageNumber (Current Page)
  • PageSize (how many pieces of data are displayed per page)

2. Keyword queries commodity list, the parameters that need to be passed in are:

• Key word
• Sorting (several of our common sorting (sales, prices, etc.) in the business list)
• Paging is relevant (because we can't get everything out of the database)
  • PageNumber (Current Page)
  • PageSize (how many pieces of data are displayed per page)

The information you need to present on the page is:

• Commodity ID (used to skip the use of commodity details)
• Commodity Name
• commodity price
• Sales of Goods
• Merchandise Picture
• Commodity concessions
• ...

Encoding implementation

Based on our analysis above, let's start coding:

Query by Category of Commodities

Based on our analysis, we certainly won't get all the data in one table, so we need to do a multi-table join, so we need to implement our functional queries in a custom mapper.

ResponseDTO Implementation

Based on the information we need to present on the front end of our previous analysis, let's define an object, com.liferunner.dto.SearchProductDTO, to present this information, with the following code:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class SearchProductDTO {
    private String productId;
    private String productName;
    private Integer sellCounts;
    private String imgUrl;
    private Integer priceDiscount;
    //Goods offer, we calculate it directly and return the price after the offer
}

Custom Mapper implementation

A new method interface is added to com.liferunner.custom.ProductCustomMapper.java:

    List<SearchProductDTO> searchProductListByCategoryId(@Param("paramMap") Map<String, Object> paramMap);

At the same time, implement our query method in mapper/custom/ProductCustomMapper.xml:

<select id="searchProductListByCategoryId" resultType="com.liferunner.dto.SearchProductDTO" parameterType="Map">
        SELECT
        p.id as productId,
        p.product_name as productName,
        p.sell_counts as sellCounts,
        pi.url as imgUrl,
        tp.priceDiscount
        FROM products p
        LEFT JOIN products_img pi
        ON p.id = pi.product_id
        LEFT JOIN
        (
        SELECT product_id, MIN(price_discount) as priceDiscount
        FROM products_spec
        GROUP BY product_id
        ) tp
        ON tp.product_id = p.id
        WHERE pi.is_main = 1
        AND p.category_id = #{paramMap.categoryId}
        ORDER BY
        <choose>
            <when test="paramMap.sortby != null and paramMap.sortby == 'sell'">
                p.sell_counts DESC
            </when>
            <when test="paramMap.sortby != null and paramMap.sortby == 'price'">
                tp.priceDiscount ASC
            </when>
            <otherwise>
                p.created_time DESC
            </otherwise>
        </choose>
    </select>

The main explanation is the <select>module here and why the if tag is not used.
Sometimes, we don't want all the conditions to work at the same time, we just want to choose one of several options, but when using the IF tag, the conditions in the IF tag will be executed as long as the expression in test is true.MyBatis provides the choose element.IF tags are related to (and) and chooses are or (or).
It chooses to go from top to bottom, and quit once any of the conditions are met.

Service implementation

Then add the method interface in servicecom.liferunner.service.IProductService:

    /**
     * Query the list of goods by category
     *
     * @param categoryId Classification id
     * @param sortby     sort order
     * @param pageNumber CurrentPage
     * @param pageSize   How many pieces of data are displayed per page
     * @return Universal Paging Results View
     */
    CommonPagedResult searchProductList(Integer categoryId, String sortby, Integer pageNumber, Integer pageSize);

In the implementation class com.liferunner.service.impl.ProductServiceImpl, implement the above method:

    // Overload
    @Override
    public CommonPagedResult searchProductList(Integer categoryId, String sortby, Integer pageNumber, Integer pageSize) {
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("categoryId", categoryId);
        paramMap.put("sortby", sortby);
        // mybatis-pagehelper
        PageHelper.startPage(pageNumber, pageSize);
        val searchProductDTOS = this.productCustomMapper.searchProductListByCategoryId(paramMap);
        // Get information from the mybatis plug-in
        PageInfo<?> pageInfo = new PageInfo<>(searchProductDTOS);
        // Encapsulated as a view that is recognized by the back-end paging component
        val commonPagedResult = CommonPagedResult.builder()
                .pageNumber(pageNumber)
                .rows(searchProductDTOS)
                .totalPage(pageInfo.getPages())
                .records(pageInfo.getTotal())
                .build();
        return commonPagedResult;
    }

Here, we use a mybatis-pagehelper plug-in, which is broken down in the welfare notes below.

Controller implementation

Continue adding exposed interface APIs to com.liferunner.api.controller.ProductController:

@GetMapping("/searchByCategoryId")
    @ApiOperation(value = "Query commodity information list", notes = "Query the list of goods by category")
    public JsonResponse searchProductListByCategoryId(
        @ApiParam(name = "categoryId", value = "Commodity Classification id", required = true, example = "0")
        @RequestParam Integer categoryId,
        @ApiParam(name = "sortby", value = "sort order", required = false)
        @RequestParam String sortby,
        @ApiParam(name = "pageNumber", value = "CurrentPage", required = false, example = "1")
        @RequestParam Integer pageNumber,
        @ApiParam(name = "pageSize", value = "Number of records displayed per page", required = false, example = "10")
        @RequestParam Integer pageSize
    ) {
        if (null == categoryId || categoryId == 0) {
            return JsonResponse.errorMsg("classification id Error!");
        }
        if (null == pageNumber || 0 == pageNumber) {
            pageNumber = DEFAULT_PAGE_NUMBER;
        }
        if (null == pageSize || 0 == pageSize) {
            pageSize = DEFAULT_PAGE_SIZE;
        }
        log.info("============By Classification:{} Search List==============", categoryId);

        val searchResult = this.productService.searchProductList(categoryId, sortby, pageNumber, pageSize);
        return JsonResponse.ok(searchResult);
    }

Because our request only requires the commodity classification id to be required, and the rest of the callers can not provide it, but if not, our system needs to give some default parameters to keep our system running smoothly, so I have defined com.liferunner.api.controller.BaseController, which stores some public configuration information.

/**
 * BaseController for : controller base class
 */
@Controller
public class BaseController {
    /**
     * Default Show Page 1
     */
    public final Integer DEFAULT_PAGE_NUMBER = 1;
    /**
     * Show 10 data per page by default
     */
    public final Integer DEFAULT_PAGE_SIZE = 10;
}

Test API

The parameters tested are categoryId: 51, sortby: price, pageNumber: 1, pageSize: 5

As you can see, we queried seven pieces of data with a total page count of 2 and sorted them from smallest to largest, proving that our code is correct.Next, with the same code logic, we continue to implement queries based on search keywords.

Query by keyword

Response DTO Implementation

Use the com.liferunner.dto.SearchProductDTO implemented above.

Custom Mapper implementation

New method in com.liferunner.custom.ProductCustomMapper:

List<SearchProductDTO> searchProductList(@Param("paramMap") Map<String, Object> paramMap);

Add query SQL in mapper/custom/ProductCustomMapper.xml:

<select id="searchProductList" resultType="com.liferunner.dto.SearchProductDTO" parameterType="Map">
        SELECT
        p.id as productId,
        p.product_name as productName,
        p.sell_counts as sellCounts,
        pi.url as imgUrl,
        tp.priceDiscount
        FROM products p
        LEFT JOIN products_img pi
        ON p.id = pi.product_id
        LEFT JOIN
        (
        SELECT product_id, MIN(price_discount) as priceDiscount
        FROM products_spec
        GROUP BY product_id
        ) tp
        ON tp.product_id = p.id
        WHERE pi.is_main = 1
        <if test="paramMap.keyword != null and paramMap.keyword != ''">
            AND p.item_name LIKE "%${paramMap.keyword}%"
        </if>
        ORDER BY
        <choose>
            <when test="paramMap.sortby != null and paramMap.sortby == 'sell'">
                p.sell_counts DESC
            </when>
            <when test="paramMap.sortby != null and paramMap.sortby == 'price'">
                tp.priceDiscount ASC
            </when>
            <otherwise>
                p.created_time DESC
            </otherwise>
        </choose>
    </select>

Service implementation

New query interface in com.liferunner.service.IProductService:

    /**
     * Query commodity list
     *
     * @param keyword    Query keywords
     * @param sortby     sort order
     * @param pageNumber CurrentPage
     * @param pageSize   How many pieces of data are displayed per page
     * @return Universal Paging Results View
     */
    CommonPagedResult searchProductList(String keyword, String sortby, Integer pageNumber, Integer pageSize);

Implement the above interface methods at com.liferunner.service.impl.ProductServiceImpl:

    @Override
    public CommonPagedResult searchProductList(String keyword, String sortby, Integer pageNumber, Integer pageSize) {
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("keyword", keyword);
        paramMap.put("sortby", sortby);
        // mybatis-pagehelper
        PageHelper.startPage(pageNumber, pageSize);
        val searchProductDTOS = this.productCustomMapper.searchProductList(paramMap);
        // Get information from the mybatis plug-in
        PageInfo<?> pageInfo = new PageInfo<>(searchProductDTOS);
        // Encapsulated as a view that is recognized by the back-end paging component
        val commonPagedResult = CommonPagedResult.builder()
                .pageNumber(pageNumber)
                .rows(searchProductDTOS)
                .totalPage(pageInfo.getPages())
                .records(pageInfo.getTotal())
                .build();
        return commonPagedResult;
    }

The only difference between the above method and the previous search ProductList (Integer categoryId, String sortby, Integer pageNumber, Integer pageSize) is that it affirms search keywords for data queries and uses overloading to be considered for subsequent different types of business extensions.

Controller implementation

Add the keyword search API to com.liferunner.api.controller.ProductController:

    @GetMapping("/search")
    @ApiOperation(value = "Query commodity information list", notes = "Query commodity information list")
    public JsonResponse searchProductList(
        @ApiParam(name = "keyword", value = "Search keywords", required = true)
        @RequestParam String keyword,
        @ApiParam(name = "sortby", value = "sort order", required = false)
        @RequestParam String sortby,
        @ApiParam(name = "pageNumber", value = "CurrentPage", required = false, example = "1")
        @RequestParam Integer pageNumber,
        @ApiParam(name = "pageSize", value = "Number of records displayed per page", required = false, example = "10")
        @RequestParam Integer pageSize
    ) {
        if (StringUtils.isBlank(keyword)) {
            return JsonResponse.errorMsg("Search keywords cannot be empty!");
        }
        if (null == pageNumber || 0 == pageNumber) {
            pageNumber = DEFAULT_PAGE_NUMBER;
        }
        if (null == pageSize || 0 == pageSize) {
            pageSize = DEFAULT_PAGE_SIZE;
        }
        log.info("============By keyword:{} Search List==============", keyword);

        val searchResult = this.productService.searchProductList(keyword, sortby, pageNumber, pageSize);
        return JsonResponse.ok(searchResult);
    }

Test API

Test parameters: keyword: Xifeng, sortby: sell, pageNumber: 1, pageSize: 10

Sort according to sales is normal, query keywords are normal, total number of 32, 10 per page, total 3 pages are normal.

Welfare Explanation

In this section of the coding implementation, we used a generic mybatis paging plug-in, mybatis-pagehelper. Next, let's take a look at the basics of this plug-in.

mybatis-pagehelper

If you have used: MyBatis Paging Plugin PageHelper So it's easy to understand that, actually, it's based on Executor Interceptor To achieve this, after the original SQL is intercepted, a modification of the SQL is made.
Let's look at the implementation in our own code, according to the springboot coded trilogy:

1. Add Dependency

        <!-- Introduce mybatis-pagehelper Plug-in unit-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

One of my classmates asked me why this dependency was introduced differently from what I used to be.Previously used:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.10</version>
</dependency>

Here's the answer: Dependent ports

We're using springboot for project development. Now that we're using springboot, we can use its auto-assembly feature. The author helped us achieve this Auto-assembled jar ok, we just need to refer to the examples to write it.

2. Configuration Change

# mybatis paging component configuration
pagehelper:
  helperDialect: mysql #Plugin supports 12 databases, select type
  supportMethodsArguments: true

3. Change Code

The following sample code:

    @Override
    public CommonPagedResult searchProductList(String keyword, String sortby, Integer pageNumber, Integer pageSize) {
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("keyword", keyword);
        paramMap.put("sortby", sortby);
        // mybatis-pagehelper
        PageHelper.startPage(pageNumber, pageSize);
        val searchProductDTOS = this.productCustomMapper.searchProductList(paramMap);
        // Get information from the mybatis plug-in
        PageInfo<?> pageInfo = new PageInfo<>(searchProductDTOS);
        // Encapsulated as a view that is recognized by the back-end paging component
        val commonPagedResult = CommonPagedResult.builder()
                .pageNumber(pageNumber)
                .rows(searchProductDTOS)
                .totalPage(pageInfo.getPages())
                .records(pageInfo.getTotal())
                .build();
        return commonPagedResult;
    }

Before we query the database, we introduce a sentence called PageHelper.startPage(pageNumber, pageSize); tell mybatis that we want to paginate the query, at which point the plug-in will launch an interceptor, com.github.pagehelper.PageInterceptor, to intercept all queries, add custom parameters, and add the total number of query data.(We'll print sql later to prove it.)

When the results are queried, we need to inform the plug-in that is PageInfo<?> pageInfo = new PageInfo<> (search ProductDTOS); (com.github.pagehelper.PageInfo is a property wrapper that the plug-in does for paging that can be viewed specifically Property Portal).

At this point, our plug-in usage is over.But why do we encapsulate an object later to return to the outside world instead of using the queried PageInfo?This is because in our actual development process, a structure encapsulation is done for the consistency of the data structure, and you can't implement this step either, which has no effect on the results.

SQL Print Comparison

2019-11-21 12:04:21 INFO  ProductController:134 - ============By keyword:West Phoenix Search List==============
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4ff449ba] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1980420239 wrapping com.mysql.cj.jdbc.ConnectionImpl@563b22b1] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM products p LEFT JOIN products_img pi ON p.id = pi.product_id LEFT JOIN (SELECT product_id, MIN(price_discount) AS priceDiscount FROM products_spec GROUP BY product_id) tp ON tp.product_id = p.id WHERE pi.is_main = 1 AND p.product_name LIKE "%Xifeng%" 
==> Parameters: 
<==    Columns: count(0)
<==        Row: 32
<==      Total: 1
==>  Preparing: SELECT p.id as productId, p.product_name as productName, p.sell_counts as sellCounts, pi.url as imgUrl, tp.priceDiscount FROM product p LEFT JOIN products_img pi ON p.id = pi.product_id LEFT JOIN ( SELECT product_id, MIN(price_discount) as priceDiscount FROM products_spec GROUP BY product_id ) tp ON tp.product_id = p.id WHERE pi.is_main = 1 AND p.product_name LIKE "%Xifeng%" ORDER BY p.sell_counts DESC LIMIT ? 
==> Parameters: 10(Integer)

We can see that there is one more SELECT count(0) in our SQL and one more LIMIT parameter in the second SQL. In the code, we know very clearly that we did not display the total number of searches and queries, so we can be sure that it is the plug-in that helps us to implement it.

Source Download

Github Portal
Gitee Portal

Next Section Forecast

In the next section, we will continue to develop the product details display and commodity evaluation business. Any development components used in the process will be introduced in a special section, brothers panic!

gogogo!