68、关系型数据与订单系统开发详细解析

关系型数据与订单系统开发详细解析

1. 关系型数据库基础概念

在关系型数据库里，一张表的某列能够和其他表的列建立关联，这种关联会在表之间形成关系。例如，在一个数据库操作会话中，用户输入如下 SQL 语句：

SELECT
   ProdNum, Price
FROM
   Coffee
WHERE
   Price > 9.0
ORDER BY
   ProdNum

当用户点击提交按钮后，会显示查询结果。

为了让数据库更具实用性，我们可以添加新表来存储更多信息。比如，在已有 Coffee 表和 Customer 表的基础上，为了存储未支付的客户订单信息，我们要创建 UnpaidOrder 表，其结构如下：
| 列名 | 数据类型 | 说明 |
| -------------- | ----------- | ------------ |
| CustomerNumber | CHAR(10) | 外键，关联 Customer 表的 CustomerNumber |
| ProdNum | CHAR(10) | 外键，关联 Coffee 表的 ProdNum |
| OrderDate | CHAR(10) | 订单日期 |
| Quantity | DOUBLE | 订单数量 |
| Cost | DOUBLE | 订单总成本 |

创建 UnpaidOrder 表的 SQL 语句如下：

CREATE TABLE UnpaidOrder
( CustomerNumber CHAR(10) NOT NULL REFERENCES Customer(CustomerNumber),
  ProdNum CHAR(10) NOT NULL REFERENCES Coffee(ProdNum),
  OrderDate CHAR(10),
  Quantity DOUBLE,
  Cost DOUBLE )

这里的 REFERENCES 限定符用于确保数据的引用完整性。例如， REFERENCES Customer(CustomerNumber) 表示 CustomerNumber 列引用了 Customer 表的 CustomerNumber 列，插入数据时，该列的值必须与 Customer 表中对应列的值匹配。

2. 数据库表间关系

系统设计人员常使用实体关系图（ERD）来展示数据库表之间的关系。常见的关系有两种：
- 一对多关系 ：表 A 中的每一行可能被表 B 中的多行引用。
- 多对一关系 ：表 A 中的多行可能引用表 B 中的一行。

以 Customer 表和 UnpaidOrder 表为例，它们之间存在一对多和多对一的关系，即一个客户可以下多个订单，多个订单可以属于同一个客户。同样， Coffee 表和 UnpaidOrder 表之间也存在这样的关系。

3. 多表数据连接查询

当相关数据存储在多个表中时，在 SELECT 语句中常常需要从不同表中提取数据。例如，要查看所有未支付订单的详细信息，包括客户编号、客户姓名、订单日期、咖啡描述和成本，需要从 Customer 、 UnpaidOrder 和 Coffee 表中提取数据。由于部分表存在同名列，需要使用限定列名，其格式为 TableName.ColumnName 。以下是具体的查询语句：

SELECT
   Customer.CustomerNumber,
   Customer.Name,
   UnpaidOrder.OrderDate,
   Coffee.Description,
   UnpaidOrder.Cost
FROM
   Customer, UnpaidOrder, Coffee
WHERE
   UnpaidOrder.CustomerNumber = Customer.CustomerNumber AND
   UnpaidOrder.ProdNum = Coffee.ProdNum

这个查询语句分为三个部分：
1. 选择列 ：指定要查询的列。
2. 指定表 ：指定要从哪些表中提取数据。
3. 搜索条件 ：告诉数据库管理系统如何关联表中的行。

注意 ：在连接多个表的数据时，一定要使用 WHERE 子句指定关联列的搜索条件，否则会得到大量不相关的数据。

4. 订单系统示例

为了更好地理解关系型数据库的应用，我们来看一个订单录入系统的示例。这个系统需要使用 Coffee 表、 Customer 表和 UnpaidOrder 表。在使用该系统前，需要先运行相应的程序创建这些表。

4.1 CoffeeDBManager 类

