69、数据库与编程综合知识解析

数据库与编程综合知识解析

1. 数据库高级主题

1.1 事务处理

在数据库操作中，有时一个任务需要执行多个数据库更新操作，这种操作被称为事务。例如，银行自动从支票账户扣除汽车贷款还款时，需要同时减少支票账户余额和汽车贷款余额。为了确保事务的完整性，所有步骤都必须执行。如果任何一个步骤失败，整个事务都不应执行。

数据库系统通常提供撤销部分完成步骤的方法，这涉及到两个重要概念：提交（commit）和回滚（rollback）。提交意味着对数据库进行永久性更改，而回滚则是撤销这些更改。

在 JDBC 中，默认情况下， Connection 类处于自动提交模式。在自动提交模式下，所有数据库更新操作一旦执行就会成为永久性更改。若要关闭自动提交模式，可以使用 Connection 类的 setAutoCommit 方法，并传入 false 参数，示例代码如下：

conn.setAutoCommit(false);

执行提交命令可以调用 Connection 类的 commit 方法：

conn.commit();

执行回滚命令可以调用 Connection 类的 rollback 方法：

conn.rollback();

以下是一个处理事务的示例代码。假设在 CoffeeDB 数据库中添加了一个名为 Inventory 的新表，用于存储每种咖啡的库存数量。当有订单时，需要同时更新 Inventory 表和 UnpaidOrder 表。

Connection conn = DriverManager.getConnection(DB_URL);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
// 尝试执行事务
try {
    // 更新库存记录
    stmt.executeUpdate("UPDATE Inventory SET Qty = Qty - " + qtyOrdered + " WHERE ProdNum = " + prodNum);
    // 向 UnpaidOrder 表中添加订单
    stmt.executeUpdate("INSERT INTO UnpaidOrder VALUES('" + custNum + "', '" + prodNum + "', '" + orderDate + "', " + qtyOrdered + ", " + cost + ")");
    // 提交所有更新
    conn.commit();
} catch (SQLException ex) {
    // 回滚更改
    conn.rollback();
}

在上述代码中， try 块中执行数据库更新语句后，调用 commit 方法提交更改；如果发生错误， catch 块中调用 rollback 方法回滚更改。

1.2 存储过程

许多商业数据库系统允许创建 SQL 语句并将其存储在数据库管理系统（DBMS）中，这些 SQL 语句被称为存储过程。其他使用该 DBMS 的应用程序可以执行这些存储过程。如果某个 SQL 语句在多个应用程序中经常使用，将其存储为存储过程可以提高执行效率，因为存储过程已经存在于 DBMS 中，通常比从外部应用程序提交的 SQL 语句执行速度更快。

每个 DBMS 都有自己创建存储过程的 SQL 语法，需要查阅相应的 DBMS 文档来确定具体格式。编写好存储过程后，可以使用 Statement 类的 execute 方法将其提交给 DBMS。要执行存储过程，需要创建一个 CallableStatement 对象，这是 java.sql 包中的一个接口。可以通过调用 Connection 类的 prepareCall 方法来创建 CallableStatement 对象。

1.3 常见错误避免

在编写 SQL 语句和 Java 代码时，需要注意以下常见错误：
- 使用 == 运算符而非 = 运算符 ：在 SQL 中，等于运算符是单个 = 符号。
- 使用双引号而非单引号 ：SQL 中的字符串字面量应使用单引号括起来。
- 使用 && 和 || 逻辑运算符 ：SQL 中的逻辑 AND 和 OR 运算符是 AND 和 OR 关键字。
- UPDATE 语句中缺少 WHERE 子句 ：使用 UPDATE 语句时，要确保包含 WHERE 子句和条件表达式，否则可能会更改表中所有行的内容。
- DELETE 语句中缺少 WHERE 子句 ：使用 DELETE 语句时，要确保包含 WHERE 子句和条件表达式，否则可能会删除表中所有行。
- 连接数据时缺少 WHERE 子句 ：从多个表中连接数据时，要使用 WHERE 子句指定连接条件，否则可能会得到大量不相关的数据。

