使用Java实现的简单药品库存管理系统

猛男妙妙屋

已于 2025-01-03 21:41:42 修改

阅读量1.3k

点赞数 21

文章标签： java 数据库开发语言学习 mssql sqlserver

于 2024-10-13 21:45:30 首次发布

本文链接：https://blog.csdn.net/qq_72421395/article/details/142184371

版权

药品库存管理系统

- 功能设计与模块实现
一、数据库设计
二、Java代码实现
三、界面展示

这是我们数据库的课程设计，这是其中一名组员==>xiao_haoya
代码文件

功能设计与模块实现

用户登录功能：通过LoginFrame实现用户登录界面，用户输入用户名和密码进行身份验证。系统模块LoginFrame.authenticate()负责验证用户信息，验证成功后初始化主界面MainFrame并关闭登录界面。
药品管理：通过MedicinePanel实现药品信息的管理。系统模块提供addMedicine()、updateMedicine()、deleteMedicine()和loadMedicines()等方法，支持药品信息的增加、修改、删除和加载显示。
库存管理：通过InventoryPanel实现库存信息的管理。系统模块包括addInventory()、updateInventory()、deleteInventory()和loadInventories()方法，用于处理库存的增加、更新、删除和显示。
订单管理：通过OrderPanel实现订单信息的管理。系统模块提供addOrder()、updateOrder()、deleteOrder()和loadOrders()方法，用于订单的创建、修改、删除和查询。
销售信息管理：通过SalePanel实现销售记录的管理。系统模块包括addSale()、updateSale()、deleteSale()和loadSales()方法，用于销售数据的录入、更新、删除和展示。
供应商管理：通过SupplierPanel实现供应商信息的管理。系统模块包括addSupplier()、updateSupplier()、deleteSupplier()和loadSuppliers()方法，用于供应商信息的维护。
用户管理：通过UserPanel实现用户信息和权限的管理。系统模块包括addUser()、updateUser()、deleteUser()和loadUsers()方法，用于用户账户的创建、信息更新、权限分配和用户列表的显示。

一、数据库设计

-- 创建数据库
CREATE DATABASE PharmacyDB;
GO

USE PharmacyDB;
GO

CREATE TABLE Supplier (
    SupplierID INT PRIMARY KEY IDENTITY(1,1),
    SupplierName NVARCHAR(100) NOT NULL,
    ContactInfo NVARCHAR(255)
);

CREATE TABLE Medicine (
    MedicineID INT PRIMARY KEY IDENTITY(1,1),
    MedicineName NVARCHAR(100) NOT NULL,
    SupplierID INT,
    Status NVARCHAR(50) CHECK (Status IN ('在库', '已订购', '已售罄')),
    StockQuantity INT
);

CREATE TABLE Inventory (
    InventoryID INT PRIMARY KEY IDENTITY(1,1),
    MedicineID INT,
    Quantity INT,
    TransactionType NVARCHAR(50) CHECK (TransactionType IN ('入库', '出库')),
    TransactionDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE [User] (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL UNIQUE,
    Password NVARCHAR(100) NOT NULL,
    Role NVARCHAR(50) CHECK (Role IN ('admin', 'user'))
);

CREATE TABLE MedicineStockRecord (
    RecordID INT PRIMARY KEY IDENTITY(1,1),
    MedicineID INT,
    StockChange INT,
    StatusChange NVARCHAR(50),
    ChangeDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE [Order] (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    MedicineID INT,
    Quantity INT,
    OrderDate DATETIME DEFAULT GETDATE(),
    Status NVARCHAR(50) CHECK (Status IN ('已下单', '已发货', '已完成'))
);

CREATE TABLE Sale (
    SaleID INT PRIMARY KEY IDENTITY(1,1),
    MedicineID INT,
    Quantity INT,
    SaleDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE SupplierOrder (
    SupplierOrderID INT PRIMARY KEY IDENTITY(1,1),
    SupplierID INT,
    MedicineID INT,
    Quantity INT,
    OrderDate DATETIME DEFAULT GETDATE(),
    Status NVARCHAR(50) CHECK (Status IN ('已下单', '已发货', '已完成'))
);

-- 添加外键约束
ALTER TABLE Medicine
ADD CONSTRAINT FK_Medicine_Supplier FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID);

ALTER TABLE Inventory
ADD CONSTRAINT FK_Inventory_Medicine FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE CASCADE;

ALTER TABLE MedicineStockRecord
ADD CONSTRAINT FK_MedicineStockRecord_Medicine FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE CASCADE;

ALTER TABLE [Order]
ADD CONSTRAINT FK_Order_Medicine FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE CASCADE;

ALTER TABLE Sale
ADD CONSTRAINT FK_Sale_Medicine FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE CASCADE;

ALTER TABLE SupplierOrder
ADD CONSTRAINT FK_SupplierOrder_Supplier FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID) ON DELETE CASCADE;

ALTER TABLE SupplierOrder
ADD CONSTRAINT FK_SupplierOrder_Medicine FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID) ON DELETE CASCADE;

