JDBC查询

共计 5912 个字符，预计需要花费 15 分钟才能阅读完成。

前面我们讲了 Java 程序要通过 JDBC 接口来查询数据库。JDBC 是一套接口规范，它在哪呢？就在 Java 的标准库 java.sql 里放着，不过这里面大部分都是接口。接口并不能直接实例化，而是必须实例化对应的实现类，然后通过接口引用这个实例。那么问题来了：JDBC 接口的实现类在哪？

因为 JDBC 接口并不知道我们要使用哪个数据库，所以，用哪个数据库，我们就去使用哪个数据库的“实现类”，我们把某个数据库实现了 JDBC 接口的 jar 包称为 JDBC 驱动。

因为我们选择了 MySQL 5.x 作为数据库，所以我们首先得找一个 MySQL 的 JDBC 驱动。所谓 JDBC 驱动，其实就是一个第三方 jar 包，我们直接添加一个 Maven 依赖就可以了：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

注意到这里添加依赖的 scope 是runtime，因为编译 Java 程序并不需要 MySQL 的这个 jar 包，只有在运行期才需要使用。如果把 runtime 改成 compile，虽然也能正常编译，但是在 IDE 里写程序的时候，会多出来一大堆类似com.mysql.jdbc.Connection 这样的类，非常容易与 Java 标准库的 JDBC 接口混淆，所以坚决不要设置为compile。

有了驱动，我们还要确保 MySQL 在本机正常运行，并且还需要准备一点数据。这里我们用一个脚本创建数据库和表，然后插入一些数据：

-- 创建数据库 learjdbc:
DROP DATABASE IF EXISTS learnjdbc;
CREATE DATABASE learnjdbc;

-- 创建登录用户 learn/ 口令 learnpassword
CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword';
GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 创建表 students:
USE learnjdbc;
CREATE TABLE students (id BIGINT AUTO_INCREMENT NOT NULL,
  name VARCHAR(50) NOT NULL,
  gender TINYINT(1) NOT NULL,
  grade INT NOT NULL,
  score INT NOT NULL,
  PRIMARY KEY(id)
) Engine=INNODB DEFAULT CHARSET=UTF8;

-- 插入初始数据:
INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);
INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96);
INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99);
INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86);
INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79);
INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85);
INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90);
INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91);
INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);

在控制台输入 mysql -u root -p，输入root 口令后以 root 身份，把上述 SQL 贴到控制台执行一遍就行。如果你运行的是最新版 MySQL 8.x，需要调整一下 CREATE USER 语句。

使用 JDBC 时，我们先了解什么是 Connection。Connection 代表一个 JDBC 连接，它相当于 Java 程序到数据库的连接（通常是 TCP 连接）。打开一个 Connection 时，需要准备 URL、用户名和口令，才能成功连接到数据库。

URL 是由数据库厂商指定的格式，例如，MySQL 的 URL 是：

jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2

假设数据库运行在本机localhost，端口使用标准的3306，数据库名称是learnjdbc，那么 URL 如下：

jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8

后面的两个参数表示不使用 SSL 加密，使用 UTF- 8 作为字符编码（注意 MySQL 的 UTF- 8 是utf8）。

要获取数据库连接，使用如下代码：

// JDBC 连接的 URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// 获取连接:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接:
conn.close();

核心代码是 DriverManager 提供的静态方法 getConnection()。DriverManager 会自动扫描 classpath，找到所有的 JDBC 驱动，然后根据我们传入的 URL 自动挑选一个合适的驱动。

因为 JDBC 连接是一种昂贵的资源，所以使用后要及时释放。使用 try (resource) 来自动释放 JDBC 连接是一个好方法：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {...}

获取到 JDBC 连接后，下一步我们就可以查询数据库了。查询数据库分以下几步：

第一步，通过 Connection 提供的 createStatement() 方法创建一个 Statement 对象，用于执行一个查询；

第二步，执行 Statement 对象提供的 executeQuery("SELECT * FROM students") 并传入 SQL 语句，执行查询并获得返回的结果集，使用 ResultSet 来引用这个结果集；

第三步，反复调用 ResultSet 的next()方法并读取每一行结果。

完整查询代码如下：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {try (Statement stmt = conn.createStatement()) {try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {while (rs.next()) {long id = rs.getLong(1); // 注意：索引从 1 开始
                long grade = rs.getLong(2);
                String name = rs.getString(3);
                int gender = rs.getInt(4);
            }
        }
    }
}

注意要点：

Statement和 ResultSet 都是需要关闭的资源，因此嵌套使用 try (resource) 确保及时关闭；

rs.next()用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得 ResultSet 时当前行不是第一行）；

ResultSet获取列时，索引从 1 开始而不是0；

必须根据 SELECT 的列的对应位置来调用 getLong(1)，getString(2) 这些方法，否则对应位置的数据类型不对，将报错。

使用 Statement 拼字符串非常容易引发 SQL 注入的问题，这是因为 SQL 参数往往是从方法参数传入的。

我们来看一个例子：假设用户登录的验证方法如下：

User login(String name, String pass) {
    ...
    stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "'AND pass='" + pass + "'");
    ...
}

其中，参数 name 和pass通常都是 Web 页面输入后由程序接收到的。

如果用户的输入是程序期待的值，就可以拼出正确的 SQL。例如：name = "bob"，pass = "1234"：

SELECT * FROM user WHERE login='bob' AND pass='1234'

但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的 SQL，这个 SQL 也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：

SELECT * FROM user WHERE login='bob' OR pass='AND pass=' OR pass=''

这个 SQL 语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设。

要避免 SQL 注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用 SQL 的地方增加转义代码。

还有一个办法就是使用 PreparedStatement。使用PreparedStatement 可以 完全避免 SQL 注入 的问题，因为 PreparedStatement 始终使用 ? 作为占位符，并且把数据连同 SQL 本身传给数据库，这样可以保证每次传给数据库的 SQL 语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录 SQL 如果用 PreparedStatement 可以改写如下：

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

所以，PreparedStatement比 Statement 更安全，而且更快。

注意

使用 Java 对数据库进行操作时，必须使用 PreparedStatement，严禁任何通过参数拼字符串的代码！

我们把上面使用 Statement 的代码改为使用PreparedStatement：

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {ps.setObject(1, "M"); // 注意：索引从 1 开始
        ps.setObject(2, 3);
        try (ResultSet rs = ps.executeQuery()) {while (rs.next()) {long id = rs.getLong("id");
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
            }
        }
    }
}

使用 PreparedStatement 和Statement稍有不同，必须首先调用 setObject() 设置每个占位符 ? 的值，最后获取的仍然是 ResultSet 对象。

另外注意到从结果集读取列时，使用 String 类型的列名比索引要易读，而且不易出错。

注意到 JDBC 查询的返回值总是ResultSet，即使我们写这样的聚合查询SELECT SUM(score) FROM ...，也需要按结果集读取：

ResultSet rs = ...
if (rs.next()) {double sum = rs.getDouble(1);
}

有的童鞋可能注意到了，使用 JDBC 的时候，我们需要在 Java 数据类型和 SQL 数据类型之间进行转换。JDBC 在 java.sql.Types 定义了一组常量来表示如何映射 SQL 数据类型，但是平时我们使用的类型通常也就以下几种：

SQL 数据类型	Java 数据类型
BIT, BOOL	boolean
INTEGER	int
BIGINT	long
REAL	float
FLOAT, DOUBLE	double
CHAR, VARCHAR	String
DECIMAL	BigDecimal
DATE	java.sql.Date, LocalDate
TIME	java.sql.Time, LocalTime