SpringBoot與MySQL+MyBatis整合，實現商品庫存預警系統?

作者：Java知識日歷 2025-02-26 09:24:54

數據庫 MySQL

傳統的人工庫存管理系統容易出現錯誤，如誤報、漏報等，導致庫存數據不準確。通過自動化監控，您可以確保庫存數據的實時性和準確性，減少人為錯誤的可能性。

對于零售業，精確的庫存跟蹤和及時的補貨決策對于降低運營成本、提高客戶滿意度至關重要。

好處

1. 提高庫存準確性

2. 及時補貨

當庫存降至預設閾值時，系統會立即發送預警通知，提醒相關人員盡快進行補貨。這有助于避免因缺貨而導致的銷售損失，確保客戶需求得到及時滿足。

3. 降低成本

高效的庫存管理能夠有效降低倉儲成本、運輸成本和滯銷品處理成本。通過自動化監控和預警，您可以更好地控制庫存水平，避免過度庫存帶來的資金占用問題。

4. 增強客戶滿意度

確保商品始終處于充足供應狀態不僅能提高銷售額，還能增強客戶信任感和忠誠度。快速響應庫存變化的能力使您能夠在競爭激烈的市場環境中脫穎而出。

Mysql數據庫

首先，創建數據庫表和觸發器。這里我們創建了一個商品庫存表product_stock和一個預警記錄表stock_alerts。

同時，我們在product_stock表上定義了一個觸發器，當庫存數量減少到閾值以下時插入一條預警記錄。

CREATE DATABASEIFNOTEXISTS inventory_db;

USE inventory_db;

-- 商品庫存表
CREATETABLE product_stock (
    idINT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOTNULL,
    stock_quantity INTNOTNULL,
    threshold INTNOTNULLDEFAULT10-- 庫存預警閾值
);

-- 插入一些測試數據
INSERTINTO product_stock (product_name, stock_quantity, threshold)
VALUES ('Product A', 15, 10),
       ('Product B', 8, 10),
       ('Product C', 20, 10);

-- 創建預警記錄表
CREATETABLEIFNOTEXISTS stock_alerts (
    idINT AUTO_INCREMENT PRIMARY KEY,
    product_id INTNOTNULL,
    alert_time TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    FOREIGNKEY (product_id) REFERENCES product_stock(id)
);

-- 創建觸發器，在庫存數量減少到閾值以下時插入一條預警記錄
DELIMITER //

CREATETRIGGER after_update_product_stock
AFTERUPDATEON product_stock
FOREACHROW
BEGIN
    IF NEW.stock_quantity < NEW.threshold THEN
        INSERTINTO stock_alerts (product_id) VALUES (NEW.id);
    ENDIF;
END//

DELIMITER ;

代碼實操

<dependencies>
    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- MyBatis Framework -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Lombok for reducing boilerplate code -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <!-- Spring Boot Starter Mail -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-mail</artifactId>
    </dependency>

    <!-- Spring Boot Starter Task -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-task</artifactId>
    </dependency>
</dependencies>

application.properties

配置數據庫連接信息和其他屬性，以及郵件服務器配置。

# DataSource Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/inventory_db?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=password