-- 创建视图
CREATE VIEW MedicineStockView AS
SELECT 
    Medicine.MedicineID,
    Medicine.MedicineName,
    SUM(CASE WHEN Inventory.TransactionType = '入库' THEN Inventory.Quantity ELSE -Inventory.Quantity END) AS CurrentStock
FROM 
    Inventory
JOIN 
    Medicine ON Inventory.MedicineID = Medicine.MedicineID
GROUP BY 
    Medicine.MedicineID, Medicine.MedicineName;

-- 创建存储过程
CREATE PROCEDURE GetMedicineStockRecords
@MedicineID INT
AS
BEGIN
    SELECT * FROM MedicineStockRecord WHERE MedicineID = @MedicineID;
END;

CREATE PROCEDURE UpdateMedicineStatus
@MedicineID INT,
@NewStatus NVARCHAR(50)
AS
BEGIN
    UPDATE Medicine SET Status = @NewStatus WHERE MedicineID = @MedicineID;
END;


-- 创建触发器，当增加库存记录时更新药品的库存量
CREATE TRIGGER UpdateStockOnInventoryInsert
ON Inventory
AFTER INSERT
AS
BEGIN
    UPDATE Medicine
    SET StockQuantity = CASE 
                            WHEN i.TransactionType = '入库' THEN StockQuantity + i.Quantity
                            WHEN i.TransactionType = '出库' THEN StockQuantity - i.Quantity
                            ELSE StockQuantity
                        END
    FROM Medicine m
    JOIN inserted i ON m.MedicineID = i.MedicineID
END;

-- 创建触发器，当删除库存记录时更新药品的库存量（出库时数量减少）
CREATE TRIGGER UpdateStockOnInventoryDelete
ON Inventory
AFTER DELETE
AS
BEGIN
    UPDATE Medicine
    SET StockQuantity = CASE 
                            WHEN d.TransactionType = '入库' THEN StockQuantity - d.Quantity
                            WHEN d.TransactionType = '出库' THEN StockQuantity + d.Quantity
                            ELSE StockQuantity
                        END
    FROM Medicine m
    JOIN deleted d ON m.MedicineID = d.MedicineID
END;

-- 创建触发器，当修改药品状态时更新药品的库存量
CREATE TRIGGER UpdateStockOnMedicineStatusUpdate
ON Medicine
AFTER UPDATE
AS
BEGIN
    UPDATE Medicine
    SET StockQuantity = CASE WHEN i.Status = '已售罄' THEN 0 ELSE m.StockQuantity END
    FROM Medicine m
    JOIN inserted i ON m.MedicineID = i.MedicineID
END;


