JDBC（todo）

1、JDBC

MySQL 知识

1.1、Java 数据库连接

Java 数据库连接（Java DataBase Connectivity，简称 JDBC）

使用 Java 语言操作关系型数据库的 API。

  • 每个关系型数据库的底层实现细节都不一样。

  • SUN 公司定义了标准接口（JDBC），定义了所有操作关系型数据库的规则。

  • 数据库厂商负责实现接口，创建实现类（即数据库驱动）。

    image-20210725130537815

好处

  • 各数据库厂商使用相同的接口，Java 代码不需要针对不同数据库分别开发（面向接口编程）
  • 可随时替换底层数据库（导入相应的驱动包），访问数据库的 Java 代码基本不变（策略模式）

1.2、JDBC 使用

1.2.1、开发步骤

注：MySQL 8 以上需要设置时区

  1. 导入驱动：JAR 包 / Maven 依赖

  2. 注册驱动：Driver

  3. 获取数据库连接：URL、用户名、密码

  4. 定义 SQL 语句

  5. 获取 SQL 执行对象：Statement

  6. 执行 SQL

  7. 处理返回结果：受影响行数 / 结果集

  8. 释放资源

1.2.2、代码示例

注：使用 MySQL 8

  1. 注册驱动

  2. 获取连接

  3. 获取 SQL 执行对象

  4. 执行 SQL

  5. 处理返回结果

    public static void query(String sql) throws ClassNotFoundException, SQLException {
    // 驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 连接
    String url = "jdbc:mysql://主机号:端口号/数据库名?参数&参数";
    String username = "用户名";
    String password = "密码";
    Connection connection = DriverManager.getConnection(url, username, password);
    // SQL执行对象
    Statement statement = connection.createStatement();
    // 处理返回结果
    ResultSet resultSet = statement.executeQuery(sql);

    while (resultSet.next()) {
        System.out.print(resultSet.getString("name") + ": ");
        System.out.println(resultSet.getString("phone"));
    }
    // 释放资源
    statement.close();
    connection.close();
}

2、JDBC API

2.1、DriverManager

驱动管理

常用方法

  • 注册驱动
  • 获取数据库连接

2.1.1、注册驱动

Driver

  • 在 1.2.2 代码示例中，通过反射加载 Driver 类，而不是调用 registerDriver()。

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

  • 查看 Driver 类：本质上也是调用 registerDriver()

    • 无参构造：用于反射机制创建实例

    • 静态代码块：注册驱动

      public class Driver extends NonRegisteringDriver
    implements java.sql.Driver {
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

    public Driver() throws SQLException {
    }
}

说明

  • 使用 MySQL 5 之后的驱动包，可省略注册驱动的步骤。

  • 自动加载 JAR 包中 META-INF/services/java.sql.Driver 文件中的驱动类。

    • MySQL 8 之前：com.mysql.jdbc.Driver

    • MySQL 8 开始：com.mysql.cj.jdbc.Driver

      image-20220401171749104

2.1.2、获取数据库连接

常用方式

  • 通过 URL 和配置文件

  • 通过 URL 和用户名密码（方法内部也是创建配置文件，再调用重载方法）

    @CallerSensitive
public static Connection getConnection(String url,
                                       java.util.Properties info)
    throws SQLException {...}

@CallerSensitive
public static Connection getConnection(String url,
                                       String user, String password)
    throws SQLException {...}

参数说明

  • url ： jdbc:mysql://主机号:端口号/数据库名称?参数&参数
  • user ：用户名
  • password ：密码

2.2、Connection

数据库连接对象

常用方法

  • 获取 SQL 执行对象
  • 事务管理

2.2.1、获取执行对象

下一节具体介绍 SQL 执行对象

  • 普通 SQL 执行对象

  • 预编译 SQL 执行对象

  • 存储过程执行对象（不常用）

    Statement createStatement() throws SQLException;
PreparedStatement prepareStatement(String sql) throws SQLException;
CallableStatement prepareCall(sql)

2.2.2、事务管理

  • 事务操作
    • 控制事务 ：BEGIN 或 START TRANSACTION
    • 提交事务 ：COMMIT
    • 回滚事务 ：ROLLBACK
  • MySQL 默认自动提交事务：每执行一条 DML 语句，MySQL 立即将事务提交到数据库。

