iOS开发——数据持久化Swift篇&（三）SQLite3
浏览数：119 /
时间：2015年06月11日
SQLite3
使用
1 //******************** 5.3 SQLite3存储和读取数据 2 func use_SQLite3() 3 { 4 //声明一个Documents下的路径 5 var dbPath = NSHomeDirectory() + "/Documents/SwiftClassDB.sqlite" 6 7 //判断数据库文件是否存在 8 if !NSFileManager.defaultManager().fileExistsAtPath(dbPath) 9 { 10 //获取安装包内数据库路径 11 var bundleDBPath:String? = NSBundle.mainBundle().pathForResource("SwiftClassDB", ofType: "sqlite") 12 13 //将安装包内数据库拷贝到Documents目录下 14 NSFileManager.defaultManager().copyItemAtPath(bundleDBPath!, toPath: dbPath, error: nil) 15 } 16 17 //打开数据库 18 let dbOperation = DatabaseOperations(dbPath: dbPath) 19 20 //添加一张表 21 dbOperation.createTable(); 22 23 //插入一条信息， 通过Person对象来传值 24 let person:Person = Person(name: "刘明洋", pwd: "liumingyang", email: "[email protected]", age: 30) 25 26 dbOperation.addUser(person) 27 28 //查询 29 let personArray:[Person] = dbOperation.readAllUsers() 30 println("共搜索到：\(personArray.count) 条数据" ) 31 32 33 //更新 34 dbOperation.updateUser("刘明洋", toName: "刘蕙通") 35 36 37 //删除 38 dbOperation.deleteUser("刘蕙通") 39 40 //关闭数据库 41 dbOperation.colseDb() 42 43 44 45 } 46 47 48 //******************** 5.4 Core Data 49 func use_CoreData() 50 { 51 //参见工程 LMYCoreData 52 }
数据操作
1 class DatabaseOperations { 2 3 4 //不透明指针，对应C语言里面的void *，这里指sqlite3指针 5 private var db:COpaquePointer = nil 6 7 8 //初始化方法打开数据库 9 required init(dbPath:String) 10 { 11 println("db path:" + dbPath) 12 13 //String类的路径，转换成cString 14 let cpath = dbPath.cStringUsingEncoding(NSUTF8StringEncoding) 15 16 //打开数据库 17 let error = sqlite3_open(cpath!, &db) 18 19 //数据库打开失败处理 20 if error != SQLITE_OK { 21 sqlite3_close(db) 22 } 23 } 24 25 26 deinit{ 27 self.colseDb() 28 } 29 30 31 //关闭数据库 32 func colseDb(){ 33 34 sqlite3_close(db) 35 } 36 37 38 39 //代码创建表 40 func createTable() -> Bool{ 41 42 //sql语句 43 var sql = "CREATE TABLE UserTable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT, age INTEGER)" 44 45 //执行sql语句 46 var execResult = sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil); 47 48 //判断是否执行成功 49 if (execResult != SQLITE_OK) { 50 return false 51 } 52 53 return true 54 } 55 56 //插入一条信息 57 func addUser(user: Person) -> Bool 58 { 59 //sql语句 60 var sql = "INSERT INTO UserTable (username, password, email, age) VALUES (?, ?, ?, ?);"; 61 //sql语句转换成cString类型 62 63 var cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) 64 65 //sqlite3_stmt 指针 66 var stmt:COpaquePointer = nil 67 68 69 //1.编译sql 70 let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) 71 72 //判断如果失败，获取失败信息 73 if prepare_result != SQLITE_OK { 74 sqlite3_finalize(stmt) 75 if let error = String.fromCString(sqlite3_errmsg(self.db)) { 76 let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" 77 println(msg) 78 self.alert(msg) 79 } 80 return false 81 } 82 83 84 let intTran = UnsafeMutablePointer<Int>(bitPattern: -1) 85 let tranPointer = COpaquePointer(intTran) 86 let transient = CFunctionPointer<((UnsafeMutablePointer<()>) -> Void)>(tranPointer) 87 88 //2.bind 绑定参数 89 //第2个参数：索引从1开始 90 //最后一个参数为函数指针 91 sqlite3_bind_text(stmt, 1, user.name!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, transient); 92 sqlite3_bind_text(stmt, 2, user.password!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, transient); 93 sqlite3_bind_text(stmt, 3, user.email!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, transient); 94 sqlite3_bind_int(stmt, 4, CInt(user.age!)); 95 96 97 //3.step执行 98 var step_result = sqlite3_step(stmt) 99 100 //判断执行结果，如果失败，获取失败信息 101 if step_result != SQLITE_OK && step_result != SQLITE_DONE { 102 sqlite3_finalize(stmt) 103 if let err = String.fromCString(sqlite3_errmsg(self.db)) { 104 let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" 105 println(msg) 106 self.alert(msg) 107 } 108 return false 109 } 110 111 //4.finalize 112 sqlite3_finalize(stmt); 113 114 return true 115 } 116 117 //查询 118 func readAllUsers() -> [Person]{ 119 120 //声明一个Person对象数组（查询的信息会添加到该数组） 121 var usersArr = [Person]() 122 123 //查询sql语句 124 var sql = "SELECT * FROM UserTable;"; 125 126 //sqlite3_stmt 指针 127 var stmt:COpaquePointer = nil 128 var cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) 129 130 //1.编译sql 131 let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) 132 if prepare_result != SQLITE_OK { 133 sqlite3_finalize(stmt) 134 if let error = String.fromCString(sqlite3_errmsg(self.db)) { 135 let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" 136 println(msg) 137 self.alert(msg) 138 } 139 return usersArr 140 } 141 142 //2.step 143 while (sqlite3_step(stmt) == SQLITE_ROW) { 144 var user = Person() 145 146 //循环 从数据库获取数据，添加到数组中 147 let cName = UnsafePointer<CChar>(sqlite3_column_text(stmt, 0)) 148 let cPwd = UnsafePointer<CChar>(sqlite3_column_text(stmt, 1)) 149 let cEmail = UnsafePointer<CChar>(sqlite3_column_text(stmt, 2)) 150 let cAge = sqlite3_column_int(stmt, 3) 151 152 user.name = String.fromCString(cName) 153 user.password = String.fromCString(cPwd) 154 user.email = String.fromCString(cEmail) 155 user.age = Int(cAge) 156 157 usersArr += [user] 158 } 159 160 //3.finalize 161 sqlite3_finalize(stmt); 162 163 return usersArr 164 } 165 166 //更新一条信息 167 func updateUser(name: String , toName:String) -> Bool 168 { 169 //更新sql语句 170 var sql = "update UserTable set username = ‘\(toName)‘ where username = ‘\(name)‘"; 171 172 //sqlite3_stmt 指针 173 var stmt:COpaquePointer = nil 174 var cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) 175 176 //1.编译sql 177 let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) 178 179 //判断如果失败，获取失败信息 180 if prepare_result != SQLITE_OK { 181 sqlite3_finalize(stmt) 182 if let error = String.fromCString(sqlite3_errmsg(self.db)) { 183 let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" 184 println(msg) 185 self.alert(msg) 186 } 187 return false 188 } 189 190 //3.step执行 191 var step_result = sqlite3_step(stmt) 192 193 //判断执行结果，如果失败，获取失败信息 194 if step_result != SQLITE_OK && step_result != SQLITE_DONE { 195 sqlite3_finalize(stmt) 196 if let err = String.fromCString(sqlite3_errmsg(self.db)) { 197 let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" 198 println(msg) 199 self.alert(msg) 200 } 201 return false 202 } 203 204 //4.finalize 205 sqlite3_finalize(stmt); 206 207 return true 208 } 209 210 211 //删除一条信息 212 func deleteUser(username: String) -> Bool 213 { 214 //删除sql语句 215 var sql = "delete from UserTable where username = ‘\(username)‘"; 216 217 //sqlite3_stmt 指针 218 var stmt:COpaquePointer = nil 219 var cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) 220 221 //1.编译sql 222 let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) 223 224 //判断如果失败，获取失败信息 225 if prepare_result != SQLITE_OK { 226 sqlite3_finalize(stmt) 227 if let error = String.fromCString(sqlite3_errmsg(self.db)) { 228 let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" 229 println(msg) 230 self.alert(msg) 231 } 232 return false 233 } 234 235 //3.step执行 236 var step_result = sqlite3_step(stmt) 237 238 //判断执行结果，如果失败，获取失败信息 239 if step_result != SQLITE_OK && step_result != SQLITE_DONE { 240 sqlite3_finalize(stmt) 241 if let err = String.fromCString(sqlite3_errmsg(self.db)) { 242 let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" 243 println(msg) 244 self.alert(msg) 245 } 246 return false 247 } 248 249 //4.finalize 250 sqlite3_finalize(stmt); 251 252 return true 253 } 254 255 256 257 258 //定义一个报警器 259 func alert(msg:String) { 260 dispatch_async(dispatch_get_main_queue()) { 261 let alert = UIAlertView(title: "SQLiteDB", message:msg, delegate: nil, cancelButtonTitle: "OK") 262 alert.show() 263 } 264 } 265 }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
