JavaWeb 学习笔记 11：JDBC

JavaWeb 学习笔记 11：JDBC

1.简介

JDBC 是一个 Java 为接入不同类型的数据库定义的一个数据库连接和执行 SQL 的 API。

可以用下图表示：

图中的具体数据库的驱动实际上就是数据库厂商提供的 JDBC 接口的实现类。

2.快速开始

用 Maven 创建一个简单的 Java 应用，并添加数据库驱动的依赖：

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

用 JDBC 相关的 API 创建连接并执行 SQL：

public class App1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载 MySQL 驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取 JDBC 连接
        String url = "jdbc:mysql://localhost:3306/jdbc_demo";
        String username = "root";
        String password = "mysql";
        Connection connection = DriverManager.getConnection(url, username, password);
        // 获取 SQL 执行对象
        Statement statement = connection.createStatement();
        // 执行 SQL
        String sql = "update tb_brand set ordered=99 where id=1";
        int rows = statement.executeUpdate(sql);
        if (rows > 0) {
            System.out.println("品牌信息修改成功");
        } else {
            System.out.println("品牌信息修改失败");
        }
        // 释放资源
        statement.close();
        connection.close();
    }
}

这个代码实际上有点问题，释放资源的相关语句最好放在finally块中：

public class App2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载 MySQL 驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取 JDBC 连接
        String url = "jdbc:mysql://localhost:3306/jdbc_demo";
        String username = "root";
        String password = "mysql";
        Connection connection = DriverManager.getConnection(url, username, password);
        try {
            // 获取 SQL 执行对象
            Statement statement = connection.createStatement();
            try {
                // 执行 SQL
                String sql = "update tb_brand set ordered=99 where id=1";
                int rows = statement.executeUpdate(sql);
                if (rows > 0) {
                    System.out.println("品牌信息修改成功");
                } else {
                    System.out.println("品牌信息修改失败");
                }
            } finally {
                // 清理资源
                if (statement != null) {
                    statement.close();
                }
            }
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}

这样可以确保在任何情况下都释放资源，但缺点是代码的可读性变差。

可以使用 Lombok 进行改善：

public class App3 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载 MySQL 驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取 JDBC 连接
        String url = "jdbc:mysql://localhost:3306/jdbc_demo";
        String username = "root";
        String password = "mysql";
        @Cleanup Connection connection = DriverManager.getConnection(url, username, password);
        // 获取 SQL 执行对象
        @Cleanup Statement statement = connection.createStatement();
        // 执行 SQL
        String sql = "update tb_brand set ordered=99 where id=1";
        int rows = statement.executeUpdate(sql);
        if (rows > 0) {
            System.out.println("品牌信息修改成功");
        } else {
            System.out.println("品牌信息修改失败");
        }
    }
}

Lombok 的 @Cleanup 注解的本质是由 Lombok 替我们在生成的字节码中添加相应的资源释放代码到try...finally语句中。所以可以保持源码简洁和可读性的同时确保任何情况下都释放资源。

3.API

3.1.DriverManager

DriverManager的主要用途是注册数据库驱动以及获取数据库连接。

3.1.1.注册驱动

在这个示例中，我们通过加载驱动类的方式实现驱动注册：

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

之所以可以这样，是因为 MySQL 驱动类中有一个静态初始化块，会调用DriverManager.registerDriver方法完成驱动注册：

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

使用高版本（>=5）的 MySQL 驱动时，不需要通过手动加载驱动类的方式实现驱动注册，这是因为在驱动依赖包的META-INFO/services/java.sql.Driver文件中添加了驱动的完整类名：

com.mysql.cj.jdbc.Driver

这样 DriverManager 就可以正确获取到驱动类并加载。

也就是说，Class.forName(...)是可以省略的：

public class App4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 获取 JDBC 连接
        String url = "jdbc:mysql://localhost:3306/jdbc_demo";
        String username = "root";
        String password = "mysql";
        @Cleanup Connection connection = DriverManager.getConnection(url, username, password);
        // ...
    }
}

3.1.2.获取数据库连接

要获取数据库连接，需要提供：

• 连接数据库的 url
• 数据库用户名
• 数据库密码

连接数据库的 url 是一个固定格式，具体包括：

(连接协议)://(数据库服务器ip或主机名):(数据库服务器端口)/(数据库名称)[?参数键值对1[&参数键值对2]...]

比如对于示例中使用的 url：jdbc:mysql://localhost:3306/jdbc_demo，包含的信息为：