1.4 相关概念总结

概念	描述
事务	需要多个数据库更新操作的任务，所有步骤必须全部执行，若有一步失败则全部回滚
提交（commit）	对数据库进行永久性更改
回滚（rollback）	撤销数据库更改
存储过程	存储在 DBMS 中的 SQL 语句，可被其他应用程序调用

1.5 操作流程总结

graph LR
    A[开启数据库连接] --> B[关闭自动提交模式]
    B --> C[执行数据库更新操作]
    C --> D{是否成功}
    D -- 是 --> E[提交更改]
    D -- 否 --> F[回滚更改]
    E --> G[关闭数据库连接]
    F --> G

2. 数据库与编程基础概念

2.1 数据库相关技术与语言

• JDBC ：Java 应用程序与 DBMS 进行通信的技术。
• SQL ：用于操作数据库管理系统的标准语言。

2.2 数据库基本概念

• 主键 ：表中每一行的唯一标识列，可用于识别特定行。
• WHERE 子句 ：用于指定从表中检索数据的搜索条件。
• UPDATE 语句 ：用于更改现有行的值。
• INSERT 语句 ：用于向表中插入新行。
• DELETE 语句 ：用于从表中删除行。
• DROP 语句 ：用于删除整个表。
• 外键 ：一个表中的列，引用另一个表中的主键。

2.3 Java 与数据库交互

• JDBC 驱动 ：用于与特定 DBMS 进行通信的 Java 类。
• 数据库 URL ：指定访问数据库的协议、数据库名称等信息的字符串。
• Statement 接口方法 ：
  • executeQuery ：用于执行 SELECT 语句。
  • executeUpdate ：用于执行 UPDATE 、 INSERT 和 DELETE 语句。

2.4 常见问题解答

2.4.1 数据库组织

数据库中的数据以行、表和列的形式组织。表是数据的集合，每一行代表一条记录，每一列代表一个特定的属性。

2.4.2 DELETE 和 DROP 语句的区别

DELETE 语句用于删除表中的行，而 DROP 语句用于删除整个表。

2.4.3 事务的定义

事务是需要多个数据库更新操作的任务，为了确保数据的一致性，所有步骤必须全部执行，若有一步失败则全部回滚。

2.4.4 常见 SQL 关系运算符

比较类型	运算符
大于	>
小于	<
大于等于	>=
小于等于	<=
等于	=
不等于	!= 或 <>

2.4.5 发送 SQL 语句到 DBMS 的步骤

1. 创建 Connection 对象，建立与数据库的连接。
2. 创建 Statement 对象。
3. 使用 Statement 对象执行 SQL 语句。
4. 处理执行结果。
5. 关闭 Statement 和 Connection 对象。

2.4.6 元数据和结果集元数据

元数据是关于数据的数据，结果集元数据是关于结果集的元数据，例如列数、列名、列类型等。结果集元数据在需要动态处理结果集时非常有用。

2.4.7 提交和回滚命令的区别

提交命令（ commit ）用于将数据库更改永久保存，而回滚命令（ rollback ）用于撤销自上一次提交以来的所有数据库更改。

2.5 编程挑战示例

