Mysql-JDBC

Mysql-JDBC

概念

  • 作用
    • JDBC 提供了一种标准的方式，使 Java 应用程序能够与不同的数据库进行交互。
    • 它屏蔽了底层数据库的差异，开发者只需使用统一的 API 即可操作各种数据库
  • 组成
    • JDBC API：提供了一组接口和类，用于连接数据库、执行 SQL 语句和处理结果。
    • JDBC 驱动程序：由数据库厂商提供，用于实现 JDBC API 与具体数据库的通信

JDBC使用步骤

  1. 加载数据库驱动
  2. 建立数据库连接
  3. 创建 Statement 对象
  4. 执行 SQL 语句
  5. 处理结果
  6. 关闭资源

加载数据库驱动

  • 在 JDBC 4.0 之前，必须手动加载数据库驱动。通常使用 Class.forName() 方法加载驱动类

  • MySQL 的驱动类是 com.mysql.cj.jdbc.Driver。加载驱动的代码如下

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

  • 从 JDBC 4.0 开始，支持驱动的自动加载。只需将驱动 JAR 包添加到项目的类路径中，JDBC 会自动加载驱动。

    • JDBC 4.0 引入了 ServiceLoader 机制，驱动 JAR 包中的 META-INF/services/java.sql.Driver 文件指定了驱动类的全限定名

    • 当调用 DriverManager.getConnection() 时，JDBC 会自动加载并注册驱动

      public class AutoLoadDriverExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("数据库连接成功！");
        } catch (SQLException e) {
            System.out.println("数据库连接失败！");
            e.printStackTrace();
        }
    }
}

  • 注意事项

    • 确保驱动 JAR 包已添加到项目的类路径中
    • 不同数据库的驱动类名不同，确保使用正确的类名

建立数据库连接

  • 在建立连接之前，需要确保数据库驱动已加载（JDBC 4.0 及以上版本可以自动加载驱动）

  • 使用 DriverManager.getConnection() 方法建立连接。需要提供以下参数：

    • URL：数据库的连接 URL。

    • 用户名：数据库的用户名。

    • 密码：数据库的密码

      String url = "jdbc:mysql://localhost:3306/testdb";
String user = "root";
String password = "password";
Connection connection = DriverManager.getConnection(url, user, password);

    • URL可选参数

      • useSSL=false：禁用 SSL。

      • serverTimezone=UTC：设置服务器时区

      • String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC";

    • 还可以通过Properties 对象传递连接属性

      Properties properties = new Properties();
properties.setProperty("user", "root");
properties.setProperty("password", "password");
properties.setProperty("useSSL", "false");
properties.setProperty("serverTimezone", "UTC");
Connection connection = DriverManager.getConnection(url, properties);

  • 在建立连接时，可能会抛出 SQLException，需要进行异常处理。推荐使用 try-with-resources 语句确保连接被正确关闭

    public class ConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String user = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("数据库连接成功！");
        } catch (SQLException e) {
            System.out.println("数据库连接失败！");
            e.printStackTrace();
        }
    }
}

创建 Statement 对象

Statement用于执行SQL语句并返回执行结果，其中Statement、PreparedStatement 和 CallableStatement 是用于执行SQL语句的核心接口

  1. Statement

    • Statement 是 JDBC 中最基本的接口，用于执行静态的 SQL 语句（即不带参数的 SQL 语句）

    • 特点：

      • 适用于执行简单的、不带参数的 SQL 语句。
      • 每次执行 SQL 时，数据库都会对 SQL 语句进行编译和优化。
      • 容易受到 SQL 注入攻击，因为 SQL 语句是直接拼接的

    • 主要方法

      • executeQuery(String sql)

        • 用于执行查询语句（如 SELECT），返回一个 ResultSet 对象

          Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

        • executeUpdate(String sql)：

        • 用于执行更新语句（如 INSERT、UPDATE、DELETE），返回受影响的行数

          int rows = stmt.executeUpdate("UPDATE users SET name = 'John' WHERE id = 1");

        • execute(String sql)：

        • 用于执行任意 SQL 语句，返回一个布尔值，表示是否返回了 ResultSet

          boolean hasResultSet = stmt.execute("SELECT * FROM users");
