JDBC查询


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

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

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

  1. <dependency>
  2. <groupId>mysql</groupId>
  3. <artifactId>mysql-connector-java</artifactId>
  4. <version>5.1.47</version>
  5. <scope>runtime</scope>
  6. </dependency>

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

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

  1. -- 创建数据库learjdbc:
  2. DROP DATABASE IF EXISTS learnjdbc;
  3. CREATE DATABASE learnjdbc;
  4. -- 创建登录用户learn/口令learnpassword
  5. CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword';
  6. GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION;
  7. FLUSH PRIVILEGES;
  8. -- 创建表students:
  9. USE learnjdbc;
  10. CREATE TABLE students (
  11. id BIGINT AUTO_INCREMENT NOT NULL,
  12. name VARCHAR(50) NOT NULL,
  13. gender TINYINT(1) NOT NULL,
  14. grade INT NOT NULL,
  15. score INT NOT NULL,
  16. PRIMARY KEY(id)
  17. ) Engine=INNODB DEFAULT CHARSET=UTF8;
  18. -- 插入初始数据:
  19. INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88);
  20. INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
  21. INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
  22. INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);
  23. INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96);
  24. INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99);
  25. INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86);
  26. INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79);
  27. INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85);
  28. INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90);
  29. INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91);
  30. INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);

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

JDBC连接

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

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

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

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

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

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

要获取数据库连接,使用如下代码:

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

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

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

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

JDBC查询

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

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

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

第三步,反复调用ResultSetnext()方法并读取每一行结果。

完整查询代码如下:

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

注意要点:

StatmentResultSet都是需要关闭的资源,因此嵌套使用try (resource)确保及时关闭;

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

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

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

SQL注入

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

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

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

其中,参数namepass通常都是Web页面输入后由程序接收到的。

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

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

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

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

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

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

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

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

所以,PreparedStatementStatement更安全,而且更快。

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

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

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

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

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

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

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

数据类型

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

SQL数据类型Java数据类型
BIT, BOOLboolean
INTEGERint
BIGINTlong
REALfloat
FLOAT, DOUBLEdouble
CHAR, VARCHARString
DECIMALBigDecimal
DATEjava.sql.Date, LocalDate
TIMEjava.sql.Time, LocalTime

注意:只有最新的JDBC驱动才支持LocalDateLocalTime

练习

JDBC查询 - 图1下载练习:使用JDBC查询数据库 (推荐使用IDE练习插件快速下载)

小结

JDBC接口的Connection代表一个JDBC连接;

使用JDBC查询时,总是使用PreparedStatement进行查询而不是Statement

查询结果总是ResultSet,即使使用聚合查询也不例外。

读后有收获可以支付宝请作者喝咖啡:

JDBC查询 - 图2