该类用于对数据库执行各种操作，代码如下：

import java.sql.*;

/**
 * The CoffeeDBManager class performs operations on
 * the CoffeeDB database.
 */
public class CoffeeDBManager {
    // Constant for database URL.
    public final String DB_URL =
            "jdbc:derby:CoffeeDB";
    // Field for the database connection
    private Connection conn;

    /**
     * Constructor
     */
    public CoffeeDBManager() throws SQLException {
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
    }

    /**
     * The getCoffeeNames method returns an array
     * of Strings containing all the coffee names.
     */
    public String[] getCoffeeNames() throws SQLException {
        // Create a Statement object for the query.
        Statement stmt =
                conn.createStatement(
                        ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
        // Execute the query.
        ResultSet resultSet = stmt.executeQuery(
                "SELECT Description FROM Coffee");
        // Get the number of rows
        resultSet.last();                 // Move to last row
        int numRows = resultSet.getRow(); // Get row number
        resultSet.first();                // Move to first row
        // Create an array for the coffee names.
        String[] listData = new String[numRows];
        // Populate the array with coffee names.
        for (int index = 0; index < numRows; index++) {
            // Store the coffee name in the array.
            listData[index] = resultSet.getString(1);
            // Go to the next row in the result set.
            resultSet.next();
        }
        // Close the connection and statement objects.
        conn.close();
        stmt.close();
        // Return the listData array.
        return listData;
    }

    /**
     * The getProdNum method returns a specific
     * coffee's product number.
     * @param coffeeName The specified coffee.
     */
    public String getProdNum(String coffeeName) throws SQLException {
        String prodNum = ""; // Product number
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
        // Create a Statement object for the query.
        Statement stmt = conn.createStatement();
        // Execute the query.
        ResultSet resultSet = stmt.executeQuery(
                "SELECT ProdNum " +
                        "FROM Coffee " +
                        "WHERE Description = '" +
                        coffeeName + "'");
        // If the result set has a row, go to it
        // and retrieve the product number.
        if (resultSet.next())
            prodNum = resultSet.getString(1);
        // Close the Connection and Statement objects.
        conn.close();
        stmt.close();
        // Return the product number.
        return prodNum;
    }

    /**
     * The getCoffeePrice method returns the price
     * of a coffee.
     * @param prodNum The specified product number.
     */
    public double getCoffeePrice(String prodNum) throws SQLException {
        double price = 0.0;  // Coffee price
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
        // Create a Statement object for the query.
        Statement stmt = conn.createStatement();
        // Execute the query.
        ResultSet resultSet = stmt.executeQuery(
                "SELECT Price " +
                        "FROM Coffee " +
                        "WHERE ProdNum = '" +
                        prodNum + "'");
        // If the result set has a row, go to it
        // and retrieve the price.
        if (resultSet.next())
            price = resultSet.getDouble(1);
        // Close the connection and statement objects.
        conn.close();
        stmt.close();
        // Return the price.
        return price;
    }

    /**
     * The getCustomerNames method returns an array
     * of Strings containing all the customer names.
     */
    public String[] getCustomerNames() throws SQLException {
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
        // Create a Statement object for the query.
        Statement stmt =
                conn.createStatement(
                        ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
        // Execute the query.
        ResultSet resultSet =
                stmt.executeQuery("SELECT Name FROM Customer");
        // Get the number of rows
        resultSet.last();                 // Move last row
        int numRows = resultSet.getRow(); // Get row number
        resultSet.first();                // Move to first row
        // Create an array for the customer names.
        String[] listData = new String[numRows];
        // Populate the array with customer names.
        for (int index = 0; index < numRows; index++) {
            // Store the customer name in the array.
            listData[index] = resultSet.getString(1);
            // Go to the next row in the result set.
            resultSet.next();
        }
        // Close the connection and statement objects.
        conn.close();
        stmt.close();
        // Return the listData array.
        return listData;
    }

    /**
     * The getCustomerNum method returns a specific
     * customer's number.
     * @param name The specified customer's name.
     */
    public String getCustomerNum(String name) throws SQLException {
        String customerNumber = "";
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
        // Create a Statement object for the query.
        Statement stmt = conn.createStatement();
        // Execute the query.
        ResultSet resultSet =
                stmt.executeQuery("SELECT CustomerNumber " +
                        "FROM Customer " +
                        "WHERE Name = '" + name + "'");
        if (resultSet.next())
            customerNumber = resultSet.getString(1);
        // Close the connection and statement objects.
        conn.close();
        stmt.close();
        // Return the customer number.
        return customerNumber;
    }

    /**
     * The submitOrder method submits an order to
     * the UnpaidOrder table in the CoffeeDB database.
     * @param custNum The customer number.
     * @param prodNum The product number.
     * @param quantity The quantity ordered.
     * @param price The price.
     * @param orderDate The order date.
     */
    public void submitOrder(String custNum, String prodNum,
                            int quantity, double price,
                            String orderDate) throws SQLException {
        // Calculate the cost of the order.
        double cost = quantity * price;
        // Create a connection to the database.
        conn = DriverManager.getConnection(DB_URL);
        // Create a Statement object for the query.
        Statement stmt = conn.createStatement();
        // Execute the query.
        stmt.executeUpdate("INSERT INTO UnpaidOrder VALUES('" +
                custNum + "', '" +
                prodNum + "', '" + orderDate + "', " +
                quantity + ", " + cost + ")");
        // Close the connection and statement objects.
        conn.close();
        stmt.close();
    }
}

这个类的主要方法及其功能如下：
- 构造函数 ：建立与数据库的连接。
- getCoffeeNames ：返回 Coffee 表中所有咖啡的名称。
- getProdNum ：根据咖啡名称返回咖啡的产品编号。
- getCoffeePrice ：根据产品编号返回咖啡的价格。
- getCustomerNames ：返回 Customer 表中所有客户的名称。
- getCustomerNum ：根据客户名称返回客户编号。
- submitOrder ：向 UnpaidOrder 表中提交订单。

5. 其他相关类

除了 CoffeeDBManager 类，订单系统还包含以下几个类：
- CustomerPanel 类 ：继承自 JPanel ，使用 JList 组件显示 Customer 表中的所有客户名称。

import java.sql.*;
import javax.swing.*;

/**
 * The CustomerPanel class is a custom JPanel that
 * shows a list of customers in a JList.
 */
public class CustomerPanel extends JPanel {
    private final int NUM_ROWS = 5; // Number of rows to display
    private JList customerList;     // A list for customer names
    String[] names;                 // To hold customer names

    /**
     * Constructor
     */
    public CustomerPanel() {
        try {
            // Create a CoffeeDBManager object.
            CoffeeDBManager dbManager = new CoffeeDBManager();
            // Get a list of customer names as a String array.
            names = dbManager.getCustomerNames();
            // Create a JList object to hold customer names.
            customerList = new JList(names);
            // Set the number of visible rows.
            customerList.setVisibleRowCount(NUM_ROWS);
            // Put the JList object in a scroll pane.
            JScrollPane scrollPane =
                    new JScrollPane(customerList);
            // Add the scroll pane to the panel.
            add(scrollPane);
            // Add a titled border to the panel.
            setBorder(BorderFactory.createTitledBorder(
                    "Select a Customer"));
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.exit(0);
        }
    }

    /**
     * The getCustomer method returns the customer
     * name selected by the user.
     */
    public String getCustomer() {
        // The JList class's getSelectedValue method returns
        // an Object reference, so we will cast it to a String.
        return (String) customerList.getSelectedValue();
    }
}

• CoffeePanel 类 ：同样继承自 JPanel ，使用 JList 组件显示 Coffee 表中的所有咖啡名称。

import java.sql.*;
import javax.swing.*;

/**
 * The CoffeePanel class is a custom JPanel that
 * shows a list of coffees in a JList.
 */
public class CoffeePanel extends JPanel {
    private final int NUM_ROWS = 5; // Number of rows to display
    private JList coffeeList;       // A list for coffee descriptions
    String[] coffeeNames;           // To hold coffee names

    /**
     * Constructor
     */
    public CoffeePanel() {
        try {
            // Create a CoffeeDBManager object.
            CoffeeDBManager dbManager = new CoffeeDBManager();
            // Get a list of coffee names as a String array.
            coffeeNames = dbManager.getCoffeeNames();
            // Create a JList object to hold the coffee names.
            coffeeList = new JList(coffeeNames);
            // Set the number of visible rows.
            coffeeList.setVisibleRowCount(NUM_ROWS);
            // Put the JList object in a scroll pane.
            JScrollPane scrollPane = new JScrollPane(coffeeList);
            // Add the scroll pane to the panel.
            add(scrollPane);
            // Add a titled border to the panel.
            setBorder(BorderFactory.createTitledBorder(
                    "Select a Coffee"));
        } catch (SQLException ex) {
            ex.printStackTrace();
            System.exit(0);
        }
    }

    /**
     * The getCoffee method returns the coffee
     * description selected by the user.
     */
    public String getCoffee() {
        // The JList class's getSelectedValue method returns
        // an Object reference, so we will cast it to a String.
        return (String) coffeeList.getSelectedValue();
    }
}

• QtyDatePanel 类 ：继承自 JPanel ，提供文本框让用户输入订单数量和订单日期。

import javax.swing.*;
import java.awt.*;

/**
 * The QtyDatePanel presents text fields for the
 * quantity of coffee being ordered and the order
 * date.
 */
public class QtyDatePanel extends JPanel {
    private JTextField qtyTextField;   // Order quantity
    private JTextField dateTextField;  // order date

    /**
     * Constructor
     */
    public QtyDatePanel() {
        // Create a label prompting the user
        // for a quantity.
        JLabel qtyPrompt = new JLabel("Quantity");
        // Create a text field for the quantity.
        qtyTextField = new JTextField(10);
        // Create a label prompting the user
        // for a date.
        JLabel datePrompt = new JLabel("Order Date");
        // Create a text field for the date.
        dateTextField = new JTextField(10);
        // Create a grid layout manager, 4 rows, 1 column.
        setLayout(new GridLayout(4, 1));
        // Add the components to the panel.
        add (qtyPrompt);
        add (qtyTextField);
        add (datePrompt);
        add (dateTextField);
    }

    /**
     * The getQuantity method returns the quantity
     * entered by the user.
     * @return The value entered into qtyTextField.
     */
    public int getQuantity() {
        return Integer.parseInt(qtyTextField.getText());
    }

    /**
     * The getDate method returns the quantity
     * entered by the user.
     * @return The value entered into dateTextField.
     */
    public String getDate() {
        return dateTextField.getText();
    }

    /**
     * The clear method clears the text fields.
     */
    public void clear() {
        qtyTextField.setText("");
        dateTextField.setText("");
    }
}

通过以上这些类，订单系统可以实现用户选择客户、选择咖啡、输入订单数量和日期，并提交订单的功能。整个订单系统的工作流程可以用以下 mermaid 流程图表示：

graph LR
    A[启动系统] --> B[显示客户列表和咖啡列表]
    B --> C{用户选择客户和咖啡}
    C -->|选择完成| D[用户输入订单数量和日期]
    D --> E[点击提交按钮]
    E --> F[获取客户编号、产品编号和咖啡价格]
    F --> G[计算订单成本并提交订单]
    G --> H[清空数量和日期文本框]
    H --> I[提示订单已提交]
    C -->|未选择完成| B

这个流程图清晰地展示了订单系统的主要操作流程，从系统启动到订单提交的整个过程。通过这些类和流程图，我们可以更深入地理解关系型数据库在实际应用中的使用。

6. PlaceOrder 类

PlaceOrder 类是订单录入系统的主类，它继承自 JFrame ，负责创建 GUI 界面并处理用户的订单提交和退出操作。以下是该类的代码：

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;

/**
 * The PlaceOrder class is a simple order entry system.
 */
public class PlaceOrder extends JFrame {
    CustomerPanel customerPanel; // Panel for customers
    CoffeePanel coffeePanel;     // Panel for coffees
    QtyDatePanel qtyDatePanel;   // Panel for quantity and date
    JPanel buttonPanel;          // Panel for buttons

    /**
     * Constructor
     */
    public PlaceOrder() {
        // Set the window title.
        setTitle("Place Order");
        // Specify an action for the close button.
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        // Create a CustomerPanel object.
        customerPanel = new CustomerPanel();
        // Create a CoffeePanel object.
        coffeePanel = new CoffeePanel();
        // Create a QtyDatePanel object.
        qtyDatePanel = new QtyDatePanel();
        // Build the ButtonPanel object.
        buildButtonPanel();
        // Create a BorderLayout manager.
        setLayout(new BorderLayout());
        // Add the panels to the content pane.
        add(customerPanel, BorderLayout.WEST);
        add(coffeePanel, BorderLayout.CENTER);
        add(qtyDatePanel, BorderLayout.EAST);
        add(buttonPanel, BorderLayout.SOUTH);
        // Pack and display the window.
        pack();
        setVisible(true);
    }

    /**
     * The buildButtonPanel method builds a panel for
     * the Submit and Exit buttons.
     */
    public void buildButtonPanel() {
        // Create a panel for the buttons.
        buttonPanel = new JPanel();
        // Create a Submit button and add an action listener.
        JButton submitButton = new JButton("Submit");
        submitButton.addActionListener(new SubmitButtonListener());
        // Create an Exit button.
        JButton exitButton = new JButton("Exit");
        exitButton.addActionListener(new ExitButtonListener());
        // Add the buttons to the panel.
        buttonPanel.add(submitButton);
        buttonPanel.add(exitButton);
    }

    /**
     * Private inner class that handles submit button events
     */
    private class SubmitButtonListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            try {
                // Get the customer name from the CustomerPanel object.
                String customerName = customerPanel.getCustomer();
                // Get the coffee description from the CoffeePanel.
                String coffee = coffeePanel.getCoffee();
                // Get the quantity from the QtyDatePanel object.
                int qty = qtyDatePanel.getQuantity();
                // Get the order date from the QtyDatePanel object.
                String orderDate = qtyDatePanel.getDate();
                // Create a CoffeeDBManager object.
                CoffeeDBManager dbManager = new CoffeeDBManager();
                // Get the customer number.
                String customerNum =
                        dbManager.getCustomerNum(customerName);
                // Get the coffee product number.
                String prodNum = dbManager.getProdNum(coffee);
                // Get the coffee price per pound.
                double price = dbManager.getCoffeePrice(prodNum);
                // Submit the order.
                dbManager.submitOrder(customerNum, prodNum, qty,
                        price, orderDate);
                // Clear the text fields for quantity and order date.
                qtyDatePanel.clear();
                // Let the user know the order was placed.
                JOptionPane.showMessageDialog(null, "Order Placed.");
            } catch (SQLException ex) {
                ex.printStackTrace();
                System.exit(0);
            }
        }
    }

