创建数据库连接
Sqlite.swift 为 swift 第三方库, 可通过 cocoapods 集成到项目中, 下面是使用方法, 创建表, 增删改查, 以及创建外键等操作
使用单例类创建数据库连接类
class SQLManager: NSObject {
static let manager = SQLManager()
var db:Connection!
let sqlFileName = "base.db" //数据库名称
private override init() {
super.init()
objc_sync_enter(self)
openDatabase()
objc_sync_exit(self)
}
private func openDatabase() {
let dbpath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).last!+"/"+sqlFileName
print("数据库 \(dbpath)")
if !FileManager.default.fileExists(atPath: dbpath) {
FileManager.default.createFile(atPath: dbpath, contents: nil)
db = try! Connection(dbpath)
}else {
do {
db = try Connection(dbpath)
}catch {
print("数据库链接失败")
}
}
}
}
创建表格, 增删改查等操作
导入 Sqlite.swift 使用, 先使用 Table 创建表 ,Expression 创建字段, 增删改查都使用 optionUserModel 操作
let OptionUserModel = Table("user") //user表名称，可同model名
let name = Expression<String>("name")
let sex = Expression<String>("sex")
let user_id = Expression<Int>("user_id")
let user_picture = Expression<String>("user_picture")
let token = Expression<String>("token")
let sign = Expression<String>("sign")
let e_mail = Expression<String>("e_mail")
let vip = Expression<String>("vip")
let phone = Expression<Int>("phone")
let create_date = Expression<String>("create_date")
let about = Expression<String>("about")
创建表格语句
func createUserTable() -> Void {
do {
let create = OptionUserModel.create(temporary: false, ifNotExists: true, withoutRowid: false) { (build) in
build.column(user_id,primaryKey: true)
build.column(name)
build.column(sex)
build.column(user_picture)
build.column(token)
build.column(sign)
build.column(e_mail)
build.column(vip)
build.column(phone)
build.column(create_date)
build.column(about)
}
try SQLManager.manager.db.run(create)
}catch {
print("创建用户表\(error)")
}
}
插入表数据, 根据数据 model
let inster = OptionUserModel.insert(
name <- model.name,
sex <- model.sex,
user_id <- model.user_id,
user_picture <- model.user_picture,
token <- model.token,
sign <- model.sign,
e_mail <- model.e_mail,
vip <- model.vip,
phone <- model.phone,
create_date <- model.create_date,
about <- model.about)
try SQLManager.manager.db.run(inster)
查询是否存在某个用户
// 查询是否存在某个用户
private func queryTable(model:UserModel) -> Bool {
let query = OptionUserModel.filter(user_id == model.user_id)
do {
let count = try SQLManager.manager.db.scalar(query.count)
if count > 0 {
print("用户存在 \(count)")
return true
}else {
print("用户不存在")
return false
}
}catch {
print("查询错误")
}
return false
}
查询用户信息根据 user_id
/// 获取用户信息
public func getUserModel(_user_id: Int) -> UserModel? {
let query = OptionUserModel.filter(user_id == _user_id)
do {
if let row = try SQLManager.manager.db.pluck(query) {
let model = UserModel(name: row[name],
sex: row[sex],
user_id: row[user_id],
user_picture: row[user_picture],
token: row[token],
sign: row[sign],
e_mail: row[e_mail],
vip: row[vip],
phone: row[phone],
create_date: row[create_date],
about: row[about])
print("查到用户 \(model)")
return model
}
}catch {
print("获取用户信息")
return nil
}
return nil
}
删除某个用户根据 user_id
/// 删除某个用户
public func deleteWithUserModel(model:UserModel) {
do {
try SQLManager.manager.db.run(OptionUserModel.filter(user_id == model.user_id).delete())
}catch {
print("删除用户失败")
}
}
更新用户信息根据 user_id
/// 更新名字
public func updateNameWithUserModel(model:UserModel) {
do {
try SQLManager.manager.db.run(OptionUserModel.filter(user_id == model.user_id).update(name <- model.name))
print("更新用户名字成功")
}catch {
print("更新用户名字错误")
}
}
如何使用Sqlite.swift 创建外键并级联删除
Sqlite.swift 创建外键, 在创建表格时使用 foreignKey 方法,第一个参数为从表中需要关联的字段, references, 参数为主表表名,及关联的主键, delete 参数为 .cascade, 这样会在删除主表行时,其从表会自动关联删除
let create = optionModel.create(temporary: false,ifNotExists: true, withoutRowid: false) { build in
build.column(id, primaryKey: true)
build.column(user_id)
build.column(book_id)
build.column(date)
build.column(book_name)
build.foreignKey(user_id, references: userModelTable, user_id, delete: .cascade)
}
注意,要使用外键功能需要先打开, 具体语句为:
try SQLManager.manager.db.execute("PRAGMA foreign_keys = ON;")