-- 当添加一条供应商订单信息时，自动添加一条订单信息，并且订单信息状态与供应商订单信息状态相同
CREATE TRIGGER AddOrderAndInventoryOnSupplierOrderInsert
ON SupplierOrder
AFTER INSERT
AS
BEGIN
    -- 添加一条订单信息
    INSERT INTO [Order] (MedicineID, Quantity, OrderDate, Status)
    SELECT MedicineID, Quantity, GETDATE(), Status
    FROM inserted;

    -- 如果供应商订单状态为已完成，添加一条库存入库信息
    IF EXISTS (SELECT 1 FROM inserted WHERE Status = '已完成')
    BEGIN
        INSERT INTO Inventory (MedicineID, Quantity, TransactionType, TransactionDate)
        SELECT MedicineID, Quantity, '入库', GETDATE()
        FROM inserted
        WHERE Status = '已完成';
    END
END;

-- 当添加一条销售信息时，添加一条库存出库信息
CREATE TRIGGER AddInventoryOutOnSaleInsert
ON Sale
AFTER INSERT
AS
BEGIN
    -- 添加一条库存出库信息
    INSERT INTO Inventory (MedicineID, Quantity, TransactionType, TransactionDate)
    SELECT MedicineID, Quantity, '出库', GETDATE()
    FROM inserted;
END;

二、Java代码实现

1.安装驱动

我用的是SQLServer2022，从官网下载mssql-jdbc.jar包。

请添加图片描述
再在项目中添加为库。

2.数据库连接

String driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            Class.forName(driverClassName);
            String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=数据库名;integratedSecurity=false;encrypt=true;trustServerCertificate=true;";
            String username = "  "; //登录用户名
            String password = "   "; //登录密码
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("连接数据库成功!");
        } catch (Exception e) {
            e.printStackTrace();
        }

通过以上代码实现数据库连接。

 public static void disconnect() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("断开数据库连接!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

3.界面代码

1.LoginFrame

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class LoginFrame extends JFrame {
    private JTextField usernameField;
    private JPasswordField passwordField;
    private static String currentUserRole; // 静态变量保存当前用户的角色

    public LoginFrame() {
        setTitle("登录");
        setSize(350, 200);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);

        JPanel panel = new JPanel(new GridBagLayout());
        GridBagConstraints gbc = new GridBagConstraints();
        gbc.insets = new Insets(5, 5, 5, 5); // 添加一些间距

        gbc.gridx = 0;
        gbc.gridy = 0;
        panel.add(new JLabel("用户名:"), gbc);

        gbc.gridx = 1;
        usernameField = new JTextField(20);
        panel.add(usernameField, gbc);

        gbc.gridx = 0;
        gbc.gridy = 1;
        panel.add(new JLabel("密码:"), gbc);

        gbc.gridx = 1;
        passwordField = new JPasswordField(20);
        panel.add(passwordField, gbc);

        gbc.gridx = 1;
        gbc.gridy = 2;
        JButton loginButton = new JButton("登录");
        loginButton.setFocusPainted(false);
        loginButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (authenticate(usernameField.getText(), new String(passwordField.getPassword()))) {
                    dispose();
                    new MainFrame().setVisible(true);
                } else {
                    JOptionPane.showMessageDialog(LoginFrame.this, "用户名或密码错误", "登录失败", JOptionPane.ERROR_MESSAGE);
                }
            }
        });
        panel.add(loginButton, gbc);

        add(panel);
    }

    private boolean authenticate(String username, String password) {
        try {
            Connection conn = DatabaseManager.getConnection();
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM [User] WHERE Username = ? AND Password = ?");
            stmt.setString(1, username);
            stmt.setString(2, password);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                currentUserRole = rs.getString("Role"); // 保存当前用户的角色
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static String getCurrentUserRole() {
        return currentUserRole; // 返回当前用户的角色
    }
}

2.MainFrame

import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class MainFrame extends JFrame {
    public MainFrame() {
        setTitle("药品库存管理系统");
        setSize(800, 600);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setLocationRelativeTo(null);

        JTabbedPane tabbedPane = new JTabbedPane();
        tabbedPane.addTab("药品信息", new MedicinePanel());
        tabbedPane.addTab("供应商信息", new SupplierPanel());
        tabbedPane.addTab("库存信息", new InventoryPanel());
        tabbedPane.addTab("用户信息", new UserPanel());
        tabbedPane.addTab("订单信息", new OrderPanel());
        tabbedPane.addTab("销售信息", new SalePanel());
        tabbedPane.addTab("供应商订单信息", new SupplierOrderPanel());

        JButton exitButton = new JButton("退出");
        exitButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                int confirm = JOptionPane.showConfirmDialog(MainFrame.this, "确定要退出吗？", "退出确认", JOptionPane.YES_NO_OPTION);
                if (confirm == JOptionPane.YES_OPTION) {
                    DatabaseManager.disconnect();
                    System.exit(0);
                }
            }
        });

        JPanel buttonPanel = new JPanel();
        buttonPanel.add(exitButton);

        getContentPane().add(tabbedPane, "Center");
        getContentPane().add(buttonPanel, "South");
    }
}