对应 Connection API 方法

  • 查看、设置自动提交状态

  • 提交事务

  • 回滚事务

    boolean getAutoCommit() throws SQLException;
void setAutoCommit(boolean autoCommit) throws SQLException;

void commit() throws SQLException;

void rollback() throws SQLException;

代码示例

SQL 可能发生运行时异常（而非编译期异常），通过 try-catch 捕获。

  1. 开启事务。

  2. 执行 SQL。

  3. 若无异常，则正常提交事务。

  4. 若发生异常，则捕获并回滚事务。

    public static void update(String sql1, String sql2) throws SQLException {
    // 省略：注册驱动、获取连接、获取SQL执行对象
    try {
        // 开启事务
        if (connection.getAutoCommit()) {
            connection.setAutoCommit(false);
        }
        // 执行SQL
        int count1 = statement.executeUpdate(sql1);
        // 此处可模拟异常，如int i = 1/0;
        int count2 = statement.executeUpdate(sql2);
        // 提交事务
        connection.commit();
    } catch (Exception e) {
        // 回滚事务
        connection.rollback();
        e.printStackTrace();
    }
    // 释放资源
}

2.3、Statement

SQL 执行对象

  • 作用：执行 SQL 语句
  • 通常只会执行 DML 和 DQL，即增删改查操作。

常用方法

  • executeUpdate()：执行 DDL、DML，返回受影响行数。
  • executeQuery()：执行 DQL 语句，返回 ResultSet 对象。

2.4、ResultSet

结果集：封装 DQL 返回的结果。

2.4.1、常用方法

boolean next() throws SQLException;

Xxx getXxx(int columnIndex) throws SQLException;
Xxx getXxx(String columnLabel) throws SQLException;
  • next()：将游标向后移动一行，若为有效行则返回 true
  • getXxx()：获取当前行的指定列数据（Xxx 表示数据类型）
    • 两个重载方法
      • int columnIndex：根据列编号获取。
      • String columnLabel：根据列名获取。
    • 要求：指定列的数据类型，与方法获取的数据类型匹配
      （举例如下）
      • 类型一致：属性列为 varchar，则使用 getString()
      • 自动转换：属性列为数字，则使用 getInt() 或 getString()

2.4.2、示例

  1. 执行 DQL，获取结果集。

  2. 遍历结果集，取出每行的指定列的元素。

    ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {
    // 获取第1列（主键）
    System.out.print(resultSet.getInt(1));
    // 获取名为name的字段
    System.out.println(resultSet.getString("name"));
}

2.5、PreparedStatement

预编译 SQL 执行对象

作用：预编译 SQL 语句并执行，预防 SQL 注入问题。

2.5.1、SQL 注入

Web 攻击技术

通过特殊字符对 SQL 语句进行转义，从而非法运行 SQL。

模拟登录场景：接收客户端参数，拼接字符串作为 SQL

// 仅展示关键代码
public void login(String name, String password) throws SQLException {
    // 拼接字符串
    String sql = "SELECT * FROM user "
        + "WHERE name = '" + name
        + "' AND password = '" + password + "'";

    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    
    while (resultSet.next()) {
        System.out.print(resultSet.getString("name") + ": ");
        System.out.println(resultSet.getString("password"));
    }
}

  1. 正常情况：客户端正常输入用户名和密码（输入正确则打印信息），对应 SQL 语句如下

    # 输入用户名【jaywee】、密码【123456】
SELECT * FROM user
WHERE name = 'jaywee' AND password = '123456'

  2. SQL 注入：客户端输入特殊字符，对应 SQL 语句如下

    • AND 语句比 OR 语句优先执行

      # 输入用户名【jaywee】，密码【'or '1' = '1】
SELECT * FROM user
WHERE name = 'jaywee' AND password = '' OR '1' = '1'

    • '1' = '1' 恒成立，等价于以下 SQL，强行进行了非法查询。

      SELECT * FROM user WHERE true;
# 即
SELECT * FROM user;

2.5.2、PreparedStatement

预编译 SQL 执行对象