以下是一些编程挑战的示例，帮助巩固所学知识：
- 学生记录查询 ：编写一个应用程序，连接到 StudentDB 数据库，从 StudentResult 表中找出得分最高和最低的学生姓名。
- 客户信息更新 ：编写一个应用程序，连接到 CoffeeDB 数据库，允许用户选择一个客户并更改其信息（不包括客户编号）。
- 未支付订单总和计算 ：编写一个应用程序，连接到 CoffeeDB 数据库，计算并显示未支付订单的总金额。
- 未支付订单查询 ：编写一个应用程序，连接到 CoffeeDB 数据库，显示一个包含未支付订单客户的 JList 组件，用户点击客户时显示该客户的所有未支付订单摘要。
- 人口数据库操作 ：运行 CreateCityDB.java 程序创建 CityDB 数据库，编写一个程序连接到该数据库，允许用户进行城市排序、计算总人口、平均人口等操作。
- 人事数据库创建 ：编写一个应用程序，创建一个名为 Personnel 的数据库，包含一个 Employee 表，并插入至少五条示例数据。
- 员工信息插入 ：编写一个 GUI 应用程序，允许用户向 Personnel 数据库中添加新员工。
- 员工信息更新 ：编写一个 GUI 应用程序，允许用户查找并更新 Personnel 数据库中员工的信息（不包括员工编号）。
- 电话簿数据库操作 ：编写一个应用程序，创建一个名为 PhoneBook 的数据库，包含一个 Entries 表，允许用户添加、查找、更改和删除联系人信息。

2.6 操作流程总结

graph LR
    A[创建数据库连接] --> B[创建 Statement 对象]
    B --> C[执行 SQL 语句]
    C --> D[处理结果]
    D --> E[关闭 Statement 和 Connection 对象]

通过学习上述内容，我们可以更好地理解数据库操作和 Java 编程之间的关系，掌握常见的数据库操作和编程技巧，避免常见的错误，并通过编程挑战进一步巩固所学知识。

3. 数据库查询语句示例

3.1 示例表结构

假设存在一个名为 Stock 的表，其结构如下：
| 列名 | 类型 |
| ---- | ---- |
| TradingSymbol | CHAR(10) |
| CompanyName | CHAR(25) |
| NumShares | INT |
| PurchasePrice | DOUBLE |
| SellingPrice | DOUBLE |

3.2 具体查询语句

3.2.1 查询公司名为 “Pinewood” 的所有行

SELECT * FROM Stock WHERE CompanyName = 'Pinewood';

3.2.2 查询所有行的 TradingSymbol 列

SELECT TradingSymbol FROM Stock;

3.2.3 查询所有行的 TradingSymbol 列和 NumShares 列

SELECT TradingSymbol, NumShares FROM Stock;

3.2.4 更新公司名为 “CoffeeCo”

UPDATE Stock SET CompanyName = 'CoffeeCo';

3.2.5 查询 TradingSymbol 以 “SU” 开头的所有行

SELECT * FROM Stock WHERE TradingSymbol LIKE 'SU%';

3.2.6 查询 SellingPrice 大于 PurchasePrice 且 NumShares 大于 100 的 TradingSymbol 列

SELECT TradingSymbol FROM Stock WHERE SellingPrice > PurchasePrice AND NumShares > 100;

3.2.7 查询 SellingPrice 大于 PurchasePrice 且 NumShares 大于 100 的 TradingSymbol 列和 NumShares 列，并按 NumShares 升序排序

SELECT TradingSymbol, NumShares FROM Stock WHERE SellingPrice > PurchasePrice AND NumShares > 100 ORDER BY NumShares ASC;

3.2.8 向 Stock 表中插入新行

INSERT INTO Stock (TradingSymbol, CompanyName, NumShares, PurchasePrice, SellingPrice) VALUES ('XYZ', 'XYZ Company', 150, 12.55, 22.47);

3.2.9 将 TradingSymbol 为 “XYZ” 的行更新为 “ABC”

UPDATE Stock SET TradingSymbol = 'ABC' WHERE TradingSymbol = 'XYZ';

3.2.10 删除 SellingPrice 大于 25 的行

DELETE FROM Stock WHERE SellingPrice > 25;

3.3 查询语句总结