• 连接协议：jdbc:mysql，用于连接 MySQL 数据库
• 数据库服务器：localhost，本地主机
• 端口：3306，默认的 MySQL 端口
• 数据库：jdbc_demo

如果连接的是本地主机（localhost），且是默认端口（3306），可以使用一种简写方式：

jdbc:mysql:///jdbc_demo

可以看做是将localhost:3306这部分删除后的 url。

可以在连接 MySQL 的 url 上添加一些参数，比如禁用 SSL 以提高性能：

String url = "jdbc:mysql:///jdbc_demo?useSSL=false";

3.2.Connection

Connection 的主要用途是获取 SQL 执行对象以及管理事务。

3.2.1.获取 SQL 执行对象

• 普通执行SQL对象

  Statement createStatement()
  

  入门案例中就是通过该方法获取的执行对象。

• 预编译SQL的执行SQL对象：防止SQL注入

  PreparedStatement  prepareStatement(sql)
  

  通过这种方式获取的 PreparedStatement SQL语句执行对象是我们一会重点要进行讲解的，它可以防止SQL注入。

• 执行存储过程的对象

  CallableStatement prepareCall(sql)
  

  通过这种方式获取的 CallableStatement 执行对象是用来执行存储过程的，而存储过程在MySQL中不常用，所以这个我们将不进行讲解。

3.2.2.管理事务

直接看示例：

public class App6 {
    public static void main(String[] args) throws Exception {
        // 获取 JDBC 连接
        String url = "jdbc:mysql:///jdbc_demo?useSSL=false";
        String username = "root";
        String password = "mysql";
        @Cleanup Connection connection = DriverManager.getConnection(url, username, password);
        // 获取 SQL 执行对象
        @Cleanup Statement statement = connection.createStatement();
        // 执行 SQL
        // 开启事务
        connection.setAutoCommit(false);
        try{
            String sql1 = "update tb_brand set ordered=66 where id=1";
            statement.executeUpdate(sql1);
            String sql2 = "update tb_brand set ordered=66 where id=2";
            statement.executeUpdate(sql2);
            // 提交事务
            connection.commit();
        }
        catch (Exception e){
            // 回滚事务
            connection.rollback();
            throw e;
        }
    }
}

这里使用Connection的3个方法管理事务：

• setAutoCommit，将数据库的自动提交设置为false，即开启事务。
• commit，提交事务
• rollback，回滚事务

MySQl 数据库默认为自动提交，即不开启事务，此时在 MySQL 客户端执行一条 SQL 会立即生效。

可以在两个 SQL 执行之间“人为制造”一个异常来观察事务的执行：

String sql1 = "update tb_brand set ordered=66 where id=1";
statement.executeUpdate(sql1);
int i = 1/0;
String sql2 = "update tb_brand set ordered=66 where id=2";
statement.executeUpdate(sql2);

结果是两条数据都没有改变，因为有异常产生，事务被回滚了。这体现了事务的原子性，即要么都被执行（提交），要么都不执行（回滚）。

3.3.Statement

可以用Statement执行 DDL 或 DML 的 SQL 语句，在执行 DML 语句时，可以通过返回值判断是否执行成功：

String sql1 = "update tb_brand set ordered=99 where id=1";
int rows = statement.executeUpdate(sql1);
if (rows > 0){
    System.out.println("修改成功");
}
else{
    System.out.println("修改失败");
}

示例中的返回值rows表示执行 SQL 后受影响的行数，因此大于零表示成功，小于等于零表示失败。

这里的执行失败和常见的 SQL 编写错误执行失败是有区别的，这里是没有数据行受影响，比如查询条件没有匹配到任何数据行，因此没有发生更新动作。

但如果执行的是 DDL 语句，就没法用这种方式判断，比如：

String sql1 = "drop database db2";
int rows = statement.executeUpdate(sql1);
if (rows > 0){
    System.out.println("删除成功");
}
else{
    System.out.println("删除失败");
}

这里用 DDL 语句删除一个数据库，明明删除成功了，但是返回的rows 是零。

3.4.ResultSet

JDBC 将查询语句返回的数据封装为一个ResultSet对象。

ResultSet 中有一个光标，用于标记结果集中的一行（初始位置在第一行之前），可以用一些 API 控制光标的移动。比如用next()方法可以将光标移动到下一行，且会返回一个 Bool 值，告诉你下一行是否有数据。当光标移动到一个有效的数据行后，我们可以通过getXXX方法获取某个列的结果，可以通过列的下标（从1开始）获取，也可以通过列名获取。比如通过getInt(1)可以获取光标所在行第一列的值，且转换为 Int。

