跳到主要内容

12-4 RDBMS 与 SQL（下）

使用 JDBC​

或许 Java 世界中最有名的数据库支持库就是 Java 数据库连接程序（Java DataBase Connectivity，JDBC） 了。JDBC 定义了一组类，它们可以用来创建和执行 SQL 语句，并访问 SQL 执行后的结果。

关于 JDBC 有趣的一个事实是：JDBC 实际上只是一个接口，它不负责和任何具体的数据库对接。要让 JDBC 能和数据库连接，就需要使用对应的驱动程序（Driver）。JDBC 已经内置在 Java 标准库中，而对于我们所使用的 MySQL 的驱动程序，则必须另行获取。幸运的是，Oracle 已经将 MySQL 的驱动程序发布到了 Maven 仓库上，我们只需要在 Gradle 中添加如下依赖即可：

runtimeOnly("com.mysql:mysql-connector-j:9.4.0")

数据库的安全性一定程度上也由其驱动程序决定，所以你应该尽可能使用最新的驱动程序，MySQL 驱动程序（称作 MySQL Connector/J）的版本信息可以在 Maven 中央仓库索引 上找到，将 9.4.0（编写时的最新版本）替换成更新的版本即可。

至于为什么这里使用 runtimeOnly 而不是之前的 implementation，这涉及到 Gradle 对依赖的管理和打包方式。简单来说就是，我们并不会直接使用 MySQL Connector/J 提供的类，但没了它插件就运行不了，对于这种需求，就可以在 Gradle 中指定 runtimeOnly。

连接到 MySQL​

为了让 JDBC 知道如何与 MySQL 交互，我们需要告诉 JDBC 『加载 MySQL 的驱动程序！』，这很容易做到：

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

Class.forName 会让 Kotlin 加载指定的类，类加载是 JVM 中的一个比较复杂的概念，你可以理解为，MySQL 在 com.mysql.cj.jdbc.Driver 这个类里面添加了一些『自动执行』的代码，这些代码的功能就是加载 MySQL 的驱动程序。为了使用这些代码，我们不通过调用它的函数，而是使用一个特殊的 Class.forName……嘛，总之，只要做这样的调用，驱动程序就设置完成了。

现在有了驱动程序，我们就可以开始连接到 MySQL：

val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name", "user", "********")

打开的连接是需要关闭的，所以要在 onDisable 中（或者程序不再需要这个连接的时候）添加如下的内容：

conn.close()

引号内看上去有点像 URL 的东西其实是 JDBC 所使用的 URI（统一资源标识符），你可以理解为 JDBC 用于描述『如何连接数据库』的一种格式。这个 URI 中有几个重要的部分：

  • localhost：数据库所在的主机名，通常是本机，但有些大型服务器拥有单独的数据库服务器，这时需要填入数据库服务器的主机名。
  • 3306：数据库服务端口，默认是 3306。
  • db_name：所要连接的数据库名称。在 MySQL 终端中，可以使用 USE 来选择数据库，但我们已经说过，通常一个应用程序使用一个数据库，因此需要在 JDBC 中指定数据库名。当连接完成后，我们就可以直接使用数据库中的表，不需要再执行 USE。

大多数数据库服务器都使用用户名和密码进行验证，因此我们也需要在此填写它们。

值得注意的是，这些值大部分时候都不由作为插件开发者的我们来决定，因为数据库服务器是服务器管理员所安装和配置的，我们需要向管理员询问这些设置。大部分时候，使用数据库的插件会有一个配置文件，供管理员填写数据库的相关信息，例如：

db:
    host: localhost
    port: 3306
    name: db_name
    user: user
    password: SUPER_SECRET_PASSWORD_DO_NOT_SHARE_OR_YOU_WILL_BE_FIRED
    ssl: false

创建和执行语句​

前面已经提到过，程序可以以字符串的形式将 SQL 语句传递给 RDBMS 执行：

val stmt = conn.createStatement()
stmt.execute("""
    CREATE TABLE deposit (
        player VARCHAR(255) PRIMARY KEY NOT NULL,
        diamond INT NOT NULL DEFAULT 0,
        gold INT NOT NULL DEFAULT 0 
    );  
""".trimIndent())
提示