if (hasResultSet) {
    ResultSet rs = stmt.getResultSet();
}

    • 使用场景

      • 执行简单的、静态的 SQL 语句。
      • 不需要多次执行的 SQL 语句

    • 缺点

      • SQL 语句是硬编码的，灵活性差。
      • 容易受到 SQL 注入攻击

  2. PreparedStatement

    • PreparedStatement 是 Statement 的子接口，用于执行预编译的 SQL 语句。它支持带参数的 SQL 语句，可以有效防止 SQL 注入

    • 特点：

      • SQL 语句是预编译的，性能更高（尤其是多次执行相同 SQL 时）。
      • 支持参数化查询，通过占位符（?）动态设置参数。
      • 防止 SQL 注入攻击，因为参数值会被自动转义

    • 主要方法

      • setXxx(int parameterIndex, Xxx value)

        • 用于设置 SQL 语句中的参数值，Xxx 表示数据类型（如 setInt、setString 等）

          PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM users WHERE id = ?");
pstmt.setInt(1, 1); // 设置第一个参数为 1
ResultSet rs = pstmt.executeQuery();

      • executeQuery()，用于执行查询语句，返回 ResultSet

      • executeUpdate()，用于执行更新语句，返回受影响的行数

      • execute()：用于执行任意 SQL 语句，返回布尔值

    • 适用场景

      • 执行带参数的 SQL 语句。
      • 需要多次执行的 SQL 语句。
      • 需要防止 SQL 注入的场景

  3. 批量处理

    • 批量处理（Batch Processing）是一种优化技术，允许一次性发送多个SQL语句到数据库执行。这种方式可以减少网络通信开销，提高数据库操作的性能，特别适用于需要执行大量SQL语句的场景

    • 批量处理的核心思想是将多个SQL语句打包成一个批次，一次性发送到数据库执行，而不是逐条发送。这样可以减少与数据库的交互次数，从而提高性能

    • 实现方式

      • Statement 批量处理：适用于静态SQL语句（无参数）。
      • PreparedStatement 批量处理：适用于动态SQL语句（带参数），性能更好

    • Statement 的批量处理过程

      • 创建 Statement 对象

      • 使用 addBatch() 方法将SQL语句添加到批处理中

      • 使用 executeBatch() 方法执行批处理中的所有SQL语句

        • executeBatch() 方法返回一个 int[] 数组，数组中的每个元素表示对应SQL语句的执行结果（通常是受影响的行数）
        • JDBC 规范规定，批处理不能用于执行 SELECT 语句
        • 批处理的 executeBatch() 方法返回一个 int[] 数组,SELECT 语句不返回更新计数，而是返回结果集（ResultSet），这与批处理的返回类型不匹配
        • 批处理的目的是优化批量写操作（如插入、更新、删除），而不是用于查询操作

      • 如果需要重新使用 Statement 对象进行新的批处理，可以调用 clearBatch() 方法清除之前的批处理

        • 批处理中的 SQL 语句会一直保留在批处理列表中，直到被显式清除。如果不调用 clearBatch()，再次执行 executeBatch() 时，这些语句会被重复执行，可能导致数据重复插入或更新
        try (Connection connection = DriverManager.getConnection(url, username, password);
     Statement stmt = connection.createStatement()) {

    // 添加SQL语句到批处理
    stmt.addBatch("INSERT INTO employees (id, name, age) VALUES (1, 'John', 30)");
    stmt.addBatch("INSERT INTO employees (id, name, age) VALUES (2, 'Jane', 25)");
    stmt.addBatch("UPDATE employees SET age = 31 WHERE id = 1");

    // 执行批处理
    int[] updateCounts = stmt.executeBatch();

    // 输出执行结果
    for (int count : updateCounts) {
        System.out.println("Affected rows: " + count);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

    • PreparedStatement 的批量处理

      • PreparedStatement 是 Statement 的子接口，支持预编译SQL语句，通常用于执行带参数的SQL语句。PreparedStatement 也支持批量处理，并且由于SQL语句是预编译的，性能通常比 Statement 更好

      • 使用步骤

        • 创建 PreparedStatement 对象

        • 使用 setXxx() 方法设置参数，然后调用 addBatch() 方法将SQL语句添加到批处理中

        • 使用 executeBatch() 方法执行批处理中的所有SQL语句

        • executeBatch() 方法返回一个 int[] 数组，表示每个SQL语句的执行结果

        • 如果需要重新使用 PreparedStatement 对象进行新的批处理，可以调用 clearBatch() 方法清除之前的批处理

          try (Connection connection = DriverManager.getConnection(url, username, password);
     PreparedStatement pstmt = connection.prepareStatement("INSERT INTO employees (id, name, age) VALUES (?, ?, ?)")) {

    // 设置参数并添加到批处理
    pstmt.setInt(1, 1);
    pstmt.setString(2, "John");
    pstmt.setInt(3, 30);
    pstmt.addBatch();

    pstmt.setInt(1, 2);
    pstmt.setString(2, "Jane");
    pstmt.setInt(3, 25);
    pstmt.addBatch();

    // 执行批处理
    int[] updateCounts = pstmt.executeBatch();

    // 输出执行结果
    for (int count : updateCounts) {
        System.out.println("Affected rows: " + count);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

    • 批量处理的注意事项

      • 批量处理通常与事务一起使用。在执行批处理之前，可以关闭自动提交，在批处理执行成功后再手动提交事务
      • 批处理的大小（即一次执行的SQL语句数量）应根据数据库和网络性能进行调整。过大的批处理可能会导致内存不足或数据库性能下降
      • 如果批处理中的某个SQL语句执行失败，executeBatch() 方法可能会抛出 BatchUpdateException。可以通过捕获该异常并检查 getUpdateCounts() 方法来处理部分成功的批处理
      • 并非所有数据库都支持批量处理，或者支持的实现方式可能不同。在使用批量处理之前，建议查阅数据库的文档

  4. CallableStatement

    • CallableStatement 是 PreparedStatement 的子接口，用于调用数据库中的存储过程或函数
    • 特点
      • 支持调用数据库中的存储过程和函数。
      • 支持输入参数、输出参数和输入输出参数。
      • 使用 {call procedure_name(?, ?)} 或 {? = call function_name(?, ?)} 语法调用存储过程或函数

获取执行结果

  1. 查询操作（SELECT）

    • 使用 Statement 或 PreparedStatement 的 executeQuery() 方法执行查询。

    • 通过 ResultSet 的方法（如 next()、getXxx()）遍历结果集并获取数据

    • ResultSet 常用方法

      • next()：

        • 将游标移动到下一行，如果有数据则返回 true，否则返回 false。

      • getXxx(int columnIndex)：

        • 根据列的索引（从 1 开始）获取数据，如 getInt(1)。

      • getXxx(String columnName)：

        • 根据列名获取数据，如 getString("name")。

      • close()：

        • 关闭 ResultSet，释放资源
        String sql = "SELECT id, name, age FROM users";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {
    int id = rs.getInt("id");         // 通过列名获取数据
    String name = rs.getString("name");
    int age = rs.getInt("age");
    System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}

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

  2. 更新操作（INSERT、UPDATE、DELETE）

    • 执行更新操作后，返回的结果是一个整数，表示受影响的行数
    • 使用 Statement 或 PreparedStatement 的 executeUpdate() 方法执行更新操作。
    • 通过返回值判断操作是否成功以及影响了多少行数据

  3. 批量操作

    • 执行批量操作后，返回的结果是一个整数数组，表示每条 SQL 语句执行后受影响的行数
    • 使用 Statement 或 PreparedStatement 的 addBatch() 方法添加 SQL 语句。
    • 使用 executeBatch() 方法执行批量操作。
    • 通过返回值数组判断每条 SQL 语句的执行结果

  4. 通用操作（execute() 方法）

    • execute()方法可以执行任意 SQL 语句，返回一个布尔值，表示是否返回了 ResultSet
    • 使用 Statement 或 PreparedStatement 的 execute() 方法执行 SQL 语句。
    • 根据返回值判断是查询操作还是更新操作。
    • 如果是查询操作，通过 getResultSet() 获取结果集；如果是更新操作，通过 getUpdateCount() 获取受影响的行数
    String sql = "SELECT * FROM users";
Statement stmt = connection.createStatement();
boolean hasResultSet = stmt.execute(sql);

if (hasResultSet) {
    ResultSet rs = stmt.getResultSet();
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
    rs.close();
} else {
    int rows = stmt.getUpdateCount();
    System.out.println("受影响的行数: " + rows);
}

stmt.close();

结果集result的注意事项

  1. 资源管理

    • 必须关闭 ResultSet：

    • ResultSet 是数据库资源的一部分，使用完毕后必须关闭，否则会导致资源泄漏。

    • 推荐使用 try-with-resources 语法，确保资源自动关闭。

    • 关闭顺序：

      • 关闭顺序应为：ResultSet → Statement → Connection
      try (Connection connection = dataSource.getConnection();
     Statement stmt = connection.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
    // 处理结果集
} catch (SQLException e) {
    e.printStackTrace();
}

  2. result结果集问题

    • 使用result结果集要求必须连接数据库，不能释放数据库资源
    • 直接在 ResultSet 中处理数据会导致业务逻辑与数据访问逻辑耦合，降低代码的可读性和可维护性
    • 结果集只能使用一次，不利于数据管理

  3. 替代方案

    • 使用 ORM（对象关系映射）框架（如 Hibernate、MyBatis）可以将查询结果直接映射为 Java 对象，简化数据访问逻辑
    • ResultSet底层将记录保存到ArrayList集合中，将 ResultSet 中的数据提取到 List 或 Map 中，便于后续处理和复用

DBUtils

Apache Commons DbUtils 是一个轻量级的 JDBC 工具库，旨在简化 JDBC 编程，减少样板代码，提高开发效率。

使用流程

  1. 添加依赖

  2. 核心类

    • QueryRunner
      • 用于执行 SQL 查询和更新操作。
      • 支持普通 SQL 和预编译 SQL。
      • 提供多种方法，如 query()、update()、insert() 等
    • ResultSetHandler
      • 用于将 ResultSet 映射为 Java 对象或集合
      • 实现类（封装的范围）
        • BeanHandler：将结果集的第一行映射为 Java Bean。
        • BeanListHandler：将结果集的每一行映射为 Java Bean，并返回 List。
        • MapHandler：将结果集的第一行映射为 Map。
        • MapListHandler：将结果集的每一行映射为 Map，并返回 List。
        • ScalarHandler：将结果集的第一行第一列映射为单个对象（如 Integer、String）

  3. 初始化QueryRunner

    • QueryRunner 可以通过 DataSource 或 Connection 初始化

      • 如果不传递 DataSource，需要在每次执行 SQL 时手动传递 Connection 对象
      • 如果传递 DataSource，QueryRunner 会自动管理 Connection 的获取和释放
      QueryRunner queryRunner = new QueryRunner();

DataSource dataSource = // 获取数据源（如 HikariCP、DBCP 等）
QueryRunner queryRunner = new QueryRunner(dataSource);

  4. 使用QueryRunner执行SQL语句

    • QueryRunner 提供了 query() 方法用于执行查询操作，并将结果集映射为 Java 对象或集合

      • 查询单行数据（BeanHandler）
      • 查询多行数据（BeanListHandler）
      • 查询单列数据（ScalarHandler）
      public User getUserById(int id) throws SQLException {
    String sql = "SELECT id, name, age FROM users WHERE id = ?";
    return queryRunner.query(sql, new BeanHandler<>(User.class), id);
}

public List<User> getAllUsers() throws SQLException {
    String sql = "SELECT id, name, age FROM users";
    return queryRunner.query(sql, new BeanListHandler<>(User.class));
}

public int getUserIdByName(String name) throws SQLException {
    String sql = "SELECT id FROM users WHERE name = ?";
    return queryRunner.query(sql, new ScalarHandler<>(), name);
}

    • QueryRunner 提供了 update() 方法用于执行更新操作（如 INSERT、UPDATE、DELETE）

      public int addUser(User user) throws SQLException {
    String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
    return queryRunner.update(sql, user.getName(), user.getAge());
}

public int updateUser(User user) throws SQLException {
    String sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
    return queryRunner.update(sql, user.getName(), user.getAge(), user.getId());
}

public int deleteUser(int id) throws SQLException {
    String sql = "DELETE FROM users WHERE id = ?";
    return queryRunner.update(sql, id);
}

  5. 批量操作

    • QueryRunner 提供了 batch() 方法用于执行批量操作

    • 批量插入

      public int[] addUsers(List<User> users) throws SQLException {
    String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
    Object[][] params = new Object[users.size()][2];
    for (int i = 0; i < users.size(); i++) {
        params[i][0] = users.get(i).getName();
        params[i][1] = users.get(i).getAge();
    }
    return queryRunner.batch(sql, params);
}

    • 批量更新

      public int[] updateUsers(List<User> users) throws SQLException {
    String sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
    Object[][] params = new Object[users.size()][3];
    for (int i = 0; i < users.size(); i++) {
        params[i][0] = users.get(i).getName();
        params[i][1] = users.get(i).getAge();
        params[i][2] = users.get(i).getId();
    }
    return queryRunner.batch(sql, params);
}

  6. 事务管理

    • QueryRunner 支持手动管理事务，通过 Connection 控制事务的提交和回滚

    • 数据库连接池（如 HikariCP、DBCP 等）主要用于管理数据库连接的获取和释放，而不是直接控制事务。事务管理仍然需要通过 Connection 对象来手动控制

      public void transferMoney(int fromId, int toId, double amount) throws SQLException {
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        connection.setAutoCommit(false); // 开启事务

        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection, "UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromId);
        queryRunner.update(connection, "UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toId);

        connection.commit(); // 提交事务
    } catch (SQLException e) {
        DbUtils.rollback(connection); // 回滚事务
        throw e;
    } finally {
        DbUtils.closeQuietly(connection); // 关闭连接
    }
}
posted @ 2025-03-17 21:47  QAQ001  阅读(77)  评论(0)    收藏  举报