JDBC 入门

baeldung 2024-01-08

1. 概述

在本文中，我们将介绍 JDBC（Java Database Connectivity） —— 这是一套用于连接数据库并执行查询的 Java API。

只要提供了合适的驱动程序，JDBC 就可以与任何数据库协同工作。

2. JDBC 驱动程序

JDBC 驱动程序是 JDBC API 的一种实现，用于连接特定类型的数据库。JDBC 驱动程序主要分为以下几类：

• 类型 1（Type 1）：将 JDBC 调用映射到另一种数据访问 API；例如 JDBC-ODBC 桥接驱动。
• 类型 2（Type 2）：使用目标数据库的客户端本地库（native libraries）；也称为“本地 API 驱动”。
• 类型 3（Type 3）：通过中间件将 JDBC 调用转换为数据库特定的调用；也称为“网络协议驱动”。
• 类型 4（Type 4）：直接连接数据库，将 JDBC 调用转换为数据库特定协议；也称为“数据库协议驱动”或“瘦驱动（thin driver）”。

目前最常用的是 类型 4 驱动，因为它具有平台无关性，并且由于直接连接数据库服务器，性能优于其他类型。但其缺点是数据库特定——因为每种数据库都有自己的通信协议。

3. 连接数据库

要连接数据库，我们只需初始化驱动程序并打开数据库连接。

3.1 注册驱动程序

在本例中，我们将使用 类型 4 的数据库协议驱动。

由于我们使用的是 MySQL 数据库，因此需要添加 mysql-connector-java 依赖项：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.32</version>
</dependency>

接着，我们可以使用 Class.forName() 方法动态加载驱动类：

Class.forName("com.mysql.cj.jdbc.Driver");

注意：在 JDBC 4.0 之前，必须显式调用 Class.forName() 来加载驱动。但从 JDBC 4.0 开始，只要驱动 JAR 包位于 classpath 中，驱动就会被自动加载。因此，在现代开发环境中通常不再需要手动调用 Class.forName()。

3.2 创建连接

我们可以使用 DriverManager.getConnection() 方法来建立数据库连接，该方法需要一个连接 URL 字符串作为参数：

try (Connection con = DriverManager
        .getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass")) {
    // 在此处使用 con
}

由于 Connection 实现了 AutoCloseable 接口，建议将其放在 try-with-resources 语句块中，以确保资源被自动释放。

连接 URL 的格式取决于所使用的数据库类型。以下是几个示例：

jdbc:mysql://localhost:3306/myDb?user=user1&password=pass
jdbc:postgresql://localhost/myDb
jdbc:hsqldb:mem:myDb

为了成功连接到名为 myDb 的数据库，我们需要先在数据库中创建该数据库、用户，并授予必要的权限：

CREATE DATABASE myDb;
CREATE USER 'user1' IDENTIFIED BY 'pass';
GRANT ALL ON myDb.* TO 'user1';

4. 执行 SQL 语句

要向数据库发送 SQL 指令，我们可以使用 Statement、PreparedStatement 或 CallableStatement 的实例，这些对象均可通过 Connection 对象获取。

4.1 Statement

Statement 接口包含执行 SQL 命令的基本方法。

首先，创建一个 Statement 对象：

try (Statement stmt = con.createStatement()) {
    // 在此处使用 stmt
}

同样，应使用 try-with-resources 语句块以确保资源自动关闭。

执行 SQL 指令可通过以下三种方法完成：

• executeQuery()：用于 SELECT 查询。
• executeUpdate()：用于更新数据或数据库结构（如 INSERT、UPDATE、DELETE、CREATE 等）。
• execute()：当不确定 SQL 语句返回结果类型时使用（可处理上述两种情况）。

例如，使用 execute() 方法创建一个 employees 表：

String tableSql = "CREATE TABLE IF NOT EXISTS employees" 
  + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30),"
  + "position varchar(30), salary double)";
stmt.execute(tableSql);

• 若使用 execute() 执行更新操作，可通过 stmt.getUpdateCount() 获取受影响的行数：
  • 返回 0：表示未影响任何行，或执行的是数据库结构变更命令。
  • 返回 -1：表示执行的是 SELECT 查询，此时可通过 stmt.getResultSet() 获取结果集。

