读写应用程序数据-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()