DatabaseManager.swift
6.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
//
// DatabaseManager.swift
// metaCode
//
// Created by zx on 2024/3/25.
//
import FMDB
import HandyJSON
class DatabaseManager {
private let dbQueue: FMDatabaseQueue
init(filePath: String) {
let databasePath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
let filePathStr = "\(databasePath)/CNLiveDownSlideModelDataBase.db"
// print("2222222:\n\(filePathStr)")
self.dbQueue = FMDatabaseQueue(path: filePathStr) ?? FMDatabaseQueue()
// 在这里调用创建表的
self.createTable()
}
func hasDataInTable() -> Bool {
// 查询一行来确定是否有数据
var isHaveData = false
dbQueue.inDatabase { db in
let sql = "SELECT * FROM CNLiveDownSlideModelDataBase LIMIT 1" // 查询一行来确定是否有数据
let results: FMResultSet? = db.executeQuery(sql, withArgumentsIn: [])
isHaveData = results != nil && results!.next() // 如果 results 不为空且至少有一行数据，则返回 true
}
return isHaveData // 如果 results 不为空且至少有一行数据，则返回 true
}
// 创建表（如果表不存在）
func createTable() {
dbQueue.inDatabase { db in
if !db.executeUpdate("CREATE TABLE IF NOT EXISTS CNLiveDownSlideModelDataBase (blockId INTEGER PRIMARY KEY AUTOINCREMENT,id INTEGER,timeStamp INTEGER, isGetPage INTEGER,json_data TEXT)", withArgumentsIn: []) {
print("Error creating table: \(db.lastErrorMessage())")
}
}
}
// 保存模型（增）
func saveModel(_ models: [CNLiveDownSlideModel]) {
self.dbQueue.inTransaction { db, rollback in
do {
for slide in models {
let jsonData = try slide.toJSONString()
let insertSQL = "INSERT INTO CNLiveDownSlideModelDataBase (blockId,id,timeStamp,isGetPage, json_data) VALUES (?,?,?,?,?)"
if db.executeUpdate(insertSQL, withArgumentsIn: [slide.blockId ,slide.id,slide.timeStamp,0, jsonData ?? ["":""]]) == true{
// print("2222222save succ")
}else{
// print("2222222save fail")
}
}
} catch {
rollback
}
}
}
// 更新模型（改）
func updateModel(_ models: [CNLiveDownSlideModel]) {
self.dbQueue.inTransaction { db, rollback in
do {
for slide in models {
let jsonData = try slide.toJSONString()
let updateSQL = "UPDATE CNLiveDownSlideModelDataBase SET json_data = ? ,id = ? , isGetPage = ? ,timeStamp = ? WHERE blockId = ?"
if db.executeUpdate(updateSQL, withArgumentsIn: [jsonData ?? ["":""],slide.id,0,slide.timeStamp ,slide.blockId]) == true{
// print("2222222Succ updating model,blockId\(slide.blockId)")
}else{
// print("2222222Error updating model")
}
}
} catch {
rollback
}
}
}
// 更新表中所有记录的 isGetPage属性
func updateIsGetPage(isGetPage: Int){
let updateSQL = "UPDATE CNLiveDownSlideModelDataBase SET isGetPage = ?"
self.dbQueue.inTransaction { db, rollback in
if db.executeUpdate(updateSQL, withArgumentsIn: [isGetPage]) == true{
// print("2222222Succ updating model")
}else{
// print("2222222Error updating model")
}
}
}
// 删除模型（删）
func deleteModel(withID id: Int64) {
self.dbQueue.inTransaction { db, rollback in
let statement = "DELETE FROM CNLiveDownSlideModelDataBase WHERE id = ?"
if db.executeUpdate(statement, withArgumentsIn: [id]){
print("Success deleting model")
}else{
print("2222222Error deleting model")
}
}
}
// 查询所有模型（查）
// func fetchAllModels() -> [CNLiveDownSlideModel] {
// var models: [CNLiveDownSlideModel] = []
// self.dbQueue.inDatabase { db in
// if let results = try? db.executeQuery("SELECT * FROM CNLiveDownSlideModelDataBase", withArgumentsIn: []){
// while results.next() {
// let model = CNLiveDownSlideModel()
// model.pageId = Int(results.int(forColumn: "id"))
// let slideModelJSONStr = results.string(forColumn: "downSlideJSONStr")
// models.append(model)
// }
//
// }
//
// }
// return models
// }
// 根据ID查询模型（查）
func fetchModel(withID id: Int64) -> [CNLiveDownSlideModel]? {
var models: [CNLiveDownSlideModel]?
self.dbQueue.inDatabase { db in
let results = try? db.executeQuery("SELECT * FROM CNLiveDownSlideModelDataBase WHERE id = ? ORDER BY timeStamp ASC", withArgumentsIn: [id])
var tempModels = [CNLiveDownSlideModel]()
while results?.next() == true {
if let jsonData = results!.data(forColumn: "json_data"){
if let jsonResult = try? JSONSerialization.jsonObject(with: jsonData, options: []) as? [String: Any] {
// print("转换成功，jsonResult: \(jsonResult)")
// 在这里，jsonResult就是转换后的Dictionary<String, Any>
let res = jsonResult
let slide = newDictionaryToModel(res, CNLiveDownSlideModel.self) as! CNLiveDownSlideModel
if let isGetPage = results?.int(forColumn: "isGetPage"){
slide.isGetPage = Int(isGetPage)
}else{
}
tempModels.append(slide)
} else {
print("2222222转换失败，无法转换为字典")
}
}else{
print("2222222jsonData fail")
}
}
if tempModels.count > 0{
models = Array(tempModels)
}
}
return models
}
// 其他数据库操作方法可以在这里继续添加...
}