原理介绍就不说赘述了，直接集成。
- cocoapods配置sdk
pod 'SQLite.swift'
- model模型直接用字段名得先封装以下，这里我们转换(宏定义)一下，方便引用
//1.声明model模型
class CDOptionModel: NSObject {
enum CDOptionType:String {
case yes = "YES"
case no = "NO"
}
var title:String = "" //字符串
var isLike = false //布尔
var createTime:Int = 0 //Int值
var type:CDOptionType = .no //枚举
}
2.转化(宏定义)，在后续操作中可用以下别名
let sqlFileName = "MySQL.db" //数据库名称
let OptionModel = Table("TabName") //表名称，可同model名
let db_id = Expression<Int>("id") //数据库自增id
let db_title = Expression<String>("title")
let db_createTime = Expression<Int>("createTime") //创建时间
let db_type = Expression<Int>("type") //分类
- 创建数据库，创建表
class CDSqlManager: NSObject {
static let shared = CDSqlManager()
var db:Connection!
private override init() {
super.init()
objc_sync_enter(self)
openDatabase()
objc_sync_exit(self)
}
//创建数据库文件
private func openDatabase() {
let libraryPath = NSSearchPathForDirectoriesInDomains(.libraryDirectory, .userDomainMask, true).first!
let dbpath = libraryPath().appendingPathComponent(str: "/\(sqlFileName)")
if !FileManager.default.fileExists(atPath: dbpath) {
FileManager.default.createFile(atPath: dbpath, contents: nil, attributes: nil)
db = try! Connection(dbpath)
createTable()
}else{
do{
db = try Connection(dbpath)
CDPrint(item: "数据库连接成功")
}catch{
CDPrint(item: "数据库连接失败:\(error.localizedDescription)")
}
}
}
//建议：每个表加一个扩展，在该扩展下写该表的所有操作，本文件只用来建、连数据库，数据库更新等
private func createTable() -> Void {
CDPrintManager.log("创建数据库表", type: .InfoLog)
createOptionTable()
}
}
func CDPrint(item:Any) {
print(item)
}
4.数据的增删改查 因为数据模型比较多时，全部写在同一个文件不好阅读和维护，建议一个表写入一个扩展文件中 CDSqlManager+option.swift
extension CDSqlManager{
//创建表
internal func createOptionTable() -> Void {
do{
let create = OptionModel.create(temporary: false, ifNotExists: false, withoutRowid: false) { (build) in
build.column(db_id,primaryKey: true)
build.column(db_title)
build.column(db_createTime)
build.column(db_isLike)
build.column(db_type)
}
try db.run(create)
CDPrint(item:"createOptionModel -->success")
}catch{
CDPrint(item:"createOptionModel -->error:\(error)")
}
}
//所有query，数据库模型转OptionModel
private func getNKOptionModelFromItem(item:Row) -> NKOptionModel {
let info = NKOptionModel()
info.title = item[db_title]
info.createTime = item[db_createTime]
info.type = NKOptionType(rawValue: item[db_type])!
info.isLike = item[db_isLike]
return info
}
//查询字段的属性，直接在字段后跟上该属性，.max()，.min()
private func queryMaxInt()->Int{
//查询表内最大id,新记录的id = 最大id+1
var maxInt = 0
do{
maxInt = try db.scalar(OptionModel.select(db_createTime.max)) ?? 0
CDPrint(item:"queryAllModelList -->success")
}catch{
CDPrintManager.log("queryAllModelList -->error:\(error)", type: .ErrorLog)
}
return maxFileId
}
//添加数据
func addNKOptionModel(info:NKOptionModel) {
let ModelId = queryMaxModelId() + 1
do {
try db.run(OptionModel.insert(
//用户模型向数据库模型赋值，用<-
db_title <- info.title,
db_createTime <- info.createTime,
db_type <- info.type.rawValue,
db_isLike <- info.isLike)
)
} catch {
CDPrint(item: "addNKOptionModel error:\(error)")
}
}
//删除模型 filter表条件，多个条件可用&& || ,类似OC sql 语句中的where xxx and xxx, where xxx or xxx
func deleteOneModel(info:NKOptionModel){
do {
try db.run(OptionModel.filter(db_title == info.title).delete())
} catch {
CDPrint(item:"deleteOneModel -->error:\(error)")
}
}
//删除tab内所有数据
func deleteAllModel(){
do {
try db.run(OptionModel.delete())
} catch {
CDPrint(item:"deleteOneModel -->error:\(error)")
}
}
//改
func updateModelTitle(type:CDOptionType,title:String,changeTime:Int){
do {
let sql = OptionModel.filter(db_title == title && db_type == type.rawValue) //多个条件可用&& = and, || = or
try db.run(sql.update(db_changeTime <- changeTime))
} catch {
CDPrint(item:"updateModelTitle -->error:\(error)")
}
}
//查询tab内所有数据
func queryAllModelList() -> [NKOptionModel]{
var arr:[NKOptionModel] = []
do {
//desc 降序，aes 升序
let sql = OptionModel.order(db_createTime.desc)
for item in try db.prepare(sql) {
let info = getNKOptionModelFromItem(item: item)
arr.append(info)
}
} catch {
CDPrint(item:"queryAllModelList -->error:\(error)")
}
return arr
}
}
//查询单个字段
func queryCreateTime(title:String) -> Int{
var index = 0
do {
let sql = OptionModel.filter(db_title == title)
for item in try db.prepare(sql.select(db_createTime)) {
index = item[db_createTime]
}
} catch {
CDPrint(item:"queryCreateTime -->error:\(error)")
}
return index
注意：
//条件关键字:filter
//修改更新关键字:db.update(sql)
//查询db.prepare(sql)
//执行sql语句： db.run(sql)
//删除的sql: dbModel.filter(条件).delete()
//删除整个表记录的sql: dbModel.delete()
//符合条件数量的sql dbModel.filter(条件).count
//表内所有数量的sql dbModel.count