接下来，使用 executeUpdate() 插入一条记录：

String insertSql = "INSERT INTO employees(name, position, salary)"
  + " VALUES('john', 'developer', 2000)";
stmt.executeUpdate(insertSql);

该方法返回受影响的行数（对于结构变更命令返回 0）。

要查询表中的记录，可使用 executeQuery()，它会返回一个 ResultSet 对象：

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    // 在此处使用 resultSet
}

务必在使用完毕后关闭 ResultSet，否则可能导致底层游标长时间未释放。推荐使用 try-with-resources 语句块自动管理资源。

4.2 PreparedStatement

PreparedStatement 对象包含预编译的 SQL 语句，支持使用问号（?）作为参数占位符。

例如，创建一个用于根据参数更新员工职位的 PreparedStatement：

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) {
    // 在此处使用 pstmt
}

通过 setX() 方法（X 为参数类型）设置参数值，第一个参数为占位符位置（从 1 开始），第二个为值：

pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

执行语句时，使用与 Statement 相同的三个方法（executeQuery()、executeUpdate()、execute()），但无需传入 SQL 字符串：

int rowsAffected = pstmt.executeUpdate();

还可以为 PreparedStatement 设置查询超时时间，防止长时间运行的查询阻塞系统：

String longRunningQuery = "SELECT SLEEP(15)"; // 模拟长时间查询
assertThrows(SQLException.class, () -> {
    try (PreparedStatement pstmt = con.prepareStatement(longRunningQuery)) {
        pstmt.setQueryTimeout(5); // 设置超时为 5 秒
        pstmt.executeQuery();
    }
});

若查询超过指定时间，将抛出 SQLException 并自动取消执行。

4.3 CallableStatement

CallableStatement 接口用于调用数据库中的存储过程。

通过 Connection.prepareCall() 创建 CallableStatement：

String preparedSql = "{call insertEmployee(?,?,?,?)}";
try (CallableStatement cstmt = con.prepareCall(preparedSql)) {
    // 在此处使用 cstmt
}

输入参数的设置方式与 PreparedStatement 相同：

cstmt.setString(2, "ana");
cstmt.setString(3, "tester");
cstmt.setDouble(4, 2000);

若存储过程包含输出参数，需先使用 registerOutParameter() 注册：

cstmt.registerOutParameter(1, Types.INTEGER);

执行后，可通过对应的 getX() 方法获取输出值：

cstmt.execute();
int new_id = cstmt.getInt(1);

示例前提：需在 MySQL 中创建如下存储过程：

DELIMITER //
CREATE PROCEDURE insertEmployee(
    OUT emp_id INT, 
    IN emp_name VARCHAR(30), 
    IN position VARCHAR(30), 
    IN salary DOUBLE
) 
BEGIN
    INSERT INTO employees(name, position, salary) 
    VALUES (emp_name, position, salary);
    SET emp_id = LAST_INSERT_ID();
END //
DELIMITER ;

该过程会插入新员工记录，并通过 emp_id 输出新记录的主键 ID。

权限说明：为使 Java 能调用存储过程，数据库用户需有访问存储过程元数据的权限。可执行以下授权：

GRANT ALL ON mysql.proc TO 'user1';

或者，在连接字符串中添加参数 noAccessToProcedureBodies=true，告知 JDBC 驱动用户无权读取过程体，此时所有参数将被视为 INOUT String 类型：

con = DriverManager.getConnection(
  "jdbc:mysql://localhost:3306/myDb?noAccessToProcedureBodies=true", 
  "user1", "pass");

5. 解析查询结果

执行查询后，结果由 ResultSet 对象表示，其结构类似于表格，包含行和列。

5.1 ResultSet 接口

ResultSet 使用 next() 方法逐行移动游标。

首先定义一个 Employee 类用于封装记录：

public class Employee {
    private int id;
    private String name;
    private String position;
    private double salary;
    
    // 标准构造函数、getter 和 setter
}

