读写应用程序数据-SQLite3
SQLite3是嵌入到ios中的关系型数据库。对存储大规模的数据非常实用，使得不必将每个对象加到内存中。
支持NULL、INTEGER、REAL（浮点数字）、TEXT（字符串和文本）、BLOB（二进制对象）数据类型。
1、通过Firefox中的SQLite Manager组件创建一个数据库文件，并在其中创建创建一个Table，添加相关的参数。
2、将创建好的文件推到Xcode项目中，并在Link Binary With Libraries中添加libsqlite3.dylib依赖库。
3、新建一个C语言类型文件，对话框会自动添加桥接文件，在文件中添加语句#import<sqlite3.h>。
4、然后创建一个DataBaseOperations.swift文件来管理数据库。
在DataBaseOperation.swift文件中，申明一个数据库指针。
//不透明指针，对应C语言里面的void *，这里指sqlite3指针 private var db:COpaquePointer = nil
创建一个初始化方法，在初始化方法中执行打开数据库操作。
//初始化方法打开数据库 required init(dbPath:String) { print("db path:" + dbPath) //String类的路径，转换成cString let cpath = dbPath.cStringUsingEncoding(NSUTF8StringEncoding) //打开数据库 let error = sqlite3_open(cpath!, &db) //数据库打开失败处理 if error != SQLITE_OK { sqlite3_close(db) } }
deinit{ self.colseDb() } //关闭数据库 func colseDb(){ sqlite3_close(db) }
//代码创建表 func createTable() -> Bool{ //sql语句 let sql = "CREATE TABLE UserTable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT, age INTEGER)" //执行sql语句 let execResult = sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil); //判断是否执行成功 if (execResult != SQLITE_OK) { return false } return true }
//插入一条信息 func addUser(user: Person) -> Bool { //sql语句 let sql = "INSERT INTO UserTable (username, password, email, age) VALUES (?, ?, ?, ?);"; //sql语句转换成cString类型 let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //sqlite3_stmt 指针 var stmt:COpaquePointer = nil //1.编译sql let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息 if prepare_result != SQLITE_OK { sqlite3_finalize(stmt) if let error = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" print(msg) self.alert(msg) } return false } //2.bind 绑定参数 //第2个参数：索引从1开始 //最后一个参数为函数指针 sqlite3_bind_text(stmt, 1, user.name!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, nil); sqlite3_bind_text(stmt, 2, user.password!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, nil); sqlite3_bind_text(stmt, 3, user.email!.cStringUsingEncoding(NSUTF8StringEncoding)!, -1, nil); sqlite3_bind_int(stmt, 4, CInt(user.age!)); //3.step执行 let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息 if step_result != SQLITE_OK && step_result != SQLITE_DONE { sqlite3_finalize(stmt) if let err = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" print(msg) self.alert(msg) } return false } //4.finalize sqlite3_finalize(stmt); return true }
//查询 func readAllUsers() -> [Person]{ //声明一个Person对象数组（查询的信息会添加到该数组） var usersArr = [Person]() //查询sql语句 let sql = "SELECT * FROM UserTable;"; //sqlite3_stmt 指针 var stmt:COpaquePointer = nil let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) if prepare_result != SQLITE_OK { sqlite3_finalize(stmt) if let error = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" print(msg) self.alert(msg) } return usersArr } //2.step while (sqlite3_step(stmt) == SQLITE_ROW) { let user = Person() //循环 从数据库获取数据，添加到数组中 let cName = UnsafePointer<CChar>(sqlite3_column_text(stmt, 0)) let cPwd = UnsafePointer<CChar>(sqlite3_column_text(stmt, 1)) let cEmail = UnsafePointer<CChar>(sqlite3_column_text(stmt, 2)) let cAge = sqlite3_column_int(stmt, 3) user.name = String.fromCString(cName) user.password = String.fromCString(cPwd) user.email = String.fromCString(cEmail) user.age = Int(cAge) usersArr += [user] } //3.finalize sqlite3_finalize(stmt); return usersArr }
//更新一条信息 func updateUser(name: String , toName:String) -> Bool { //更新sql语句 let sql = "update UserTable set username = '\(toName)' where username = '\(name)'"; //sqlite3_stmt 指针 var stmt:COpaquePointer = nil let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息 if prepare_result != SQLITE_OK { sqlite3_finalize(stmt) if let error = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" print(msg) self.alert(msg) } return false } //2.step执行 let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息 if step_result != SQLITE_OK && step_result != SQLITE_DONE { sqlite3_finalize(stmt) if let err = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" print(msg) self.alert(msg) } return false } //4.finalize sqlite3_finalize(stmt); return true }
//删除一条信息 func deleteUser(username: String) -> Bool { //删除sql语句 let sql = "delete from UserTable where username = '\(username)'"; //sqlite3_stmt 指针 var stmt:COpaquePointer = nil let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息 if prepare_result != SQLITE_OK { sqlite3_finalize(stmt) if let error = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)" print(msg) self.alert(msg) } return false } //3.step执行 let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息 if step_result != SQLITE_OK && step_result != SQLITE_DONE { sqlite3_finalize(stmt) if let err = String.fromCString(sqlite3_errmsg(self.db)) { let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)" print(msg) self.alert(msg) } return false } //4.finalize sqlite3_finalize(stmt); return true }
//定义一个报警器 func alert(msg:String) { dispatch_async(dispatch_get_main_queue()) { let alert = UIAlertView(title: "SQLiteDB", message:msg, delegate: nil, cancelButtonTitle: "OK") alert.show() } }
因为工程数据库文件打包之后，会在NSBundle.mainBundle()路径下，该路径是只读的，不允许修改，所以必须把该路径下的数据库拷贝一份到Documents路径下，以后整个工程都将操作Documents路径下的数据库。
在ViewController中，添加一下代码。
//声明一个Documents下的路径 let dbPath = NSHomeDirectory() + "/Documents/RWDataTest.sqlite" //判断数据库文件是否存在 if !NSFileManager.defaultManager().fileExistsAtPath(dbPath) { //获取安装包内数据库路径 let bundleDBPath = NSBundle.mainBundle().pathForResource("RWDataTest", ofType: "sqlite")! //将安装包内数据库拷贝到Documents目录下 do { try NSFileManager.defaultManager().copyItemAtPath(bundleDBPath, toPath: dbPath) } catch let error as NSError { print(error)//如果创建失败，error 会返回错误信息 } }
//打开数据库 let dbOperation = DatabaseOperations(dbPath: dbPath) //添加一张表 dbOperation.createTable(); //插入一条信息， 通过Person对象来传值 let person:Person = Person(name: "刘明洋", pwd: "liumingyang", email: "liumingyang@leadingdo.com", age: 30) dbOperation.addUser(person) //查询 let personArray:[Person] = dbOperation.readAllUsers() print("共搜索到：\(personArray.count) 条数据" ) //更新 dbOperation.updateUser("刘明洋", toName: "刘蕙通") //删除 dbOperation.deleteUser("刘蕙通") //关闭数据库 dbOperation.colseDb()
