Product Management Demo

This example uses Spring Boot, MyBatis-Plus, MySQL, and Druid to implement paginated association query between commodities and categories.

1. Prepare the database

Create the java2601 database and import the table structure and test data from goods.sql. The example requires at least the following two tables:

goods: Commodity List.
category: Commodity Category List.

cid in the commodity table is used to associate cid in the category table.

2. Create the Spring Boot project

Introduce dependence

Add the following dependencies to pom.xml. The version number can be managed uniformly based on the Spring Boot version of the course project.

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
        <version>3.5.6</version>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-3-starter</artifactId>
        <version>1.2.23</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Configure data sources and `MyBatis-Plus`

Configure database connection and MyBatis-Plus in application.yml:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      url: jdbc:mysql://localhost:3306/java2601?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
      initial-size: 2
      min-idle: 3
      max-active: 10
      max-wait: 10000

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml
  type-aliases-package: com.hyxy.goods.po
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto
      # table-prefix: tb_
      # logic-delete-field: deleteFlag
      # logic-delete-value: 1
      # logic-not-delete-value: 0

The development environment can directly write user names and passwords, and the actual project should manage database credentials through environment variables or external configuration.

create a directory structure

It is recommended to subcontract according to controller, business layer, data access layer, and entity classes.

Use reverse engineering to generate basic code

You can use the MyBatis-Plus code generator to generate entity classes, Mapper, Services, and corresponding XML files.

After generation, you need to check the package name, table name, primary key policy, and storage location of the generated files. You cannot directly assume that all configurations are consistent with the current project.

3. Scan the Mapper interface

Use @MapperScan on the startup class to uniformly scan the Mapper interface:

package com.hyxy.goods;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.hyxy.goods.mapper")
public class GoodsApplication {

    public static void main(String[] args) {
        SpringApplication.run(GoodsApplication.class, args);
    }
}

After using @MapperScan, there is usually no need to add @Mapper to each Mapper interface. Just choose one of the two methods.

package com.hyxy.goods.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hyxy.goods.po.Category;

public interface CategoryMapper extends BaseMapper<Category> {
}

package com.hyxy.goods.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hyxy.goods.po.Goods;

public interface GoodsMapper extends BaseMapper<Goods> {
}

4. Configure paging plug-ins

Create a configuration class in the config package and register the MyBatis-Plus paging interceptor:

package com.hyxy.goods.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

This courseware uses MyBatis-Plus 3.5.6. If you upgrade to 3.5.9 or higher, the parser related to the paging plug-in has been split into optional dependencies, and modules need to be supplemented according to the official instructions of the corresponding version.

When querying products, the category name is also displayed.

Add non-table fields to the commodity entity class

cname comes from an associated query and is not a real field in the goods table, so @TableField(exist = false) needs to be used:

package com.hyxy.goods.po;

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

@Data
public class Goods {

    // 省略 goods 表中的其他字段

    @TableField(exist = false)
    private String cname;
}

Declare query methods in the Mapper interface

The paging parameters should be placed before the method parameter list, and the query conditions should be explicitly named using @Param:

package com.hyxy.goods.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hyxy.goods.po.Goods;
import org.apache.ibatis.annotations.Param;

public interface GoodsMapper extends BaseMapper<Goods> {

    IPage<Goods> selectJoinCategory(
            Page<Goods> page,
            @Param("gname") String gname,
            @Param("cid") Integer cid
    );
}

Writing Mapper XML

Create GoodsMapper.xml under the resources/mapper directory. namespace must be consistent with the fully qualified class name of the Mapper interface.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hyxy.goods.mapper.GoodsMapper">

    <select id="selectJoinCategory" resultType="Goods">
        SELECT
            g.gid,
            g.gname,
            g.price,
            g.stock,
            g.cid,
            g.sales,
            g.onsale,
            g.createtime,
            c.cname AS cname
        FROM goods AS g
        LEFT JOIN category AS c ON g.cid = c.cid
        <where>
            <if test="gname != null and gname != ''">
                AND g.gname LIKE CONCAT('%', #{gname}, '%')
            </if>
            <if test="cid != null">
                AND g.cid = #{cid}
            </if>
        </where>
        ORDER BY g.gid DESC
    </select>

</mapper>

Table aliases should be added to columns in associated queries as much as possible to avoid ambiguity when two tables have fields with the same name.

6. Writing tests

Use the Spring Boot test class to verify paging and association query results:

package com.hyxy.goods;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hyxy.goods.mapper.GoodsMapper;
import com.hyxy.goods.po.Goods;
import jakarta.annotation.Resource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class GoodsApplicationTests {

    @Resource
    private GoodsMapper goodsMapper;

    @Test
    void selectJoinCategory() {
        Page<Goods> page = new Page<>(1, 3);

        IPage<Goods> pageResult = goodsMapper.selectJoinCategory(
                page,
                null,
                null
        );

        pageResult.getRecords().forEach(System.out::println);
        System.out.println("总记录数：" + pageResult.getTotal());
        System.out.println("总页数：" + pageResult.getPages());
    }
}

During testing, you should confirm that the console has generated SQL with paging restrictions, and check whether the cname in each product object is correctly mapped.

7. Common questions

Mapper method cannot find corresponding SQL

Check the following:

Whether the namespace of XML is consistent with the fully qualified name of the Mapper interface.
Whether id of <select> is consistent with the interface method name.
Whether mapper-locations overwrites the directory where the XML file is located.

Pagination does not take effect

Make sure that MybatisPlusInterceptor and PaginationInnerInterceptor are registered and ensure that the paging parameters are in front of the Mapper method parameter list.

Error reported in category number condition or abnormal query result

When using g.cid in multi-table queries, do not directly write cid without table aliases, otherwise field ambiguity may occur.

Product Management Demo

Product Management Demo

1. Prepare the database

2. Create the Spring Boot project

Introduce dependence

Configure data sources and MyBatis-Plus

create a directory structure

Use reverse engineering to generate basic code

3. Scan the Mapper interface

4. Configure paging plug-ins

5. Realize related query between goods and categories

Add non-table fields to the commodity entity class

Declare query methods in the Mapper interface

Writing Mapper XML

6. Writing tests

7. Common questions

Mapper method cannot find corresponding SQL

Pagination does not take effect

Error reported in category number condition or abnormal query result

Configure data sources and `MyBatis-Plus`