示例：

String sql = "select * from tb_brand where id<5";
@Cleanup ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
    int id = resultSet.getInt("id");
    String brandName = resultSet.getString("brand_name");
    String companyName = resultSet.getString("company_name");
    System.out.println(String.format("%d[brandName:%s,companyName:%s]", id, brandName, companyName));
}

要注意，ResultSet本身也是一个资源，需要调用ResultSet.closer()方法关闭资源，这里同样使用 Lombok 的@Cleanup注解。

也可以使用列下标：

int id = resultSet.getInt(1);
String brandName = resultSet.getString(2);
String companyName = resultSet.getString(3);

3.5.PreparedStatement

PreparedStatement继承自Statement，用于执行预处理 SQL，可以用于预防 SQL 注入攻击。

3.5.1.SQL 注入攻击

看一个用户登录的例子：

private static boolean login(String username, String password) throws Exception {
    //查询数据库，如果有用户名和密码的数据，就视为用户名和密码正确，登录成功
    // 获取 JDBC 连接
    String url = "jdbc:mysql:///jdbc_demo?useSSL=false";
    String dbUsername = "root";
    String dbPassword = "mysql";
    @Cleanup Connection connection = DriverManager.getConnection(url, dbUsername, dbPassword);
    // 获取 SQL 执行对象
    @Cleanup Statement statement = connection.createStatement();
    // 执行 SQL
    String sql = "select * from tb_user where username='%s' and password='%s' limit 1";
    sql = String.format(sql, username, password);
    System.out.println(sql);
    @Cleanup ResultSet resultSet = statement.executeQuery(sql);
    if (resultSet.next()) {
        // 查询到结果，登录成功
        System.out.println(String.format("登录成功...[username:%s,password:%s]",
                                         username, password));
        return true;
    }
    // 没有查询到结果，登录失败
    System.out.println(String.format("登录失败...[username:%s,password:%s]",
                                     username, password));
    return false;
}

调用登录方法模拟登录：

login("zhangsan", "123");
login("zhangsan", "111");

输出：

select * from tb_user where username='zhangsan' and password='123' limit 1
登录成功...[username:zhangsan,password:123]
select * from tb_user where username='zhangsan' and password='111' limit 1
登录失败...[username:zhangsan,password:111]

没有问题。

但如果用下边的方式登录：

login("abc", "' or '1'='1");

输出：

select * from tb_user where username='abc' and password='' or '1'='1' limit 1
登录成功...[username:abc,password:' or '1'='1]

观察打印的 SQL 就能发现，通过将输入的密码伪造成' or '1'='1这样的 SQL 片段，就能改变 SQL 的内容，让 SQL 的查询条件中出现or '1'='1'这样的恒等式，在这种情况下，SQL 执行的结果会是表的第一行数据。因此返回的是true。

3.5.2.预防注入攻击

使用 PrepareStatement预防 SQL 注入攻击：

// 获取 SQL 执行对象
String sql = "select * from tb_user where username=? and password=? limit 1";
@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
// 执行 SQL
@Cleanup ResultSet resultSet = preparedStatement.executeQuery();

获取PreparedStatement时需要传入 SQL 语句，并且用占位符?表示可能由用户输入的查询条件。并且在之后使用setXXX方法设置占位符对应的参数的值（下标从1开始）。

再次执行测试：

login("zhangsan", "123");
login("zhangsan", "111");
login("abc", "' or '1'='1");

结果：

登录成功...[username:zhangsan,password:123]
登录失败...[username:zhangsan,password:111]
登录失败...[username:abc,password:' or '1'='1]

SQL 注入攻击没有成功。

3.5.3.原理

之所以 PreparedStatement 可以预防 SQL 注入攻击，是因为它会对占位符对应的参数值进行处理，对其中的敏感字符（比如单引号）进行转义（在单引号前添加反斜杠），这样参数值就只能作为一个字符串充当查询条件，不能改变 SQL 语句的结构了。

要证明这一点，需要使用 MySQL 自身的日志功能。

要开启 MySQL 日志，需要修改 MySQL 的全局配置文件my.ini（或 my.cnf）：

[mysqld]
port=3306
# 输出日志到文件
log-output=FILE
# 开启日志功能
general-log=ON
# 日志文件
general_log_file="D:/software/coding/mysql-8.0.29-winx64/logs/mysql.log"
# 开启慢查询功能
slow-query-log=ON
# 慢查询日志文件
slow_query_log_file="D:/software/coding/mysql-8.0.29-winx64/logs/mysql_slow.log"
# 超过 2 秒的查询是慢查询
long_query_time=2