    /**
     * Private inner class that handles exit button events
     */
    private class ExitButtonListener implements ActionListener {
        public void actionPerformed(ActionEvent e) {
            System.exit(0);
        }
    }

    /**
     * main method
     */
    public static void main(String[] args) {
        new PlaceOrder();
    }
}

该类的主要功能和操作步骤如下：
1. 构造函数 ：
- 设置窗口标题和关闭操作。
- 创建 CustomerPanel 、 CoffeePanel 和 QtyDatePanel 对象。
- 调用 buildButtonPanel 方法创建按钮面板。
- 设置布局管理器并将各个面板添加到内容面板中。
- 打包并显示窗口。
2. buildButtonPanel 方法 ：
- 创建一个面板用于放置按钮。
- 创建“Submit”和“Exit”按钮，并为它们添加相应的事件监听器。
- 将按钮添加到面板中。
3. SubmitButtonListener 类 ：
- 处理“Submit”按钮的点击事件。
- 获取用户选择的客户名称、咖啡名称、订单数量和订单日期。
- 创建 CoffeeDBManager 对象。
- 通过 CoffeeDBManager 对象获取客户编号、产品编号和咖啡价格。
- 调用 submitOrder 方法提交订单。
- 清空数量和日期文本框。
- 显示提示信息告知用户订单已提交。
4. ExitButtonListener 类 ：
- 处理“Exit”按钮的点击事件，退出程序。

