JDBC

1、JDBC

MySQL 知识

1.1、Java 数据库连接

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

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

每个关系型数据库的底层实现细节都不同，相应的操作方式也不同。
SUN 公司定义了标准接口（JDBC），定义了所有操作关系型数据库的规则。
数据库厂商负责实现接口，创建实现类（即数据库驱动）。

好处

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

1.2、JDBC 开发步骤（❗）

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

导入依赖：数据库连接驱动（mysql-connector-java）

代码开发：

驱动管理
- 注册驱动：Driver
- 获取数据库连接：URL、用户名、密码
定义 SQL 语句
获取 SQL 执行对象：Statement
执行 SQL，获得返回结果：受影响行数 / 结果集
释放资源

基于 MySQL 8 示例

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（❗）

驱动管理：DriverManager
连接：Connection
SQL 执行对象：Statement、PreparedStatement
结果集：ResultSet

2.1、DriverManager

驱动管理的常用方法

注册驱动：registerDriver()
获取数据库连接：getConnection()

2.1.1、注册驱动

Driver

在 1.2 代码示例中，是通过反射加载 Driver 类，而不是调用 DriverManager 类的 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 8 之前：com.mysql.jdbc.Driver
- MySQL 8 开始：com.mysql.cj.jdbc.Driver
自动注册驱动
- 使用 MySQL 5 之后的驱动包，可省略注册驱动的步骤。
- 会自动加载 JAR 包中 META-INF/services/java.sql.Driver 文件中的驱动类。

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

数据库连接对象的常用方法

获取执行对象
事务管理

2.2.1、获取执行对象

普通 SQL 执行对象
预编译 SQL 执行对象

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

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

2.2.2、事务管理（❗）

MySQL 事务

事务操作
- 控制事务 ：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 捕获。

开启事务。
执行 SQL。
若无异常，则正常提交事务。

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

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 执行对象

JDBC 通常只会执行 DML 和 DQL（即增删改查），而 DCL 和 DDL 在数据库层面进行。

常用方法

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

2.4、ResultSet（❗）

结果集：封装 DQL 执行后返回的结果。

常用方法

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()

示例

执行 DQL，获取结果集。

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

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 注入问题。

2.5.1、SQL 注入（❗）

Web 攻击技术：通过特殊字符 恶意拼接 SQL，对 SQL 语句进行转义，从而非法运行 SQL。

模拟登录场景：接收客户端参数，拼接字符串作为 SQL，使用 Statement 执行 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"));
    }
}

正常情况：假设客户端输入用户名【jaywee】、密码【123456】
```
SELECT * FROM user
WHERE name = 'jaywee' AND password = '123456'
```
SQL 注入：客户端输入特殊字符（用户名【jaywee】，密码【'or '1' = '1】）
- AND 语句比 OR 语句优先执行，'1' = '1' 恒成立
```
SELECT * FROM user
WHERE name = 'jaywee' AND password = '' OR '1' = '1'
```
- 等价于以下 SQL，进行了非法查询。
```
SELECT * FROM user WHERE true;
SELECT * FROM user;
```

2.5.2、预编译 SQL

PreparedStatement：预编译 SQL 执行对象

作用：提前编译 SQL 语句，将特殊字符进行转义，预防 SQL 注入问题。

常用方法

void setXxx(int parameterIndex, Xxx xxx);

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

设置参数：通过参数下标设置（从 1 开始）
执行 SQL：执行时不需要传入 SQL，因为已预编译

获取 PreparedStatement：通过 Connection

PreparedStatement prepareStatement(String sql)
    throws SQLException;

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 操作数据库的流程

Java 代码
1. 获取 SQL 执行对象（Statement）
2. 执行 SQL：将 SQL 发送到 MySQL 服务器端（statement.executeXxx()）
MySQL 服务器端
1. 检查 SQL：语法正确性
2. 编译 SQL：编译成可执行的函数
3. 执行 SQL，将结果返回给 Java。

使用上的对比

使用 Statement：每次执行 SQL 时，MySQL 服务器端都需经历以上 3 个步骤

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

使用 PreparedStatement
- 获取 SQL 执行对象时：将 SQL 模板发送给 MySQL 服务器端进行检查和编译。
- 执行 SQL 时：MySQL 服务器端直接执行 SQL 语句，无需再次检查和编译。
```
// 模板SQL
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
ps.setXxx()
```

3、数据库连接池（❗）

3.1、简介

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

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

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

说明：无论是否使用连接池，获取 Connection 之后的操作相同。

没有使用连接池
- 用户每次需要使用连接时，都需要通过 DriverManager 创建 Connection 对象，使用完毕后释放。
- 特点：重复创建和释放连接资源，耗费 CPU 性能。
使用数据库连接池：预先创建并存储 Connection 对象。
- 用户每次需要使用连接时，从连接池中获取 Connection，使用完毕后归还给连接池。
- 特点：资源重用，节省频繁创建销毁连接所花费的时间，提升系统响应速度。

3.2、DataSource

数据源：DataSource

SUN 公司定义了标准接口（DataSource），定义了获取 Connection 的功能。
```
Connection getConnection()
```
第三方组织负责实现接口，创建实现类（即数据库连接池）
常见的数据库连接池
- DBCP
- C3P0
- Hikari
- Druid：阿里巴巴开源项目，性能好

3.3、Driud

通过连接池获取 Connection 的步骤

环境及配置
- 导入依赖：数据库连接驱动、数据库连接池
- 定义配置文件：在 resources 目录下
Java 代码
- 加载配置文件
- 获取 DataSource
- 获取 Connection

3.3.1、配置文件

druid.properties

常用配置

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

3.3.2、使用

加载配置文件
获取 DataSource

获取 Connection

Properties prop = new Properties();
prop.load(DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));

DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

Connection connection = dataSource.getConnection();

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

4、JDBC + 数据源开发步骤（❗）

4.1、环境及配置

导入依赖：数据库连接驱动、数据库连接池

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

定义连接池配置文件（resources 目录）

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

4.2、Java 代码

获取连接

加载连接池配置文件
获取 DataSource

获取 Connection

Properties prop = new Properties();
prop.load(DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));

DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

Connection connection = dataSource.getConnection();

处理业务
1. 定义 SQL 模板
2. 获取 SQL 执行对象：PreparedStatement
  - 传入 SQL 模板，预编译
  - 设置参数
3. 执行 SQL
4. 处理返回结果：受影响行数 / 结果集
释放资源

posted @ 2021-11-27 00:49 Jaywee 阅读(101) 评论(0) 编辑收藏举报

刷新页面返回顶部

Jaywee

JDBC

1、JDBC

1.1、Java 数据库连接

1.2、JDBC 开发步骤（❗）

2、JDBC API（❗）

2.1、DriverManager

2.1.1、注册驱动

2.1.2、获取数据库连接

2.2、Connection

2.2.1、获取执行对象

2.2.2、事务管理（❗）

2.3、Statement

2.4、ResultSet（❗）

2.5、PreparedStatement（❗）

2.5.1、SQL 注入（❗）

2.5.2、预编译 SQL

2.5.3、示例

2.5.4、原理

3、数据库连接池（❗）

3.1、简介

3.2、DataSource

3.3、Driud

3.3.1、配置文件

3.3.2、使用

4、JDBC + 数据源开发步骤（❗）

4.1、环境及配置

4.2、Java 代码

公告

☝

👇