SpringBoot与MySQL+MyBatis整合，实现商品库存预警系统
对于零售业，精确的库存跟踪和及时的补货决策对于降低运营成本、提高客户满意度至关重要。
好处
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 ;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.
代码实操
复制
<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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.
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=true1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.
“
*** 请注意替换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; // 预警阈值
}1.2.3.4.5.6.7.8.9.10.11.
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(); // 获取所有低于阈值的商品库存信息
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.
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>1.2.3.4.5.6.7.8.
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); // 发送邮件
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.
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的商品库存并检查是否需要发送预警
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.
启动类
复制
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;
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.
阅读剩余
THE END
