用java连接数据库写一个商品库存管理系统（功能注释）

好的，下面是一个简单的Java商品库存管理系统，使用MySQL数据库进行数据存储和管理。这个系统包括以下功能：

1. 添加新商品：允许用户输入新商品的名称、数量和价格，并将其保存到MySQL数据库中。
2. 更新商品信息：允许用户选择一个商品并更新其数量和价格。
3. 删除商品：允许用户选择一个商品并将其从数据库中删除。
4. 列出所有商品：在控制台上列出所有商品的名称、数量和价格。
5. 搜索商品：允许用户输入关键字并搜索匹配的商品。

在开始之前，请确保您已经安装了MySQL数据库和Java开发环境（如Eclipse或IntelliJ IDEA）。接下来，您需要创建一个名为“products”的数据库，并在其中创建一个名为“inventory”的表格，包括以下字段：id（自增 ID）、name（商品名称）、quantity（库存量）和 price（价格）。

然后，您可以使用以下Java代码连接到MySQL数据库并实现上述功能：

import java.sql.*;

public class InventorySystem {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/products";

    // Database credentials
    static final String USER = "username";
    static final String PASS = "password";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            // Open a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // Execute a query to create the inventory table if it doesn't exist
            stmt = conn.createStatement();
            String sql = "CREATE TABLE IF NOT EXISTS inventory " +
                         "(id INTEGER not NULL AUTO_INCREMENT, " +
                         " name VARCHAR(255), " +
                         " quantity INTEGER, " +
                         " price DOUBLE, " +
                         " PRIMARY KEY ( id ))";
            stmt.executeUpdate(sql);

            // Loop until the user quits the program
            boolean quit = false;
            while (!quit) {
                System.out.println("Select an option:");
                System.out.println("1. Add new product");
                System.out.println("2. Update product information");
                System.out.println("3. Delete product");
                System.out.println("4. List all products");
                System.out.println("5. Search for a product");
                System.out.println("6. Quit");
                System.out.print("Enter your choice: ");

                // Read the user's choice
                int choice = Integer.parseInt(System.console().readLine());

                // Perform the selected action
                switch (choice) {
                    case 1:
                        addProduct(conn);
                        break;
                    case 2:
                        updateProduct(conn);
                        break;
                    case 3:
                        deleteProduct(conn);
                        break;
                    case 4:
                        listProducts(conn);
                        break;
                    case 5:
                        searchProduct(conn);
                        break;
                    case 6:
                        quit = true;
                        break;
                    default:
                        System.out.println("Invalid choice");
                        break;
                }
            }
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
                // Nothing we can do
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    // Add a new product to the inventory
    private static void addProduct(Connection conn) throws SQLException {
        System.out.print("Enter product name: ");
        String name = System.console().readLine();
        System.out.print("Enter quantity: ");
        int quantity = Integer.parseInt(System.console().readLine());
        System.out.print("Enter price: ");
        double price = Double.parseDouble(System.console().readLine());

        String sql = "INSERT INTO inventory (name, quantity, price) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setInt(2, quantity);
        pstmt.setDouble(3, price);
        pstmt.executeUpdate();

        System.out.println("Product added successfully");
    }

    // Update an existing product's information
    private static void updateProduct(Connection conn) throws SQLException {
        listProducts(conn);
        System.out.print("Enter product ID to update: ");
        int id = Integer.parseInt(System.console().readLine());
        System.out.print("Enter new quantity: ");
        int quantity = Integer.parseInt(System.console().readLine());
        System.out.print("Enter new price: ");
        double price = Double.parseDouble(System.console().readLine());

        String sql = "UPDATE inventory SET quantity=?, price=? WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, quantity);
        pstmt.setDouble(2, price);
        pstmt.setInt(3, id);
        int rows = pstmt.executeUpdate();

        if (rows > 0) {
            System.out.println("Product updated successfully");
        } else {
            System.out.println("Product not found");
        }
    }

    // Delete a product from the inventory
    private static void deleteProduct(Connection conn) throws SQLException {
        listProducts(conn);
        System.out.print("Enter product ID to delete: ");
        int id = Integer.parseInt(System.console().readLine());

        String sql = "DELETE FROM inventory WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        int rows = pstmt.executeUpdate();

        if (rows > 0) {
            System.out.println("Product deleted successfully");
        } else {
            System.out.println("Product not found");
        }
    }

    // List all products in the inventory
    private static void listProducts(Connection conn) throws SQLException {
        String sql = "SELECT * FROM inventory";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int quantity = rs.getInt("quantity");
            double price = rs.getDouble("price");
            System.out.println(id + "\t" + name + "\t" + quantity + "\t" + price);
        }

        rs.close();
        stmt.close();
    }

    // Search for a product by name
    private static void searchProduct(Connection conn) throws SQLException {
        System.out.print("Enter search keyword: ");
        String keyword = System.console().readLine();

        String sql = "SELECT * FROM inventory WHERE name LIKE ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "%" + keyword + "%");
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int quantity = rs.getInt("quantity");
            double price = rs.getDouble("price");
            System.out.println(id + "\t" + name + "\t" + quantity + "\t" + price);
        }

        rs.close();
        pstmt.close();
    }
}

注意：在实际应用中，您应该将用户名和密码存储在安全的地方，而不是直接在代码中硬编码。