然后遍历 ResultSet，为每条记录创建 Employee 对象：

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    List<Employee> employees = new ArrayList<>(); 
    while (resultSet.next()) { 
        Employee emp = new Employee(); 
        emp.setId(resultSet.getInt("emp_id")); 
        emp.setName(resultSet.getString("name")); 
        emp.setPosition(resultSet.getString("position")); 
        emp.setSalary(resultSet.getDouble("salary")); 
        employees.add(emp); 
    }
}

通过 getX() 方法（X 为数据类型）获取单元格值，可使用列索引（从 1 开始）或列名作为参数。推荐使用列名，以避免因查询列顺序变化而导致错误。

5.2 可更新的 ResultSet

默认情况下，ResultSet 只能向前遍历且不可修改。

若需双向遍历或更新数据，需在创建 Statement 时指定额外参数：

stmt = con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_UPDATABLE
);

支持的导航方法包括：

• first(), last(), beforeFirst(), beforeLast()：跳转到首/末行或其前一行。
• next(), previous()：前后移动。
• getRow()：获取当前行号。
• absolute(int row)：跳转到指定行。
• relative(int nrRows)：相对移动若干行。
• moveToInsertRow(), moveToCurrentRow()：移动到插入行或返回当前行。

更新操作使用 updateX() 方法（仅修改 ResultSet 内存中的数据），需调用以下方法同步到数据库：

• updateRow()：提交当前行修改。
• insertRow() / deleteRow()：插入新行或删除当前行。
• refreshRow()：从数据库刷新当前行。
• cancelRowUpdates()：取消当前行的修改。

示例：插入新员工记录：

try (Statement updatableStmt = con.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
    try (ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql)) {
        updatableResultSet.moveToInsertRow();
        updatableResultSet.updateString("name", "mark");
        updatableResultSet.updateString("position", "analyst");
        updatableResultSet.updateDouble("salary", 2000);
        updatableResultSet.insertRow();
    }
}

6. 解析元数据

JDBC API 支持获取数据库的元数据信息。

6.1 DatabaseMetaData

通过 DatabaseMetaData 可获取数据库的通用信息，如表、存储过程、SQL 方言等。

例如，列出所有表名：

DatabaseMetaData dbmd = con.getMetaData();
ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null);
while (tablesResultSet.next()) {
    LOG.info(tablesResultSet.getString("TABLE_NAME"));
}

6.2 ResultSetMetaData

通过 ResultSetMetaData 可获取特定 ResultSet 的结构信息，如列数、列名等：

ResultSetMetaData rsmd = rs.getMetaData();
int nrColumns = rsmd.getColumnCount();

IntStream.range(1, nrColumns + 1).forEach(i -> {
    try {
        LOG.info(rsmd.getColumnName(i));
    } catch (SQLException e) {
        e.printStackTrace();
    }
});

注意：列索引从 1 开始，因此循环应为 1 到 nrColumns（含）。

7. 事务处理

默认情况下，每条 SQL 语句执行后会自动提交。但也可以通过编程方式控制事务，以保证数据一致性（例如：只有当多个操作全部成功时才提交）。

首先关闭自动提交，然后使用 commit() 和 rollback() 控制事务：

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?";
PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql);
pstmt2.setDouble(1, 3000); // 注意：此处应使用 pstmt2
pstmt2.setInt(2, 1);

boolean autoCommit = con.getAutoCommit();
try {
    con.setAutoCommit(false);
    pstmt.executeUpdate();
    pstmt2.executeUpdate();
    con.commit();
} catch (SQLException exc) {
    con.rollback();
} finally {
    con.setAutoCommit(autoCommit);
}

注意：为简洁起见，此处省略了 try-with-resources 块，实际开发中仍应使用。

8. 关闭资源

使用完毕后，应关闭连接以释放数据库资源：

con.close();

但若使用了 try-with-resources 语句块，则无需显式调用 close()，系统会自动关闭 Connection、Statement、PreparedStatement、CallableStatement 和 ResultSet 等资源。

9. 结论

本文介绍了使用 JDBC API 的基础知识，包括驱动类型、连接数据库、执行各类 SQL 语句、处理结果集、元数据查询以及事务控制等内容。掌握这些核心概念，即可在 Java 应用中高效地与关系型数据库交互。