本文详细介绍如何在Swift中使用GRDB.swift库操作SQLite数据库，包括连接数据库、执行SQL语句、创建表、索引、数据插入、更新、查询、删除等基本操作，以及使用API进行更高级的数据管理，如模型创建、存储、查询、更新和删除。
简介
在 OC 中我们经常使用 FMDB 以及一些系列衍生的 Model 工具来操作数据库, 但在Swift中显得不那么友好.
- Swift 与 OC 混编中 ClassName 变成项目名.类名, 这种情况下操作数据库会出现各种各样的问题.
- 必须要继承 NSObject, 不适合 Struct 作为Model 的情况.
GRDB.swift 是 Swift 用来操作 SQLite 的工具. 通过 Protocol 的方式, 对 Struct Class 进行 CURD 操作, 同样也可以使用 DML 操作数据.
创建连接数据库
var dbPool: DatabasePool?
var dbQueue: DatabaseQueue?
do {
dbPool = try DatabasePool(path: path)
dbQueue = try DatabaseQueue(path: path)
}catch {
}
DatabasePool 支持并发数据库操作 和 WAL模式.推荐使用.
执行SQL语句操作
创建表
guard let cursor = dbPool else {
return
}
do {
try cursor.write({ (db) in
try db.execute(sql: """
CREATE TABLE IF NOT EXISTS user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
address TEXT)
"""
)
})
}catch {
debugPrint("create table user error")
}
创建索引
try cursor.write({ db in
let res = try db.indexes(on: "user").filter({$0.name == "name_index"}).isEmpty
if res {
try db.create(index: "name_index", on: "user", columns: ["name"])
}
})
删除索引
try cursor.write({ db in
try db.drop(index: "name_index")
})
插入
try cursor.write({ (db) in
let exists = try db.tableExists("user")
if exists {
try db.execute(
sql: """
INSERT INTO user(name, age, address)
VALUES(?,?,?)
""",
arguments: ["kevin",12,"address"])
}
})
statement 方式插入
try cursor.write({ (db) in
let exists = try db.tableExists("user")
if exists {
let statement = try db.makeUpdateStatement(sql: """
INSERT INTO user(name, age, address)
VALUES(?,?,?)
""")
try statement.execute(arguments: ["kevin",12,"address"])
try statement.execute(arguments: ["kevin",12,"address"])
try statement.execute(arguments: ["kevin",12,"address"])
}
})
更新
try cursor.write({ (db) in
let exists = try db.tableExists("user")
if exists {
try db.execute(sql: """
UPDATE user SET name = ?, age = ?, address = ?
WHERE name = ?
""", arguments: ["kevin5",12,"address", "kevin"])
try db.execute(sql: """
UPDATE user SET name = :name, age = :age, address = :address
WHERE name = :name2
""", arguments: ["name": "kevin5", "age": 12, "address": "address", "name2": "kevin"])
}
})
statement 方式
try cursor.write({ (db) in
let exists = try db.tableExists("user2")
if exists {
let statement = try db.makeUpdateStatement(sql: """
UPDATE user SET name = ?, age = ?, address = ?
WHERE name = ?
""")
try statement.execute(arguments: ["kevin5",12,"address", "kevin"])
}
查询
查询是需要使用class Row 进行查询, 也可以使用数据库存储数据类型进行查询.(DatabaseValueConvertible)
try cursor.read({ db in
let exists = try db.tableExists("user")
if exists {
let statement = try db.makeSelectStatement(sql: """
select * from user
""")
let res = try Row.fetchAll(statement)
print(res)
let res2 = try Row.fetchAll(db, sql: "SELECT * FROM user")
if res2.isEmpty == false {
let r = res2[0]
print(r)
print(r["name"] as String)
}
let res3 = try String.fetchAll(db, sql: "select name from user")
print(res3)
let res4 = try Row.fetchCursor(db, sql: "SELECT * FROM user")
while let r = try res4.next() {
debugPrint(r["id"] as Int)
debugPrint(r[Column("name")] as String)
}
}
})
直接过去某个数据
let name: String? = try cursor.read { (db) -> String? in
return try String.fetchOne(db, sql: "select name from user where id = 1")
}
删除
guard let cursor = dbPool else {
return
}
do {
try cursor.write({ (db) in
let exists = try db.tableExists("user")
if exists {
try db.execute(
sql: """
DELETE FROM user
WHERE id > 30
""")
}
})
} catch let error {
debugPrint(error)
}
使用 API 操作
创建存储模型
基础部分, 建议使用 Codable 协议
struct UserEntity: Codable {
var id: Int?
var name: String?
var age: Int?
var address: String?
}
使用 ColumnExpression 声明列名表示, 在 CURD 中直接使用 Columns.id/.name/.age/.address 进行操作数据
extension UserEntity {
enum Columns: String, ColumnExpression {
case id, name, age, address
}
}
使用 TableRecord 协议, 告知操作数据表
extension UserEntity: TableRecord {
// 数据表名字 如果不自定义 将使用默认的表名, 默认为小写开头驼峰命名 例如 UserEntity -> userEntity
static var databaseTableName: String {
return "user"
}
// 查询字段, 默认使用 * , 复制后将使用 id, name, age, address
// static var databaseSelection: [SQLSelectable] = [Column("id"), Column("name"), Column("age"), Column("address")]
// 同上 声明列名表示的方式
static var databaseSelection: [SQLSelectable] = [Columns.id, Columns.name, Columns.age, Columns.address]
}
FetchableRecord 进行查询操作, 使用了Codable可以不实现init(row: Row)
// fetch
extension UserEntity: FetchableRecord {
// 未使用 Codable = Decodable & Encodable 协议的 需要实现init(row: Row)
init(row: Row) {
id = row["id"]
name = row["name"]
age = row[Columns.age]
address = row[Columns.address]
}
}
使用PersistableRecord / MutablePersistableRecord插入更新保存数据, 存储模型是Class使用PersistableRecord, 存储模型是struct使用MutablePersistableRecord. 两者区别在于 MutablePersistableRecord save() insert() 是 mutating.
// insert
extension UserEntity: MutablePersistableRecord {
func encode(to container: inout PersistenceContainer) {
container["id"] = id
container[Columns.name] = name
container[Columns.address] = address
container[Columns.age] = age
}
mutating func didInsert(with rowID: Int64, for column: String?) {
id = Int(rowID)
}
}
创建表
try db.create(table: databaseTableName, temporary: false, ifNotExists: true, body: { (t) in
t.autoIncrementedPrimaryKey("id")
t.column("name", .text)
t.column("age", .integer)
t.column("address", .text)
})
插入
try cursor.write({ (db) -> Void in
var user = UserEntity(id: nil, name: "kk", age: 22, address: "kk.ads")
try user.insert(db)
})
查询
guard let user = try cursor.read({ (db) -> UserEntity? in
return try UserEntity.fetchOne(db, sql: "select * from user where id == 20")
}) else {
return
}
guard let user2 = try cursor.read({ (db) -> UserEntity? in
// return try UserEntity.filter(key: 30).fetchOne(db)
return try UserEntity.fetchOne(db, key: 30)
// return try UserEntity.fetchOne(db)
}) else {
return
}
更新
guard var kk = try cursor.read({ (db) -> UserEntity? in
return try UserEntity.filter(sql: "name == 'kk'").fetchOne(db)
}) else {
debugPrint("fetch kk error")
return
}
try cursor.write({ (db) -> Void in
try kk.updateChanges(db, with: { (u) in
u.address = "k.kkkkkkk"
})
// try kk.updateChanges(db) {
// $0.address = "k.kkkkkkk"
// }
})
var newkk = kk
newkk.address = "k.kk.kk"
try cursor.write({ (db) -> Void in
try newkk.updateChanges(db, from: kk)
})
删除
guard let kk = try cursor.read({ (db) -> UserEntity? in
return try UserEntity.filter(sql: "name == 'kk'").fetchOne(db)
}) else {
debugPrint("fetch kk error")
return
}
try cursor.write({ (db) -> Void in
try kk.delete(db)
})
}catch {
debugPrint("delete error")
}
迁移
GRDB通过注册标识(可写成版本号)进行数据库迁移操作.
创建迁移对象
var migrator = DatabaseMigrator()
查询应用迁移
do {
let res = try migrator.appliedMigrations(in: dbPool!)
print(res.contains("v1"))
print(res.contains("v2"))
} catch let error {
debugPrint(error)
}
注册
migrator.registerMigration("v1") { (db) in
try db.create(table: "user_entity", body: { (t) in
t.autoIncrementedPrimaryKey("id")
t.column("name", .text)
t.column("address", .text)
t.column("age", .integer)
})
}
migrator.registerMigration("v2") { (db) in
try db.alter(table: "user_entity", body: { (t) in
t.add(column: "time", .date).notNull().defaults(to: Date())
})
}
执行
do {
try migrator.migrate(dbPool!)
// try migrator.migrate(dbPool!, upTo: "v2")
}catch {
print("error migrator")
}
仅为个人学习记录, 详细操作, 请移步官方文档.
扫一扫
350
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
