Ktor 从入门到放弃(四) 数据库操作

其实这一篇与 Ktor 关系不大,主要讲一下如何操作数据库,我们经常使用的数据库操作方法在 Ktor 里一样适用。

还是让我们先安装起 mysql 吧,在 Ubuntu 下可以这么做:

$ sudo apt install mysql-server

这里需要额外提一下,在 Ubuntu 18.04 以后,安装 mysql 不再提示输入 root 用户的密码,而是要在安装完成后额外配置。在完成配置前,无法使用 root 用户登录 mysql。我们需要读取 /etc/mysql/debian.cnf 文件,如下:

$ sudo cat /etc/mysql/debian.cnf
[client]
host     = localhost
user     = debian-sys-maint
password = 7hWj7C23Uf5nPHcT
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 7hWj7C23Uf5nPHcT
socket   = /var/run/mysqld/mysqld.sock

可以看到如上面所示的内容,mysql 为我们分配了默认的密码,使用默认的数据库的密码进行登录并进行修改:

$ mysql -udebian-sys-maint -p7hWj7C23Uf5nPHcT
mysql > use mysql;
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
mysql > flush privileges;

完成后即可以使用 root 用户和密码 123456 来进行登录了。


接下来回到 Ktor 项目,在 build.gradle 内加入对 mysql 的引用:

compile "mysql:mysql-connector-java:8.0.15"

然后先写一个定义文件,来描述如何连接 mysql:

const val JDBC_DRIVER = "com.mysql.jdbc.Driver"
const val DB_URL = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8"
const val DB_USER = "root"
const val DB_PASSWORD = "123456"

通常情况下,连接字符串写成 jdbc:mysql://localhost:3306/mydb 就够了,后面的参数标识了需要使用 Unicode 以及默认的字符编码为 UTF-8,当然此处会有个坑,后面再讲。

后面的事情就简单了,按以下操作方式进行就好了,你会发现这些代码都很熟悉:

Class.forName(JDBC_DRIVER)
val conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)
val stmt = conn.prepareStatement("SELECT * FROM MyTable WHERE id = ?")
stmt.setInt(1, 10000)
val rs = stmt.executeQuery()
while (rs.next()) {
    println(rs.getString(rs.findColumn("name")))
}
rs.close()
stmt.close()
conn.close()

这样就完成了一个最简单的查询。在实际项目中,为了不频繁开启和关闭数据库连接,通常会将 getConnection 的部分用连接池来管理。

好了,下面再来看一个代码,简单的 insert:

val stmt = conn.prepareStatement("INSERT INTO MyTable(user) VALUES (?)")
stmt.setString(1, userName)
stmt.executeUpdate()
stmt.close()

这里的 userName 是从外部传入的,这不重要,重要的是当这个 userName 含有表情或一些特殊符号时,insert 进去的数据是乱码。这个问题必须被解决,不然那些喜欢在名字或签名里加表情的同学们就要说话了。

这里我们选择的方法是将 mysql 的编码更改为 utf8mb4,关于这个编码,网上已有非常多的讨论,此处不赘述,我们只需要编写一个 utf8mb4.cnf 并且让 mysql 加载即可:

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

innodb_file_format=barracuda
innodb_file_format_max=barracuda
innodb_file_per_table=1
innodb_large_prefix=1

把这段代码保存到 /etc/mysql/conf.d/utf8mb4.cnf 然后重启 mysql 即可。

$ sudo service mysql restart

理论上到了这里也差不多应该结束本篇了,但是考虑到会有不少人需要将数据内容转换为 json,特此写个扩展,查询得到的 ResultSet 用它就好了:

fun ResultSet.toJsonArray(): String {
    var str = "["
    if (first()) {
        do {
            var item = "{"
            for (i in 0 until metaData.columnCount) {
                item += "\"${metaData.getColumnName(i)}\": \"${getString(i)}\","
            }
            item += "},"
            str += item
        } while (next())
    }
    str = str.trimEnd(',')
    str += "]"
    return str
}

上面那个查询如果改成转为 JSON 就可以直接这么写了:

val rs = stmt.executeQuery()
val jsonstr = rs.toJsonArray()
rs.close()

下一篇预告:《Ktor 从入门到放弃(五) session 管理》

推荐阅读更多精彩内容