作用：预编译 SQL 语句并执行，预防 SQL 注入问题。

  • 获取 PreparedStatement：通过 Connection

    PreparedStatement prepareStatement(String sql)
    throws SQLException;

  • 常用方法

    • 设置参数：通过参数下标设置（从 1 开始）

    • 执行 SQL：执行时不需要传入 SQL，因为已预编译

      void setXxx(int parameterIndex, Xxx xxx);

ResultSet executeQuery() throws SQLException;
int executeUpdate() throws SQLException;

对比

  • 使用 Statement

    // 拼接SQL
String sql = "";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);

  • 使用 PreparedStatement

    // 占位符SQL
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
ps.setXxx()

2.5.3、示例

登录场景：

// 仅展示关键代码
public void login(String name, String password) throws SQLException {
    // 占位符
    String sql = "SELECT * FROM user WHERE name = ? AND password = ?";
    // SQL执行对象
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, name);
    statement.setString(2, password);
	// 执行
    ResultSet resultSet = statement.executeQuery(sql);

    while (resultSet.next()) {
        System.out.print(resultSet.getString("name") + ": ");
        System.out.println(resultSet.getString("password"));
    }
}

防止 SQL 注入：PreparedStatement 会将特殊字符进行转义

# 输入用户名【jaywee】，密码【'or '1' = '1】
SELECT * FROM user
WHERE name = 'jaywee' AND password = '\' OR \'1\' = \'1'

2.5.4、原理

Java 操作数据库的流程

  1. 编写 Java 代码（获取 statement）

  2. 将 SQL 发送到 MySQL 服务端（statement.executeXxx()）

  3. MySQL 服务端的操作

    1. 检查 SQL：语法正确性

    2. 编译 SQL：编译成可执行的函数

    3. 执行 SQL 语句，将结果返回给 Java。

      image-20220401232556550

说明

  • 使用 Statement：每次执行 SQL 都需经历以上步骤。
  • 使用 PreparedStatement
    • 获取 PreparedStatement 时，Java 就将 SQL 发送到 MySQL 服务端进行检查和编译。
    • 执行 SQL 时，MySQL 服务端直接执行 SQL 语句，无需再次检查和编译。

3、数据库连接池

3.1、简介

数据库连接池：负责分配、管理数据库连接（Connection）的容器

允许用户重复使用现有的数据库连接，而不是每次都建立和释放连接。

  • 资源重用：重复使用现有的数据库连接。
  • 提升系统响应速度：节省频繁建立和释放连接的时间。
  • 避免数据库连接遗漏：若某个 Connection 长时间处于空闲状态，连接池会将其释放

说明

  • 没有使用连接池

    • 用户每次需要使用连接时都需要创建一个 Connection 对象，使用完毕后释放。
    • 特点：重复创建和释放连接资源，耗费 CPU 性能。

  • 使用数据库连接池

    • 预先创建并存储 Connection 对象。

    • 用户需要连接数据库时，从连接池中获取 Connection，使用完毕后归还给连接池。

    • 特点：资源重用，节省频繁创建销毁连接所花费的时间，提升系统响应速度。

      image-20210725210432985

3.2、DataSource

数据源：DataSource

  • SUN 提供的数据库连接池标准接口，定义了获取 Connection 的功能。

    Connection getConnection()

  • 由第三方组织实现此接口，常见的数据库连接池

    • DBCP
    • C3P0
    • Druid：阿里巴巴开源项目，性能好

说明

  • 以前：通过 DriverManager 对象获取 Connection 对象
  • 连接池：通过 DataSource 获取 Connection 对象

3.3、Driud 使用

使用步骤

  • 导入依赖（JAR 包）
  • 定义配置文件
  • 加载配置文件
  • 获取 DataSource
  • 获取 Connection
  • 获取 SQL 执行对象、执行 SQL

3.3.1、配置文件

druid.properties（放在 resources 目录下）

常用配置

driverClassName=
url=jdbc:mysql://主机号:端口号/数据库名/参数&参数
username=
password=
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

3.3.2、获取 DataSource

  • 加载配置文件

  • 获取 DataSource

  • 获取 Connection

    // 加载配置文件
Properties prop = new Properties();
prop.load(DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));
// 获取DataSource
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
// 获取Connection
Connection connection = dataSource.getConnection();

获取 Connection 之后，就可以获取 Statement 并执行 SQL 语句了。

JavaWebmysql

相关

JavaWeb的三大作用域

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

标签