阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

JDBC查询

69次阅读
没有评论

共计 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 连接

使用 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 查询

获取到 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) 这些方法，否则对应位置的数据类型不对，将报错。

SQL 注入

使用 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

注意：只有最新的 JDBC 驱动才支持 LocalDate 和LocalTime。

练习

使用 JDBC 查询数据库。

下载练习

小结

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

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

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

正文完
星哥玩云-微信公众号
post-qrcode
Java java教程 Linux 星哥说事
发表至： JAVA
2024-08-05
 0
星锅
版权声明：本站原创文章，由 星锅 于2024-08-05发表，共计5912字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
if条件判断
使用Atomic
LocalDateTime
将网关注册到”服务中心”，实现服务转发
搭建项目框架
Maven介绍
XML概述
枚举类型
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3419805
评论数
4
文章搜索
热门文章
基于开源MaxKB构建大语言模型的本地知识库系统

基于开源MaxKB构建大语言模型的本地知识库系统

基于开源 MaxKB 构建大语言模型的本地知识库系统 什么是 MaxKB MaxKB 是一款基于 LLM（La...
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
vmware下的网卡分配问题

vmware下的网卡分配问题

导读 vmware 虚拟机下 linux centos6.6 只有 lo，没有 eth0 网卡、随机分配 ip...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
分享7 个开源项目管理工具

分享7 个开源项目管理工具

在一项调查中，有 71% 的组织表示他们在开发过程中会用到敏捷方法。此外，用敏捷方法管理项目比传统方法管理项目...
在Linux 命令行下浏览天气预报

在Linux 命令行下浏览天气预报

导读 Q: 我经常在 Linux 桌面查看天气预报。然而，是否有一种在终端环境下，不通过桌面小插件或者浏览器查...
Linux 预设命令行 —— Bash

Linux 预设命令行 —— Bash

这篇文章主要介绍的是 Bash，希望各位能从这篇文章里对 Bash 有一个简单的了解 Bash 是什么 Bas...
如何用sed进行十六进制替换

如何用sed进行十六进制替换

现网 teastore 导出的数据文件，通过 vim 和 less 查看时，都发现是乱码，通过 more 查看...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中