3.MedicinePanel

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MedicinePanel extends JPanel {
    private DefaultTableModel tableModel;
    private JTable table;
    private JTextField medicineNameField, supplierIDField, stockQuantityField, searchField;
    private JComboBox<String> statusComboBox;

    public MedicinePanel() {
        setLayout(new BoxLayout(this, BoxLayout.Y_AXIS));

        tableModel = new DefaultTableModel(new String[]{"ID", "名称", "供应商ID", "状态", "库存量"}, 0);
        table = new JTable(tableModel);
        JScrollPane scrollPane = new JScrollPane(table);
        add(scrollPane);

        JPanel inputPanel = new JPanel();
        inputPanel.setLayout(new BoxLayout(inputPanel, BoxLayout.Y_AXIS));

        JPanel medicineNamePanel = new JPanel();
        medicineNamePanel.add(new JLabel("药品名称:"));
        medicineNameField = new JTextField(20);
        medicineNamePanel.add(medicineNameField);
        inputPanel.add(medicineNamePanel);

        JPanel supplierIDPanel = new JPanel();
        supplierIDPanel.add(new JLabel("供应商ID:"));
        supplierIDField = new JTextField(20);
        supplierIDPanel.add(supplierIDField);
        inputPanel.add(supplierIDPanel);

        JPanel statusPanel = new JPanel();
        statusPanel.add(new JLabel("状态:"));
        statusComboBox = new JComboBox<>(new String[]{"在库", "已订购", "已售罄"});
        statusPanel.add(statusComboBox);
        inputPanel.add(statusPanel);

        JPanel stockQuantityPanel = new JPanel();
        stockQuantityPanel.add(new JLabel("库存量:"));
        stockQuantityField = new JTextField(20);
        stockQuantityPanel.add(stockQuantityField);
        inputPanel.add(stockQuantityPanel);

        JPanel searchPanel = new JPanel();
        searchPanel.add(new JLabel("搜索:"));
        searchField = new JTextField(20);
        searchPanel.add(searchField);
        JButton searchButton = new JButton("搜索");
        searchButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                searchMedicines();
            }
        });
        searchPanel.add(searchButton);
        inputPanel.add(searchPanel);

        JPanel buttonPanel = new JPanel();
        JButton addButton = new JButton("添加");
        addButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (checkAdmin()) {
                    addMedicine();
                } else {
                    showPermissionDeniedMessage();
                }
            }
        });
        buttonPanel.add(addButton);

        JButton updateButton = new JButton("更新");
        updateButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (checkAdmin()) {
                    updateMedicine();
                } else {
                    showPermissionDeniedMessage();
                }
            }
        });
        buttonPanel.add(updateButton);

        JButton deleteButton = new JButton("删除");
        deleteButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (checkAdmin()) {
                    deleteMedicine();
                } else {
                    showPermissionDeniedMessage();
                }
            }
        });
        buttonPanel.add(deleteButton);

        JButton refreshButton = new JButton("刷新");
        refreshButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                loadMedicines();
            }
        });
        buttonPanel.add(refreshButton);

        inputPanel.add(buttonPanel);
        add(inputPanel);

        loadMedicines();
    }

    private boolean checkAdmin() {
        String currentUserRole = LoginFrame.getCurrentUserRole(); // 获取当前用户的角色
        return "admin".equals(currentUserRole);
    }

    private void showPermissionDeniedMessage() {
        JOptionPane.showMessageDialog(this, "无权限操作", "提示", JOptionPane.WARNING_MESSAGE);
    }

    private void addMedicine() {
        try {
            Connection conn = DatabaseManager.getConnection();
            PreparedStatement stmt = conn.prepareStatement("INSERT INTO Medicine (MedicineName, SupplierID, Status, StockQuantity) VALUES (?, ?, ?, ?)");

            stmt.setString(1, medicineNameField.getText());
            stmt.setInt(2, Integer.parseInt(supplierIDField.getText()));
            stmt.setString(3, (String) statusComboBox.getSelectedItem());
            stmt.setInt(4, Integer.parseInt(stockQuantityField.getText()));

            stmt.executeUpdate();
            loadMedicines();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void updateMedicine() {
        int selectedRow = table.getSelectedRow();
        if (selectedRow != -1) {
            try {
                Connection conn = DatabaseManager.getConnection();
                PreparedStatement stmt = conn.prepareStatement("UPDATE Medicine SET MedicineName = ?, SupplierID = ?, Status = ?, StockQuantity = ? WHERE MedicineID = ?");

                stmt.setString(1, medicineNameField.getText());
                stmt.setInt(2, Integer.parseInt(supplierIDField.getText()));
                stmt.setString(3, (String) statusComboBox.getSelectedItem());
                stmt.setInt(4, Integer.parseInt(stockQuantityField.getText()));
                stmt.setInt(5, (int) tableModel.getValueAt(selectedRow, 0));

                stmt.executeUpdate();
                loadMedicines();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            JOptionPane.showMessageDialog(this, "请选择要更新的药品", "提示", JOptionPane.WARNING_MESSAGE);
        }
    }

    private void deleteMedicine() {
        int selectedRow = table.getSelectedRow();
        if (selectedRow != -1) {
            try {
                Connection conn = DatabaseManager.getConnection();
                PreparedStatement stmt = conn.prepareStatement("DELETE FROM Medicine WHERE MedicineID = ?");

                stmt.setInt(1, (int) tableModel.getValueAt(selectedRow, 0));

                stmt.executeUpdate();
                loadMedicines();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            JOptionPane.showMessageDialog(this, "请选择要删除的药品", "提示", JOptionPane.WARNING_MESSAGE);
        }
    }

    private void loadMedicines() {
        tableModel.setRowCount(0);
        try {
            Connection conn = DatabaseManager.getConnection();
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Medicine");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                tableModel.addRow(new Object[]{
                        rs.getInt("MedicineID"),
                        rs.getString("MedicineName"),
                        rs.getInt("SupplierID"),
                        rs.getString("Status"),
                        rs.getInt("StockQuantity")
                });
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void searchMedicines() {
        tableModel.setRowCount(0);
        String keyword = searchField.getText();
        try {
            Connection conn = DatabaseManager.getConnection();
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Medicine WHERE MedicineName LIKE ? OR Status LIKE ?");
            stmt.setString(1, "%" + keyword + "%");
            stmt.setString(2, "%" + keyword + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                tableModel.addRow(new Object[]{
                        rs.getInt("MedicineID"),
                        rs.getString("MedicineName"),
                        rs.getInt("SupplierID"),
                        rs.getString("Status"),
                        rs.getInt("StockQuantity")
                });
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4.UserPanel、SupplierPanel、SalePanel、OrderPanel、InventoryPanel

以上界面的实现与MedicinePanel 相同。

import javax.swing.*;

public class Main {
    public static void main(String[] args) {
        DatabaseManager.connect();
        SwingUtilities.invokeLater(new Runnable() {
            @Override
            public void run() {
                new LoginFrame().setVisible(true);
            }
        });
    }
}