前言、
本篇文章使用模型插入，不需要涉及到表结构升级。
表结构升级方式
表结构升级方式Demo
1、创建路径、创建数据库
static let manager = BaseQueue()
lazy var db: Connection = {
let path = BaseQueue.path()
var db = try? Connection(path)
db?.busyTimeout = 5.0//设置线程安全
return db!
}()
private class func path() -> String{
let path = NSHomeDirectory() + "/Documents/Caches/Sqlite"
let file = FileManager.default
if file.fileExists(atPath: path) == false{
do {
try file.createDirectory(atPath: path, withIntermediateDirectories: true, attributes: nil)
debugPrint("create path success")
} catch {
debugPrint(error.localizedDescription)
}
}
return path + "DataBase.sqlite"
}
2、相关字段定义
//数据库单利
public let dataBase : Connection = BaseQueue.manager.db
//表主键
private let id = Expression<String>("primaryId")
//表模型数据
private let data = Expression<String?>("data")
3、创建表、删除表
private class func addTable(_ name :String) -> Table{
let tables = Table(name)
try! dataBase.run(tables.create(ifNotExists: true, block: { (table) in
//注意primaryKey：autoincrement 和 true这里为true
table.column(id,primaryKey: true)
table.column(data)
}))
return tables
}
public class func dropTable(_ name :String,completion:@escaping ((_ success : Bool) ->Void)){
let exeStr = "drop table if exists \(name) "
do {
try dataBase.execute(exeStr)
completion(true)
}catch{
completion(false)
}
}
4、根据id查询当前表中是否有重复数据
private class func selectCount(tableName :String,primaryId :String) ->Int{
let table = addTable(tableName)
let alice = table.filter(id == primaryId)
do {
let count = try dataBase.scalar(alice.count)
debugPrint(count)
return count
} catch {
return 0
}
}
5、插入数据
public class func insertData(tableName :String,primaryId :String,content : String,completion:@escaping ((_ success : Bool) -> Void)){
let count = selectCount(tableName: tableName, primaryId: primaryId)
if count == 0 {
let table = BaseQueue.addTable(tableName)
let insertdata = table.insert(id <- primaryId,data <- content)
do {
try dataBase.run(insertdata)
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}else{
updateData(tableName: tableName, primaryId: primaryId, content: content, completion: completion)
}
}
6、更新数据
public class func updateData(tableName :String,primaryId :String,content : String,completion:@escaping ((_ success : Bool) -> Void)){
let count = selectCount(tableName: tableName, primaryId: primaryId)
if count == 0 {
insertData(tableName: tableName, primaryId: primaryId, content: content, completion: completion)
}else{
let table = addTable(tableName).filter(primaryId == id)
let update = table.update(id <- primaryId,data <- content)
do {
try dataBase.run(update)
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}
}
5和6需要注意下，这边判断如果没有数据则做数据插入，有则数据更新，不然会抛出异常
7、删除数据
public class func deleteData(tableName :String,primaryId :String,completion:@escaping ((_ success : Bool) -> Void)){
let table = addTable(tableName)
do {
let alice = table.filter(id == primaryId)
try dataBase.run(alice.delete())
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}
8、查询数据
public class func searchData(tableName :String,primaryId :String,completion:@escaping ((_ datas : JSON) -> Void)){
let table = addTable(tableName)
let alice = table.filter(id == primaryId)
guard let datas : AnySequence<Row> = try? dataBase.prepare(alice) else {
completion("")
return
}
decodeData(listData: datas, completion: completion)
}
public class func searchData(tableName :String,page : Int,size : Int = 20,completion:@escaping ((_ datas : JSON) ->Void)){
let table = addTable(tableName)
let order = table.order(id.asc).limit(size, offset: (page - 1) * size)
guard let datas : AnySequence<Row> = try? dataBase.prepare(order) else {
completion(JSON(""))
return
}
decodeData(listData: datas,completion: completion)
}
public class func searchData(tableName :String,completion:@escaping ((_ datas : JSON) ->Void)){
let table = addTable(tableName)
guard let datas : AnySequence<Row> = try? dataBase.prepare(table) else {
completion(JSON(""))
return
}
decodeData(listData: datas,completion: completion)
}
private class func decodeData(listData : AnySequence<Row>,completion:@escaping ((_ datas : JSON) ->Void)){
DispatchQueue.global().async {
var contentData : [JSON] = []
listData.forEach { (objc) in
let content :String = objc[data] ?? ""
let dataS : Data = content.data(using: .utf8)!
let json = JSON(dataS)
contentData.append(json)
}
let json = JSON(contentData)
DispatchQueue.main.async {
completion(json)
}
}
}
数据解析可以优化下，看是否有更优化的方法，目前先这样处理
9、使用方式
类名定义
public class ATUser: HandyJSON {
var userId : String = ""
var nickName : String = ""
var name : String = ""
var age : Int = 0
required public init() {}
}
数据插入
let user = ATUser()
user.userId = "10000"
user.age = 30
user.nickName = "飞将军"
user.name = "李广"
insertData(tableName: "yourTableName", primaryId: user.userId, content: user.toJSONString() ?? "") { (success) in
if (success){
print("插入成功")
}
}
updateData(tableName: "yourTableName", primaryId: user.userId, content: user.toJSONString() ?? "") { (success) in
if (success){
print("更新成功")
}
}
searchData(tableName: "yourTableName") { (json) in
print(json)
}
deleteData(tableName: "yourTableName", primaryId: "10000") { (success) in
if (success){
print("删除成功")
}
}
10、相关库
pod 'HandyJSON', '~> 5.0.0'
pod 'SwiftyJSON'
pod 'SQLite.swift'