• my.ini 文件可以存在于好几个位置，一般会放在 MySQL 的安装根目录，如果电脑上没有该文件，就手动添加一个。关于 MySQL 配置文件的更多说明可以查看官方文档。
• 全局配置文件中的可选环境变量说明可以查看官方文档。
• 需要注意，配置文件中的文件路径中的目录分隔符要使用/而不是反斜杠，因为后者是转义符，或者也可以给后者添加转义符变成双反斜杠\\，否则会导致 MySQL 不能正确写入日志。

查看日志就能看到，实际执行的 SQL 是：

在 VSC 中很明显，使用了转义符'，导致整个参数值都是一个字符串。

在 SQL 中两个单引号''表示一个字符串中的单引号。

使用 PrepareStatement 除了可以预防 SQL 注入攻击，还可以提升性能。

用 Java 执行 SQL 的过程可以用下图表示：

大致有这么几个步骤：

1. Java 将 SQL 语句发送给 MySQL 服务器。
2. MySQL 检查 SQL 语法。
3. MySQL 对 SQL 进行编译，生成可执行的函数。
4. 执行 SQL。

这其中 MySQL 检查 SQL 语法和编译 SQL 占用相当长的时间。

如果使用的是 PrepareStatement，在用 SQL 创建 PrepareStatement 对象的时候，Java 就会将带占位符的 SQL 发送给 MySQL，MySQL 会对 SQL 检查语法和编译，这样在执行 SQL 的时候就只需要传输参数给 MySQL，MySQL 使用编译好的 SQL 直接执行即可，无需检查 SQL 语法和编译 SQL。

当然，对于单条 SQL 来说两者没有什么性能提升，只是将 SQL 的语法检查和编译提前进行罢了。但是如果是对同一条预编译 SQL 执行多次查询，只是使用不同的参数，那就会带来不错的性能提升，每执行 N 次，就可以减少 N-1 次的语法检查和编译过程。

要使用“预编译 SQL”功能，还需要打开一个 MySQL 开关，MySQL 默认是关闭预编译 SQL 功能的。

String url = "jdbc:mysql:///jdbc_demo?useSSL=false&useServerPrepStmts=true";

编写一个简单示例，在一个 PrepareStatement 上执行两次不同参数的查询：

String sql = "select * from tb_user where username=? and password=? limit 1";
@Cleanup PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
@Cleanup ResultSet resultSet = preparedStatement.executeQuery();
preparedStatement.setString(1, "aaa");
preparedStatement.setString(2, "bbb");
@Cleanup ResultSet resultSet2 = preparedStatement.executeQuery();

日志：

结果很清楚，一条 Prepare 语句，两条 Execute 语句，说明只编译了一次。

4.数据库连接池

数据库连接池用于管理和分配数据库连接（Connection）。

这个过程可以用下图表示：

Java 官方对数据库连接池定义了一个接口DataSource，由第三方提供具体的实现。

常见的数据库连接池有：

• DBCP
• C3P0
• Druid

这里以 Druid 为例进行说明。

添加 Druid 的依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.17</version>
</dependency>

在/resources目录下添加一个配置文件 jdbc.properties：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///jdbc_demo?useSSL=false&useServerPrepStmts=true
username=root
password=mysql
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

定义一个类变量 DataSource 作为数据库连接池句柄，并在静态初始化块中创建 Druid 数据库连接池：

public class App13 {
    private static DataSource dataSource;
    static {
        // 从配置文件读取数据库连接信息
        Properties properties = new Properties();
        InputStream propertiesStream = App13.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            // 加载数据库连接信息到 Properties
            properties.load(propertiesStream);
            try {
                // 创建 Druid 数据库连接池
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

现在获取数据库连接时使用数据库连接池即可，不再需要使用DriverManager：

private static boolean login(String username, String password) throws Exception {
    //查询数据库，如果有用户名和密码的数据，就视为用户名和密码正确，登录成功
    // 获取 JDBC 连接
    @Cleanup Connection connection = dataSource.getConnection();
    // ...
}

The End，谢谢阅读。

本文的完整示例可以从这里获取。

5.参考资料

• 黑马程序员JavaWeb基础教程
• 从零开始 Spring Boot 35：Lombok - 红茶的个人站点 (icexmoon.cn)
• Maven 简易指南 - 红茶的个人站点 (icexmoon.cn)
• MySQL 8.0参考手册：：4.2.2.2使用选项文件