需求	查询语句
查询特定公司名的所有行	SELECT * FROM Stock WHERE CompanyName = 'Pinewood';
查询特定列	SELECT TradingSymbol FROM Stock;
查询多列	SELECT TradingSymbol, NumShares FROM Stock;
更新公司名	UPDATE Stock SET CompanyName = 'CoffeeCo';
查询以特定字符开头的行	SELECT * FROM Stock WHERE TradingSymbol LIKE 'SU%';
查询满足多条件的特定列	SELECT TradingSymbol FROM Stock WHERE SellingPrice > PurchasePrice AND NumShares > 100;
查询满足多条件的多列并排序	SELECT TradingSymbol, NumShares FROM Stock WHERE SellingPrice > PurchasePrice AND NumShares > 100 ORDER BY NumShares ASC;
插入新行	INSERT INTO Stock (TradingSymbol, CompanyName, NumShares, PurchasePrice, SellingPrice) VALUES ('XYZ', 'XYZ Company', 150, 12.55, 22.47);
更新特定行	UPDATE Stock SET TradingSymbol = 'ABC' WHERE TradingSymbol = 'XYZ';
删除满足条件的行	DELETE FROM Stock WHERE SellingPrice > 25;

3.4 查询操作流程

graph LR
    A[确定查询需求] --> B[编写 SQL 查询语句]
    B --> C[执行查询语句]
    C --> D[获取查询结果]
    D --> E[处理查询结果]

4. Java 与数据库连接及操作示例

4.1 连接数据库

以下是连接名为 EmployeeDB 的数据库的 Java 代码示例，其 URL 为 jdbc:derby:jdbc:EmployeeDB ：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        String DB_URL = "jdbc:derby:jdbc:EmployeeDB";
        try {
            Connection conn = DriverManager.getConnection(DB_URL);
            System.out.println("数据库连接成功！");
            conn.close();
        } catch (SQLException e) {
            System.out.println("数据库连接失败：" + e.getMessage());
        }
    }
}

4.2 执行查询语句

假设要执行 SELECT Name FROM Employee 语句，以下是 Java 代码示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class DatabaseQueryExample {
    public static void main(String[] args) {
        String DB_URL = "jdbc:derby:jdbc:EmployeeDB";
        try {
            Connection conn = DriverManager.getConnection(DB_URL);
            Statement stmt = conn.createStatement();
            String sql = "SELECT Name FROM Employee";
            ResultSet result = stmt.executeQuery(sql);
            while (result.next()) {
                String name = result.getString("Name");
                System.out.println("Name: " + name);
            }
            result.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("执行查询失败：" + e.getMessage());
        }
    }
}

4.3 Java 操作数据库流程

graph LR
    A[加载数据库驱动] --> B[建立数据库连接]
    B --> C[创建 Statement 对象]
    C --> D[执行 SQL 语句]
    D --> E{语句类型}
    E -- SELECT --> F[处理查询结果]
    E -- UPDATE/INSERT/DELETE --> G[获取受影响行数]
    F --> H[关闭结果集、Statement 和连接]
    G --> H

4.4 操作步骤总结

1. 加载数据库驱动 ：使用 Class.forName() 方法加载相应的 JDBC 驱动（某些数据库可省略此步骤）。
2. 建立数据库连接 ：使用 DriverManager.getConnection() 方法，传入数据库 URL 等信息。
3. 创建 Statement 对象 ：使用 Connection 对象的 createStatement() 方法。
4. 执行 SQL 语句 ：
   • 对于 SELECT 语句，使用 executeQuery() 方法。
   • 对于 UPDATE 、 INSERT 和 DELETE 语句，使用 executeUpdate() 方法。
5. 处理结果 ：
   • 对于查询结果，使用 ResultSet 对象遍历结果集。
   • 对于更新操作，获取受影响的行数。
6. 关闭资源 ：依次关闭 ResultSet 、 Statement 和 Connection 对象。

通过以上对数据库查询语句和 Java 与数据库连接操作的学习，我们可以更加熟练地进行数据库的操作和管理，同时利用 Java 语言实现对数据库的灵活交互。无论是简单的查询还是复杂的事务处理，都能有更深入的理解和实践。