# MyBatis Mapper Location
mybatis.mapper-locations=classpath*:mapper/*.xml

# Email Configuration
spring.mail.host=smtp.gmail.com
spring.mail.port=587
spring.mail.username=fg456hj@gmail.com
spring.mail.password=AAbb123456
spring.mail.properties.mail.smtp.auth=true
spring.mail.properties.mail.smtp.starttls.enable=true

“
*** 請注意替換fg456hj@gmail.com和AAbb123456為您自己的Gmail地址和密碼。如果您使用其他郵件服務提供商，請相應地調整配置。

Entity類

package com.example.inventory.model;

import lombok.Data;

@Data
public class ProductStock {
    private Integer id;           // 主鍵ID
    private String productName;   // 商品名稱
    private Integer stockQuantity;// 庫存量
    private Integer threshold;    // 預警閾值
}

Mapper接口及XML文件

ProductStockMapper.java

package com.example.inventory.mapper;

import com.example.inventory.model.ProductStock;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
publicinterface ProductStockMapper {

    @Select("SELECT * FROM product_stock WHERE id = #{id}")
    ProductStock getProductById(Integer id); // 根據ID獲取商品庫存信息

    @Update("UPDATE product_stock SET stock_quantity = stock_quantity - #{quantity} WHERE id = #{id}")
    void reduceStock(@Param("id") Integer id, @Param("quantity") Integer quantity); // 減少指定ID的商品庫存

    @Select("SELECT COUNT(*) FROM stock_alerts WHERE product_id = #{productId}")
    int getAlertCountForProduct(Integer productId); // 獲取指定商品ID的預警次數

    @Select("SELECT * FROM product_stock WHERE stock_quantity < threshold")
    List<ProductStock> getLowStockProducts(); // 獲取所有低于閾值的商品庫存信息
}

ProductStockMapper.xml

<?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.example.inventory.mapper.ProductStockMapper">
    <!-- Additional mappings if needed can be defined here -->
</mapper>

Service層

實現業務邏輯。

package com.example.inventory.service;

import com.example.inventory.mapper.ProductStockMapper;
import com.example.inventory.model.ProductStock;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.List;

@Service
publicclass InventoryService {

    @Autowired
    private ProductStockMapper productStockMapper;

    @Autowired
    private JavaMailSender mailSender;

    public ProductStock getProductById(int id) {
        return productStockMapper.getProductById(id); // 根據ID獲取商品庫存信息
    }

    @Transactional
    public boolean reduceStock(int productId, int quantity) {
        productStockMapper.reduceStock(productId, quantity); // 減少指定ID的商品庫存
        int alertCount = productStockMapper.getAlertCountForProduct(productId); // 獲取預警次數
        // 如果有預警，則發送郵件通知
        if (alertCount > 0) {
            sendAlertEmail(productStockMapper.getProductById(productId));
        }
        return alertCount > 0;
    }

    @Scheduled(fixedRate = 3600000) // 每小時執行一次
    public void checkAndSendAlerts() {
        List<ProductStock> lowStockProducts = productStockMapper.getLowStockProducts(); // 獲取所有低于閾值的商品庫存信息
        for (ProductStock product : lowStockProducts) {
            sendAlertEmail(product); // 發送郵件通知
        }
    }

    private void sendAlertEmail(ProductStock product) {
        SimpleMailMessage message = new SimpleMailMessage();
        message.setTo("recipient@example.com"); // 收件人郵箱地址
        message.setSubject("Inventory Alert: Low Stock for " + product.getProductName()); // 郵件主題
        message.setText("The stock quantity for " + product.getProductName() +
                " has dropped below the threshold. Current stock: " + product.getStockQuantity()); // 郵件正文
        mailSender.send(message); // 發送郵件
    }
}

Controller層

package com.example.inventory.controller;

import com.example.inventory.model.ProductStock;
import com.example.inventory.service.InventoryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/products")
publicclass ProductController {

    @Autowired
    private InventoryService inventoryService;

    @GetMapping("/{id}")
    public ProductStock getProductById(@PathVariable int id) {
        return inventoryService.getProductById(id); // 根據ID獲取商品庫存信息
    }

    @PostMapping("/{id}/reduce/{quantity}")
    public boolean reduceStock(@PathVariable int id, @PathVariable int quantity) {
        return inventoryService.reduceStock(id, quantity); // 減少指定ID的商品庫存并檢查是否需要發送預警
    }
}

啟動類

package com.example.inventory;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.JavaMailSenderImpl;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.util.Properties;

@SpringBootApplication
@MapperScan("com.example.inventory.mapper")
@EnableScheduling
publicclass InventoryApplication {

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

    @Bean
    public JavaMailSender getJavaMailSender() {
        JavaMailSenderImpl mailSender = new JavaMailSenderImpl();
        mailSender.setHost("smtp.gmail.com");
        mailSender.setPort(587);

        mailSender.setUsername("fg456hj@gmail.com"); // 替換為您的Gmail地址
        mailSender.setPassword("AAbb123456"); // 替換為您的Gmail密碼

        Properties props = mailSender.getJavaMailProperties();
        props.put("mail.transport.protocol", "smtp");
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.starttls.enable", "true");
        props.put("mail.debug", "true");

        return mailSender;
    }
}

責任編輯：武曉燕來源： Java知識日歷