三重引号 """ 可以用于在 Kotlin 中编写多行字符串（默认的单引号字符串不能跨越多行），但这样编写出来的字符串会因为缩进而在每行前有额外的空格，Kotlin 提供了 trimIndent 方法来移除它们，这样编写代码的人就不必在代码和输出的美观性之间做选择。

我们首先使用 createStatement 方法创建一条语句对象（当然，一切都是对象！），然后调用 execute 方法来执行它，JDBC 会将该 SQL 语句发送给 MySQL 并执行。

当执行完成后，一件很重要的事情是关闭语句，这样 JDBC 就可以释放相关的资源：

stmt.close()
善始善终

你可能听人说过，Kotlin（和 Java）是一门自动内存管理的语言。的确如此，使用 Kotlin 编写程序时，对象会在创建时自动分配内存，而不需要编程者手动管理。不过，Kotlin 的自动性仅限于内存管理方面，在 SQL 库中，清理工作并不能全自动完成。由于语句本质上是 JDBC 和 RDBMS 之间的连接，因此 Kotlin 不知道该如何关闭它，开发者必须手动关闭连接，否则，即使 Kotlin 中的语句对象已经释放，对应的连接仍然存在，这可不太好。

这样的代码看上去简单，但其实存在一个相当致命的漏洞：连接是无论如何都要关闭的，可是 execute 可能产生异常，并提前异常返回，那样 stmt.close() 就不会被执行，于是连接就被泄漏了。因此，我们必须对 execute 中可能产生的异常进行处理。大家已经知道有 try 和 catch，于是可能写出下面这样的代码：

val stmt = conn.createStatement()

try {
    stmt.execute("...")
} catch (ex: Throwable) {}

stmt.close()

但这会造成两个问题：

  • 这样的代码不太好看。（当然，你可能还是不这么认为……）
  • 我们的代码『吃掉』了原本应该抛出的异常 ex，但其实我们只是想确保执行 stmt.close()，而不是直接屏蔽所有异常。

在 JDBC（和 Java 语言本身）中，其实已经有这个问题的解决方法了。如果你去查看 Statement 所实现的接口，你会发现一个 AutoClosable，意思是『自动关闭』，这正是 Java 当中用于自动清理不再使用的资源的一种方式。具体来说，如果一个对象实现了 AutoClosable，那么在以某种方式使用它时，这个对象就有能力清理自身所包含的资源。只不过，我们刚才所写的代码，并没有用这种方式，所以 Statement 也就不会自动关闭。

『某种方式』到底是什么呢？请看下面的 Kotlin 代码：

conn.createStatement().use {
    it.execute("...")
}

Kotlin 为所有的 AutoClosable 都定义了扩展函数 use，它的功能与 let 差不多，都是把表达式的值作为 Lambda 的第一个参数重新传递给你，不过，use 会在 Lambda 执行完成后，将这个 AutoClosable 关闭掉。这样一来，我们只需要在 use 块内正常使用语句对象，当离开了 use 之后，不管有没有异常，Kotlin 都会自动关闭语句，这样我们就放心啦。

到底怎么回事？

AutoClosable 本身只是定义了一个 close 方法。实现 AutoClosable 的类（例如 Statement）把清理资源的代码放在 close 中，这样只要调用 close 方法，就可以清理不再使用的资源。而 use 扩展函数所做的，正是在我们的 Lambda 表达式执行完成后，自动调用 close 方法，不过它内部添加了额外的代码来处理异常情况。

通过 SQL 发送数据​

createStatement 可以用来将事先写好的 SQL 语句发给 RDBMS 执行，例如上面的 CREATE TABLE。那么，如果想要执行 INSERT INTO 这样具有动态数据的 SQL 语句呢？这样的语句无法事先写在代码中，也就不能通过 execute 来直接执行。

Nyaci：直接把数据转换成字符串，和 SQL 的其它部分拼起来不就好了？

我就知道你会这么说……那么好，我们来看看这么做会发生什么，下面的代码初始化玩家帐户，把钻石数和金币数设置为 0：

conn.createStatement().use {
    val playerName: String = args.get(0)    // 例如，从命令中获取玩家名
    it.execute("""
        INSERT INTO deposit VALUES ('$playerName', 0, 0);
    """)
}
危险

绝不要在实际插件中写出这样的代码。绝，对，不，要。

$ 是我们先前见到过的插值语法，它将玩家名称拼到这个 SQL 语句中，所以如果 playerName 是 "Nyaci"，那么所执行的语句就是：

INSERT INTO deposit VALUES ('Nyaci', 0, 0);

由于 SQL 中使用单引号来表示字符串，因此需要在两侧加上 '，到目前为止，一切都看上去很 OK，对吗？

这段代码存在着一个巨大的漏洞：坏人只需要使用一个精心设计的玩家名（通过使用自定义名称一类的插件），就可以利用这段代码执行任意的 SQL 语句。例如，如果有人把 playerName 设置为了 Nyaci', 0, 0); DROP TABLE deposit; -- ，那么拼接出来的 SQL 语句就是：

-- 想象以下三部分拼在一起：
-- INSERT INTO deposit VALUES('             （插值的前半部分）
-- Nyaci', 0, 0); DROP TABLE deposit; --    （用户输入）
-- ', 0, 0);                                （插值的后半部分）

INSERT INTO deposit VALUES('Nyaci', 0, 0); DROP TABLE deposit; -- ', 0, 0);

一旦我们的程序允许了这样的情况发生，整个存款表就会被直接删除。当然 DROP TABLE 也可以换成其它的语句，比如给自己的帐户增加金币，或者悄悄删除某个用户。如果服务器管理员不是那么小心，也许坏人还能执行 DROP DATABASE，或者更危险的事情。

这就是著名的 SQL 注入攻击（SQL Injection Attack），攻击者在原本应当是数据的地方插入代码，如果开发者没有意识到这一点，贸然相信这些数据（比如把它们插入 SQL 中直接执行），这些代码就会一下子『活过来』，在服务器里到处作妖，这可是非常糟糕的情况。

语奇终无

这正是插值的局限性之一。插值本身的功能只是把前后的内容与你提供的值拼在一起，而不管其内容是什么。换句话说，当使用插值时，攻击者可以在写了一部分的 SQL 语句之后续写，而且他们想写什么都可以！UPDATE 篡改数据，SELECT 盗取密码等等都不在话下。尽管我们用引号将内容括起来了，但要打破这样的限制非常简单，只需要加上注释符号 --。

有没有什么办法来避免这样的事情发生呢？当然，你可以小心地检查输入，确保它不包含 DROP TABLE 这样看上去很危险的内容，但这么做一来很复杂，二来不太靠谱，而且说不定有的玩家就想叫做 DROP TABLE 呢（笑）。其实设计 JDBC 的人已经考虑到了这个问题，我们需要使用下面这样的代码：

conn.prepareStatement("INSERT INTO deposit VALUES (?, 0, 0);")  // 预留一个插槽位置
    .use { 
        it.setString(1, "Nyaci")    // 给插槽 1 填入一个字符串
        it.execute()                // 执行语句
    }

prepareStatement 创建所谓的『预编译语句』，我们可以提前把 SQL 语句发给 RDBMS 编译。注意到语句当中的 ? 了吗？这个符号表示插槽（Slot），也就是说，稍后我们可以在这里插入额外的数据。如果语句中需要多个插槽，可以使用多个 ?。

在 use 中，我们使用 setString 来给刚才留下的插槽填入数据。要注意的是，与 Kotlin 不同，SQL 中的索引从 1 开始，即第一个插槽的索引是 1。

Nyaci：最后不还是要把数据插入 SQL 语句里……？

插槽的功能与执行字符串插值的 SQL 语句是一样的，但与字符串插值不同，SQL 语句和数据是分开到达 RDBMS 的。在 RDBMS 中，SQL 引擎 会先编译带有插槽的语句，然后再把数据放在正确的位置上，这样即使有人把 "Nyaci" 换成恶意代码，RDBMS 也只会认为『这家伙的名字长得有点奇怪』，而不会真的把它当作 SQL 代码来执行 —— 因为这一步已经在 prepareStatement 的时候完成了。

像这样把数据和代码分离，是应对远程代码执行漏洞（RCE）（SQL 注入是 RCE 的一种）的最有效方法。

类似地，如果我们想要用 SQL 来更新玩家的钻石数，可以使用 UPDATE：

conn.prepareStatement("UPDATE deposit SET diamond = ? WHERE name = ?;") // 这里有两个插槽
    .use {
        it.setInt(1, 999)           // diamond = 999
        it.setString(2, "Nyaci")    // name = 'Nyaci'
        it.execute()
    }

这样即使有坏人把玩家名改成了 DROP DATABASE 这样危险的语句，RDBMS 也不会执行它，坏人只好失望地洗洗睡了（笑）。

顺便一提，UPDATE、INSERT 和 DELETE 在执行完成后，会将实际修改的行数返回给程序。如果要使用这个返回值，只需要把 execute 替换成 executeUpdate：

val changed = it.execute()
println("修改了 $changed 行数据！")

通过 SQL 接收数据​

最后要说的就是大家期待已久的 SELECT 了，SELECT 的执行和其它语句是一样的：

conn.prepareStatement("SELECT * FROM deposit WHERE name = ?;")
    .use {
        it.setString(1, "Nyaci")            // name = 'Nyaci'
        it.executeQuery().use { result ->   // 给参数重新命名，不与外层的 it 冲突
            // 使用 result
        }
    }

ResultSet 同样也实现了 AutoClosable，即要求在使用完成后释放。虽然 ResultSet 会在对应的语句关闭后自动释放，但随手清理不需要的资源是一个好习惯。

唯一的区别是，我们使用了 executeQuery，告诉 JDBC『这个查询返回一张表，请把它给我』。executeQuery 返回一个 ResultSet，我们可以像下面这样使用它：

while (result.next()) {
    println("钻石数：${result.getInt("diamond")}")
    println("金币数：${result.getInt("gold")}")
}

ResultSet 表示的是一列对象，其中每一个对象是 RDBMS 中的一行，也就是『一列「行」』！一开始，ResultSet 是没有读取任何行的，通过 next 可以获取下一行（当第一次调用时，也就是读取第一行）。调用了 next 之后，ResultSet 会把读取到的数据保存下来，可以使用各类 getXXX 方法来读取这一行中对应的数据，就像读取配置文件一样。

在上面的代码中，我们使用 while 来不断读取 ResultSet 的下一行。当 next 发现下面已经没有数据的时候，它就会返回 false，从而结束循环。在每一次循环中，我们用 getInt 读出当前行的 diamond 和 gold 的值，并将它们显示出来。读取数据时，既可以传递列名，也可以传递编号，不过由于 SQL 中的列是无序的，因此除非在 SELECT 中明确指定了列的顺序，否则还是用列名比较保险。

默认情况下，ResultSet 是单向的：只能从上往下，用 next 选中每一行，一旦走过了一行，就不能再回来。这种查询方式对于 RDBMS 来说性能比较高，不过如果确实有需要倒退的情况，可以通过调整 prepareStatement 的参数来做到。这部分内容已经超出了本书的范围，讲解起来也有些复杂，大家如果有需要的话，就只好自己去查查相关的资料了（笑）。

最后要说明的是，通过 executeQuery 所产生的 ResultSet 是和被执行的语句（Statement 或 PreparedStatement）相关联的，如果要对 ResultSet 做什么操作，就必须在语句被关闭之前，也就是离开 use 之前完成。尝试在语句有效范围之外使用 ResultSet 会产生错误。

终于，有关 RDBMS 和 SQL 的内容就要告一段落了。笔者先前有提到过，『关系型数据库非常强大，但使用起来也同样困难』，不知大家从这三节的内容中（加上一节语言知识），是否感受到了这种高可靠性系统对知识储备的要求呢（笑）？让我们来简单总结一下关于 RDBMS 和 SQL 的内容：

  • RDBMS 使用表来描述数据间的关系。
  • SQL 是客户端和 RDBMS 之间沟通的语言。
  • SQL 有多种语句，分别用于不同的功能，如创建和删除表，查询和修改数据等。
  • 在 Kotlin 中，使用 JDBC 和 MySQL 的驱动程序与 MySQL 建立连接。
  • 使用 createStatement 和 execute 执行固定的 SQL 语句。
  • 使用 prepareStatement 和插槽来执行包含数据的 SQL 语句。不能使用插值，因为会给坏人留下可乘之机。
  • 使用 ResultSet 读取查询所得的内容。

关系型数据库在可靠性方面提供了很高的保障，并且擅长管理极大规模的数据。例如，著名的 CoreProtect 插件就利用 RDBMS 来记录海量的世界变化数据（破坏和放置方块、容器交互、实体伤害等），并且能够快速地查询到指定坐标附近发生的事情。不过，RDBMS 也不是万能的，对于一些小规模的数据，RDBMS 的单次操作相比诸如 YAML 文件之类的存储方式就要慢得多（大约需要几十毫秒），因此如果只是存储几名玩家的重生点数据，那还是不必劳驾 RDBMS 大动干戈了（笑）。