2022-06-08：JDBC编程、JDBC查询、SQL与Java数据类型

程序运行的时候，往往需要存取数据。现代应用程序最基本，也是使用最广泛的数据存储就是关系数据库。

Java为关系数据库定义了一套标准的访问接口：JDBC（Java Database Connectivity），本节介绍如何在Java程序中使用JDBC。

1、JDBC简介

在介绍JDBC前，先简单了解一下关系数据库。

为了便于程序保存和读取数据，而且能直接通过条件快速查询到指定的数据，就出现了数据库这种专门用于集中存储和查询的软件。

不过本教程不涉及关系数据库的详细内容，如果想从零开始学习关系数据库和基本的SQL语句，可以看SQL课程。

1.1、JDBC

JDBC是Java DataBase Connectivity的缩写，它是Java程序访问数据库的标准接口。

使用Java程序访问数据库时，Java代码并非通过TCP连接去连接数据库，而是通过JDBC接口来访问，而JDBC接口则通过JDBC驱动来实现真正对数据库的访问。

例如，我们在Java代码中要访问MySQL，那么必须编写代码操作JDBC接口。注意到JDBC接口是Java标准库自带的，所以可以直接编译。而具体的JDBC驱动是由数据库厂商提供的。访问某个具体的数据库时，我们只需要引入该厂商提供的JDBC驱动，就可以通过JDBC接口来访问，这样保证了Java程序编写的是一套数据库访问代码，却可以访问各种不同的数据库，因为他们都提供了标准的JDBC驱动：

从代码来看，Java标准库自带的JDBC接口其实就是定义了一组接口，而某个具体的JDBC驱动其实就是实现了这些接口的类：

实际上，一个MySQL的JDBC驱动就是一个jar包，它本身也是纯Java编写的。我们编写的代码只需要引用Java标准库提供的java.sql包下边的相关接口，由此再间接地通过MySQL驱动的jar包通过网络访问MySQL服务器，所有复杂的网络通讯都被封装到JDBC驱动中，因此，Java程序本身只需要引入一个MySQL驱动的jar包就可以正常访问MySQL服务器：

小结

使用JDBC的好处是：

各数据库厂商使用相同的接口，Java代码不需要针对不同的数据库分别开发；
Java程序编译期仅仅依赖java.sql包，不依赖具体数据库的jar包；
可随时替换底层数据库，访问数据库的Java代码基本不变。

2、JDBC查询

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

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

这里我们选择MySQL作为数据库，所以我们首先要找到一个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自带的控制台，或者在cmd控制台中输入 mysql -u root -p，输入密码后，把上述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。使用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，即使我们写这样的聚合查询，也需要按照结果集读取：

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

数据类型

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接口的Connection代表一个JDBC连接；

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

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

posted @ 2022-06-08 11:11 ShineLe 阅读(86) 评论(0) 收藏举报

刷新页面返回顶部

ShineLee