读写应用程序数据-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类的路径，转换成cStringlet 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.编译sqllet 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 = nillet cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)//1.编译sqllet 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.stepwhile (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 = nillet cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)//1.编译sqllet 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 = nillet cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)//1.编译sqllet 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()

 

《新程序员》：云原生和全面数字化实践50位技术专家共同创作，文字、视频、音频交互阅读

总结

以上是生活随笔为你收集整理的读写应用程序数据-SQLite3的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。