12.13

继续完善仓库管理系统package com.warehouse.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.warehouse.entity.Inventory;
import com.warehouse.utils.DBUtil;

/**

• 库存DAO层

• 实现库存的数据库操作
  */
  public class InventoryDAO {
  private static final Logger logger = LoggerFactory.getLogger(InventoryDAO.class);
  private QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());

  /**

  • 添加库存记录
  • @param inventory 库存对象
  • @return 添加成功返回1，失败返回0
    */
    public int addInventory(Inventory inventory) {
    String sql = "INSERT INTO inventory (warehouse_id, material_id, quantity, unit_price, amount) VALUES (?, ?, ?, ?, ?)";
    try {
    return queryRunner.update(sql, inventory.getWarehouseId(), inventory.getMaterialId(),
    inventory.getQuantity(), inventory.getUnitPrice(), inventory.getAmount());
    } catch (SQLException e) {
    logger.error("添加库存记录失败: {}", e.getMessage());
    return 0;
    }
    }

  /**

  • 更新库存数量
  • @param warehouseId 仓库ID
  • @param materialId 物资ID
  • @param quantityChange 数量变化量（正数为增加，负数为减少）
  • @return 更新成功返回1，失败返回0
    */
    public int updateInventoryQuantity(Integer warehouseId, Integer materialId, int quantityChange) {
    String sql = "UPDATE inventory SET quantity = quantity + ?, amount = amount + (SELECT unit_price FROM inventory WHERE warehouse_id = ? AND material_id = ?) * ? WHERE warehouse_id = ? AND material_id = ?";
    try {
    // 先获取当前单价
    Inventory inventory = getInventoryByWarehouseAndMaterial(warehouseId, materialId);
    if (inventory == null) {
    logger.error("库存记录不存在: warehouseId={}, materialId={}", warehouseId, materialId);
    return 0;
    }
    // 更新数量和金额
    sql = "UPDATE inventory SET quantity = quantity + ?, amount = amount + ? * ? WHERE warehouse_id = ? AND material_id = ?";
    return queryRunner.update(sql, quantityChange, inventory.getUnitPrice(), quantityChange, warehouseId, materialId);
    } catch (SQLException e) {
    logger.error("更新库存数量失败: {}", e.getMessage());
    return 0;
    }
    }

  /**

  • 更新库存单价和数量
  • @param warehouseId 仓库ID
  • @param materialId 物资ID
  • @param quantity 新的数量
  • @param unitPrice 新的单价
  • @return 更新成功返回1，失败返回0
    */
    public int updateInventory(Integer warehouseId, Integer materialId, int quantity, double unitPrice) {
    String sql = "UPDATE inventory SET quantity = ?, unit_price = ?, amount = ? WHERE warehouse_id = ? AND material_id = ?";
    try {
    double amount = quantity * unitPrice;
    return queryRunner.update(sql, quantity, unitPrice, amount, warehouseId, materialId);
    } catch (SQLException e) {
    logger.error("更新库存失败: {}", e.getMessage());
    return 0;
    }
    }

  /**

  • 根据仓库ID和物资ID删除库存记录
  • @param warehouseId 仓库ID
  • @param materialId 物资ID
  • @return 删除成功返回1，失败返回0
    */
    public int deleteInventory(Integer warehouseId, Integer materialId) {
    String sql = "DELETE FROM inventory WHERE warehouse_id = ? AND material_id = ?";
    try {
    return queryRunner.update(sql, warehouseId, materialId);
    } catch (SQLException e) {
    logger.error("删除库存记录失败: {}", e.getMessage());
    return 0;
    }
    }

  /**

  • 根据仓库ID和物资ID查询库存
  • @param warehouseId 仓库ID
  • @param materialId 物资ID
  • @return 库存对象
    */
    public Inventory getInventoryByWarehouseAndMaterial(Integer warehouseId, Integer materialId) {
    String sql = "SELECT * FROM inventory WHERE warehouse_id = ? AND material_id = ?";
    try {
    return queryRunner.query(sql, new BeanHandler<>(Inventory.class), warehouseId, materialId);
    } catch (SQLException e) {
    logger.error("根据仓库ID和物资ID查询库存失败: {}", e.getMessage());
    return null;
    }
    }

  /**

  • 根据仓库ID查询所有库存
  • @param warehouseId 仓库ID
  • @return 库存列表
    */
    public List getInventoryByWarehouseId(Integer warehouseId) {
    String sql = "SELECT * FROM inventory WHERE warehouse_id = ?";
    try {
    return queryRunner.query(sql, new BeanListHandler<>(Inventory.class), warehouseId);
    } catch (SQLException e) {
    logger.error("根据仓库ID查询库存失败: {}", e.getMessage());
    return new ArrayList<>(); // 返回空列表而不是null
    }
    }

  /**

  • 根据物资ID查询所有库存
  • @param materialId 物资ID
  • @return 库存列表
    */
    public List getInventoryByMaterialId(Integer materialId) {
    String sql = "SELECT * FROM inventory WHERE material_id = ?";
    try {
    return queryRunner.query(sql, new BeanListHandler<>(Inventory.class), materialId);
    } catch (SQLException e) {
    logger.error("根据物资ID查询库存失败: {}", e.getMessage());
    return new ArrayList<>(); // 返回空列表而不是null
    }
    }

  /**

  • 查询所有库存
  • @return 库存列表
    */
    public List getAllInventory() {
    String sql = "SELECT * FROM inventory";
    try {
    return queryRunner.query(sql, new BeanListHandler<>(Inventory.class));
    } catch (SQLException e) {
    logger.error("查询所有库存失败: {}", e.getMessage());
    return new ArrayList<>(); // 返回空列表而不是null
    }
    }

  /**

  • 查询低于安全库存的物资
  • @return 库存列表
    */
    public List getLowInventory() {
    String sql = "SELECT * FROM inventory WHERE quantity < 10";
    try {
    return queryRunner.query(sql, new BeanListHandler<>(Inventory.class));
    } catch (SQLException e) {
    logger.error("查询低库存失败: {}", e.getMessage());
    return new ArrayList<>(); // 返回空列表而不是null
    }
    }
    }