7. 订单系统的使用与验证

在完成订单系统的开发后，我们可以按照以下步骤使用该系统并验证订单数据：
1. 创建数据库表 ：
- 运行 CreateCustomerTable.java 程序创建 Customer 表并添加示例数据。
- 运行 CreateUnpaidOrderTable.java 程序创建 UnpaidOrder 表。
2. 启动订单系统 ：
- 运行 PlaceOrder 类的 main 方法，启动订单录入系统。
3. 提交订单 ：
- 在客户列表中选择一个客户。
- 在咖啡列表中选择一种咖啡。
- 输入订单数量和订单日期。
- 点击“Submit”按钮提交订单。
4. 验证订单数据 ：
- 运行 CoffeeDBViewer 应用程序。
- 输入以下 SELECT 语句查询订单相关数据：

SELECT
   Customer.CustomerNumber,
   Customer.Name,
   UnpaidOrder.OrderDate,
   Coffee.Description,
   UnpaidOrder.Cost
FROM
   Customer, UnpaidOrder, Coffee
WHERE
   UnpaidOrder.CustomerNumber = Customer.CustomerNumber AND
   UnpaidOrder.ProdNum = Coffee.ProdNum

- 点击“Submit”按钮，查看查询结果，验证订单数据是否正确插入到 `UnpaidOrder` 表中。

