前言、
本篇文章使用模型插入病游，不需要涉及到表結(jié)構(gòu)升級(jí)唇跨。
表結(jié)構(gòu)升級(jí)方式
表結(jié)構(gòu)升級(jí)方式Demo
1、創(chuàng)建路徑、創(chuàng)建數(shù)據(jù)庫
static let manager = BaseQueue()
lazy var db: Connection = {
let path = BaseQueue.path()
var db = try? Connection(path)
db?.busyTimeout = 5.0//設(shè)置線程安全
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买猖、相關(guān)字段定義
//數(shù)據(jù)庫單利
public let dataBase : Connection = BaseQueue.manager.db
//表主鍵
private let id = Expression<String>("primaryId")
//表模型數(shù)據(jù)
private let data = Expression<String?>("data")
3改橘、創(chuàng)建表、刪除表
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政勃、根據(jù)id查詢當(dāng)前表中是否有重復(fù)數(shù)據(jù)
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唧龄、插入數(shù)據(jù)
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兼砖、更新數(shù)據(jù)
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需要注意下奸远，這邊判斷如果沒有數(shù)據(jù)則做數(shù)據(jù)插入，有則數(shù)據(jù)更新讽挟，不然會(huì)拋出異常
7懒叛、刪除數(shù)據(jù)
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、查詢數(shù)據(jù)
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)
}
}
}
數(shù)據(jù)解析可以優(yōu)化下耽梅，看是否有更優(yōu)化的方法薛窥，目前先這樣處理
9、使用方式
類名定義
public class ATUser: HandyJSON {
var userId : String = ""
var nickName : String = ""
var name : String = ""
var age : Int = 0
required public init() {}
}
數(shù)據(jù)插入
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眼姐、相關(guān)庫
pod 'HandyJSON', '~> 5.0.0'
pod 'SwiftyJSON'
pod 'SQLite.swift'