8. 总结

通过以上的介绍，我们详细了解了关系型数据库的基本概念、表间关系、多表数据连接查询以及如何使用 Java 开发一个简单的订单录入系统。以下是本文的主要内容总结：
| 知识点 | 描述 |
| ---- | ---- |
| 关系型数据库基础 | 表之间通过外键建立关联，使用 REFERENCES 限定符确保引用完整性。 |
| 表间关系 | 常见的关系有一对多和多对一，可通过实体关系图展示。 |
| 多表数据连接查询 | 使用 SELECT 语句从多个表中提取数据，需使用限定列名和 WHERE 子句指定关联条件。 |
| 订单系统开发 | 通过多个 Java 类实现订单录入系统，包括 CoffeeDBManager 、 CustomerPanel 、 CoffeePanel 、 QtyDatePanel 和 PlaceOrder 类。 |

整个订单系统的开发过程可以用以下 mermaid 流程图表示：

graph LR
    A[数据库表创建] --> B[启动订单系统]
    B --> C[用户操作]
    C -->|提交订单| D[获取数据并计算成本]
    D --> E[插入订单数据到 UnpaidOrder 表]
    E --> F[验证订单数据]
    C -->|退出系统| G[结束程序]

这个流程图概括了从数据库表创建到订单提交和验证的整个过程，帮助我们更清晰地理解订单系统的开发和使用流程。通过实际的代码示例和操作步骤，我们可以将关系型数据库的理论知识应用到实际项目中，实现一个功能完整的订单录入系统。